October 17, 2011 at 12:37 am
Hello I have a locking issue which I'm trying to resolve, we have a table which gets hit with a lot of updates and selects. When the update occurrs it seems to lock the whole table which blocks any selects on that table for the duration of the update. I have been testing out the ROWLOCK hint within the update transaction but It doesn't seem to be doing as I expected. Below are my queries
1st window
begin tran
update Applications with (rowlock)
set status = 'Production_test'
where ID = 7
WAITFOR DELAY '00:00:10' --For acheiving real time Concurrency in this example
commit tran
2nd window
select * from Applications
where ID = 24
The query in the second window is waiting until the update query in the 1st window completes. I was expecting that the ROWLOCK would only lock the row that it is updating and a select query on another row would run?? The table has a primary key(clustered index) on the ID column and the isolation is at default which is read commited.
October 17, 2011 at 5:42 am
If you have space in tempdb, try using read commited snapshot isolation.
October 17, 2011 at 8:54 am
matthew.peters (10/17/2011)
Hello I have a locking issue which I'm trying to resolve, we have a table which gets hit with a lot of updates and selects. When the update occurrs it seems to lock the whole table which blocks any selects on that table for the duration of the update. I have been testing out the ROWLOCK hint within the update transaction but It doesn't seem to be doing as I expected. Below are my queries1st window
begin tran
update Applications with (rowlock)
set status = 'Production_test'
where ID = 7
WAITFOR DELAY '00:00:10' --For acheiving real time Concurrency in this example
commit tran
2nd window
select * from Applications
where ID = 24
The query in the second window is waiting until the update query in the 1st window completes. I was expecting that the ROWLOCK would only lock the row that it is updating and a select query on another row would run?? The table has a primary key(clustered index) on the ID column and the isolation is at default which is read commited.
My first question would be: Do you need to update statistics for the table you are updating? The next one would be how often are you rebuilding your indexes?
If it seems that rowlevel locking is not working my first guess would be indexes are not being used. Indexes don't get used if the statistics get out of date. So rebuilding your statistics could help.
Now it is also possible if you are updating more than one row at a time that the data is spread out over multiple pages in the table. If this is the case sql server determines it is easier to escalate to a table level lock instead of a row level or page level lock. In that case there isn't much you can do, except try to make sure the update happens as quickly as possible. So you might need to look at adding an index so the update would be quicker.
Finally, if you have existing indexes that contain the fields you are updating, this can causing issues as well and could show a need for updating statistics or rebuilding the indexes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply