有关sqlserver的锁
今天有被问到:sqlserver锁定一行的最小锁是什么?
当时答曰:行级锁
回来查文档,得有关锁的信息(sql2k用syslockinfo表查, sql2k5用sys.dm_tran_locks表查):
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
OK,现在来看看锁定到一行的最小级别的锁是什么。
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 行受影响)
10
(1 行受影响)
11
(1 行受影响)
12
1> insert into t_heap select * from t_pk;
13
2> go
14
(3 行受影响)
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 行受影响)
19![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
现在,我更新有主键的表t_pk,看看sqlserver锁了些什么东东:
这里,sqlserver对t_pk表以及相应的page加上了IX锁,对更新的那一行加上了X锁。
再来,现在试试对堆表的更新:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
看看sqlserver对堆表锁了些什么:
![](https://www.cnblogs.com/images/cnblogs_com/jll/2.jpg)
sqlserver 2005很聪明,也只锁了一行(曾记得,sql2k对堆表会锁住一个page的,待找sql2k再测试)。
这2个测试中的锁定,一个是KEY, 一个是RID, 基本符合我所说的行级锁的意思。但真较上劲的话,我的答案又似乎不足,:)