Pessimistic lock conflicts with transaction processing concurrency

1. Run the following in a query window:Code

Begin   Transaction ;
Go
Select   * From Sk_product With ( Holdlock ) Where Proid = 47 ;
Waitfor Delay ' 00:00:30 ' ;
Select   * From Sk_product Where Proid = 47 ;
Go
Commit   Transaction ;

 

2. Run the following code in a new query window:

 

Update   [ Laptop ] . [ DBO ] . [ Sk_product ]
Set   [ Proname ]   =   [ Proname ] +   ' 12 '
  Where Proid = 47
Go
Select   * From Sk_product Where Proid = 47 ;

The results show that the data displayed by the two select statements in the first window is the data before modification. The second window is executed only after the code in the first window is executed, in the second window, select displays the modified data.

 

In this way, the concurrency is limited in time, because its uniqueness causes requests to be executed in order, which is inefficient.

 

3. Differences between two locks
Select * from table with (holdlock) other transactions can read the table, but cannot update or delete the table.
Select * from table with (tablockx) other transactions cannot read, update, and delete tables.

4.
RunExecSp_lock report lock Information

Press Ctrl + 2 in the query analyzer to view the lock information.

5. How to lock a row of a table
Set   Transaction   Isolation   Level   Read   Uncommitted  
Select   *   From   Table Rowlock Where ID =   1