Q
Problem solve Get help with specific problems with your technologies, process and projects.

How does ROWLOCK work in SQL Server, and when should I use it?

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.

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close