How does "ROWLOCK" work? I have this situation:
User1 - Query Analyzer begin transaction update t1 with (rowlock) set c1 = 10 where c1 = 5 User 2 - Query Analyzer update t1 with (rowlock) set c1 = 20 where c1 = 15
Since the query executed by User1 is done with begin transaction the which User2 fires fails to execute until I do rollback or commit transaction in User1 - Query Analyzer.
According to me SQL Server should only do Row Level locking and should allow the other query to execute, but it does not execute. Can you please explain to me why?
First let me just say you should avoid using locking hints. In most situations SQL Server will select the appropriate locking granularity without the need of a hint.
I am guessing there is no index on the C1 column. Therefore the only way SQL Server can check to see the value of C1 is to look at every row. After the first transaction is executed an exclusive lock is help on the rows modified for the duration of the transaction. When the second transaction is executed the shared locks required to examine the rows will be blocked by the existing exclusive locks until the first transaction completes. If you have an index on the C1 column then an index seek can be used to located valid rows rather than examining every row itself.