Rowlock row-level locks in SQL Server

First, the use of Rowlock

1, Rowlock Row-level lock to ensure that the user gets the updated row, to the row to update, this period of time is not modified by other users. So the row-level lock can guarantee the consistency of data, and can improve the concurrency of data operation.

2. Rowlock tells SQL Server to use only row-level locks, rowlock syntax can be used in select,update and DELETE statements.

3. In the SELECT statement, for example

A connection is executed.

SET TRANSACTION Isolation  Level Repeatable READ 

begin Tran 

Select *  fromTableName with(Rowlock,updlock)whereID=3 

waitforDelay'00:00:05' 

Commit Tran 

b Connection if the execution

UpdateTableNameSetcolname='Ten' whereID=3 --then wait 5 seconds .

UpdateTableNameSetcolname='Ten' whereID<>3 --can be executed immediately

Ii. places to be aware of using Rowlock in SQL Server

1, if you mistakenly use on too many rows, the database is not smart enough to automatically upgrade row-level locks to page locks, the server will also be the cost of row-level locks to consume a large amount of memory and CPU, until it is unable to respond.

2, the SELECT statement, Rowlock without the use of the combination of the case is meaningless, with (rowlock,updlock) Such a combination is established, the data queried to use the Rowlock to lock, when the data is update, the lock will be released

Reference: Rowlock row-level locks in SQL Server http://www.studyofnet.com/news/1047.html

Rowlock row-level locks in SQL Server