I was asked today:What is the minimum lock used by sqlserver to lock a row?
Answer: Row-Level Lock
Check the document and obtain the lock information. (use the syslockinfo table of sql2k, and use the SYS. dm_tran_locks table of sql2k5 ):
Resource Type:
1 = Null Resource (not used)
2 = Database
3 = File
4 = Index
5 = Table
6 = Page
7 = Key
8 = Zone
9 = RID (row ID)
10 = Applications
OK. Now let's take a look at what is the minimum lock that is locked to a row.
1 D: \ Program Files \ Microsoft SQL Server \ 90 \ Tools \ binn > Osql - USA - Pdba123 - Sdragon
2 1 > Create Table T_pk (t_id Int Primary Key , T_desc Varchar ( 50 ));
3 2 > Create Table T_heap (t_id Int , T_desc Varchar ( 50 ));
4 3 > Go
5 1 > Insert Into T_pk Values ( 1 , ' Welcome ' );
6 2 > Insert Into T_pk Values ( 2 , ' Nice to meet you ' );
7 3 > Insert Into T_pk Values ( 3 , ' Wall Street, stock ' );
8 4 > Go
9 ( 1 Rows affected)
10 ( 1 Rows affected)
11 ( 1 Rows affected)
12 1 > Insert Into T_heap Select * From T_pk;
13 2 > Go
14 ( 3 Rows affected)
15 1 > Begin Tran
16 2 > Update T_pk Set T_desc = ' Have a nice day ' Where T_id = 3 ;
17 3 > Go
18 ( 1 Rows affected)
19
Now, I update the t_pk table with the primary key to see what SQL Server locks:
Here, sqlserver adds the IX lock to the t_pk table and the corresponding page, and adds the X lock to the updated row.
Now, try updating the heap table:
1 > Rollback ;
2 > Go
1 > Begin Tran
2 > Update T_heap Set T_desc = ' Have a nice day ' Where T_id = 3 ;
3 > Go
Check what SQL Server locks the heap table:
Sqlserver 2005 is very clever and only locks one row (I remember that sql2k locks a page on the heap table and will test it again when I find sql2k ).
The locks in these two tests, one is the key and the other is the RID, basically in line with what I mean by row-level locks. But if it is really strong, my answer seems to be insufficient ,:)