Simple understanding of locks in SQL Server

1 How to lock a row in a table

2 locking a table for a database

SELECT *  from Table  with (HOLDLOCK

Add lock Statement:

 sybase:  update  table set  col1=  col1 where  1  =  0   ; MSSQL:  select  col1 from  table ( Tablockx) where  1  Span style= "color: #808080;" >=  0   in  EXCLUSIVE MODE; 

after locking, the other person is not operable until the lock user unlocks, with commit or rollback. a few examples to help you deepen your impressions

1) Exclusive lock
Create a new two connection
Execute the following statement in the first connection

begin Tran Update  Set A='aa'where B='B2 ' waitfor'00:00:30'--    CommitTran

Execute the following statement in a second connection

begin Tran Select *  from  where B='B2'commitTran

If both statements are executed at the same time, the select query must wait for 30 seconds for the update to complete before executing

2) shared lock
Execute the following statement in the first connection

begin Tran Select *  from Holdlock -  where B='B2'waitfor'  00:00:30'--commitTran

Execute the following statement in a second connection

 begin  tran  select  A,c from   table1  where  B=   " b2    " update   set  a=   '  

If both statements are executed at the same time, the select query in the second connection can be executed and the update must wait for 30 seconds before the first transaction releases the shared lock to an exclusive lock to execute

3) Deadlock

add table2 (d,e) D E D1 E1 D2 E2 execute the following statement in the first connectionbegin Tran Updatetable1SetA='AA' whereB='B2' waitforDelay'00:00:30' Updatetable2SetD='d5' whereE='E1' Commit Tran 

Execute the following statement in a second connection

begin Tran Updatetable2SetD='d5' whereE='E1' waitforDelay'00:00:10' Updatetable1SetA='AA' whereB='B2' Commit Tran 

At the same time, the system detects the deadlock and aborts the process.

Add one point:
Table-level locking hints supported by SQL Server2000
HOLDLOCK holds a shared lock until the entire transaction is complete and should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level
NOLOCK statement execution does not emit a shared lock, allowing dirty reads, equal to the READ UNCOMMITTED transaction isolation LEVEL
Paglock with multiple page locks where a table lock is used
READPAST let SQL Server skip any locking lines, perform transactions, apply to READ UNCOMMITTED transaction isolation LEVEL only skip RID lock, skip page, zone and table lock
Rowlock forcing the use of row locks
Tablockx enforces exclusive table-level locks, which prevent any other transactions from using this table during a transaction
Uplock forcing updates to be used when reading tables without sharing locks
Application Lock:
The application lock is the lock generated by the client code, not the lock generated by SQL Server itself
Two procedures for handling application locks
Sp_getapplock Locking Application Resources
Sp_releaseapplock Unlocking an application resource
Note: The difference between a table that locks a database
SELECT * from table with (HOLDLOCK) Other transactions can read the table but cannot update the delete
SELECT * from table with (Tablockx) Other transactions cannot read table, update and delete

Simple understanding of locks in SQL Server