July 2, 2008 at 5:59 pm
I have two separate updates running which I have reduced to the following two sets of statements:
process 1:
begin transaction
update table set column1 = column1 + 2 where reference_value = 6
process 2:
begin transaction
update table set column1 = column1 + 5 where reference_value = 7
Both of these statement will update multiple rows. The table has an index on reference_value and there is a primary key which is not being used in the update. I have disallowed page locks on the table. The problem is, process 2 is being blocked by process 1. None of the records overlap and can't for the life of me figure out how to get SQL Server to do true row level locks. I do know that this same example works just fine on Oracle 10g and DB2.
Also, when I look at the locks it tells me they are key locks and shows process 2 is waiting.
Thanks in advance for any responses.
July 2, 2008 at 11:44 pm
Hi,
These transaction will create DeadLock.
To avoid Deadlock, You can do serialization.
July 2, 2008 at 11:45 pm
I mean first commit the transaction(1) before starting the other transaction(2).
July 2, 2008 at 11:51 pm
July 3, 2008 at 12:00 am
You should firstly look a the locking on the table maybe this is not configured right. I would then do as the first person said comit the tran on each one.
July 3, 2008 at 8:22 am
Use TABLOCKX Option.
July 3, 2008 at 12:25 pm
Hi,
These transaction will create DeadLock.
To avoid Deadlock, You can do serialization.
I don't understand why these should deadlock. The where clause is specifying two completely different sets of rows. The actual data of the two statements never intersect.
Hari.Sharma (7/2/2008)
I mean first commit the transaction(1) before starting the other transaction(2).
I wish I could. These two processes are done by different people on different computers. Generally there are 100 different processes running and every once in a while I get a block.
July 3, 2008 at 12:37 pm
Trigger (7/2/2008)
Firstly, who cares whether it works on Oracle & DB2!Secondly, how have you ascertained that process 2 is blocked by process 1
Thirdly, How did you disable page locks and why did you disable page locking?
If you can answer the second and third we can troubleshoot it better.
I mentioned that it works on Oracle and DB2 as reference, not a put down to SQL Server. In the shop I work in, when there is a problem we always ask what is the behavior on other platforms. If it doesn't work on any platform then you know there is a basic issue at hand. Had a scenario where it worked on SQL Server and DB2 but not Oracle, turns out we found a odd bug in 10g's parser which was subsequently fixed by hot patch.
When I run the first statement in query analyzer and then run the second statement in a separate query analyzer the second statement hangs. When I look at the Activity monitor it tells me that process 2 is being blocked by process 1. It shows in the Activity Monitor that process 2 is waiting on a key lock. The rows of data being updated are unique to each statement.
I turned off the page locking by using alter index with allow_page_locks = off. I disabled page locking because it was causing waits because of page locks overlapping into other update requests. My thinking is if I have a true row lock, I am only locking the row of data that I am updating and I should not have this kind of contention.
July 4, 2008 at 5:43 am
Dear ghindson,
Read the Last para of the problem mentioned earlier.
There is clearly mentioned that Single statement is updating multiple rows and lock is also there.
It may be the case that few rows are common in both transaction.
So we cant update same rows in another transaction without commiting first one.
July 14, 2008 at 10:36 am
Hari.Sharma (7/4/2008)
Dear ghindson,Read the Last para of the problem mentioned earlier.
There is clearly mentioned that Single statement is updating multiple rows and lock is also there.
It may be the case that few rows are common in both transaction.
So we cant update same rows in another transaction without commiting first one.
The last paragraph is stating the problem that appears to be happening, but this is impossible.
The first statement clearly has a where clause that is different than the second statement.
One statement has a where clause with "where reference_value = 6" and the second statement has a where clause with "where reference_value = 7". There is no way these two statements can intersect.
This is the problem. These two statements are blocking each other with a key lock but do not have any intersection between them. I had mentioned that I turned off the page locking and tried to force row level locking, but this did not help me.
September 11, 2008 at 8:51 pm
Hi,
Did you find a solution of this issue?
I am facing a similar issue while readaing a different row from the table.
What is happening:
First Connection:
1. insert 4 new rows.
2. update one of the inserted rows with complete PK in the where clause.
yet not commit this connections txns. Here I have 4 Exclusive(X) KEY locks on this table, say table
A.
Open a new connection:
1. Trying to read a different row on the same table above, with using the PK keys in the where clause.actually this row does not exist on the table.
Locks :
However it is creating a Share read lock (S) and going into the WAIT condition, because of the first
connection locks.
Table PK Properties :
This table contains a 5 columns compoiste PK key. In the second connection Select query I
am using where clause only on the 4 columns.
Question:
Question is this, If it is trying to select a row which does not exist on the table, then why it is
waiting for the first connection's X locks.
Specific region:
This locking is coming only on when applition runs with J2EE context. With normal JDBC connection in the JAVA code or with opening a 2 sql console, this issue is not coming.
Please help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply