SQL Server Performance Forum – Threads Archive
How to solve this blocking problem?I have a transaction which is updating many rows of a table. The Lock Type is KEY and Mode is X. Some select statements can be run against the table but some will just hang due to blocking problem. How can I find out what kind of select statements I cannot run?
How to avoid this blocking problem?
One way is to run sp_who2 when updating.
Any select statement that is trying to scan through this KEY will be blocked, even if this KEY is not part of the result set of the select statement. One way to minimize these blocks is to make sure that you indexed the table properly so that your select statements are not trying a table scan.
And the other option is to use READPAST locking hint.
My understanding is that KEY means row lock on the index. Let’s say that the index id_custno of customer table contains the following: cust1
cust5 If the one has been locked is cust2, does it mean: select * from customer where custno = ‘cust1’ will work?
select * from customer where custno = ‘cust2’ will not work?
select * from customer where custno <> ‘cust2’ will work? How does SQL Server decide which indexes? My guess is that those indexes are the one containing the columns being updated. I notice that the clustered index is always included. Is it because the clustered index being part of all the non-clustered index? I have tried to force lock hint: update customer with (rowlock) set column2 = …. This does not work. I still get TAB when many rows are to be updated. But if I change to: update customer with (paglock) set column2 = …. I will get KEY on the column2 index and PAG on the clustered index.
Will creating views help?