SQL Server Insert操作中的锁

原文:SQL Server Insert操作中的锁

这篇博文简单介绍一下在SQL Server中一条Insert语句中用到的锁。

准备数据

首先我们建立一张表Table_1,它有两列Id(bigint)和Value(varchar),其中Id建立了主键。

CREATE TABLE [dbo].[Table_2](
[Id] [bigint] NOT NULL,
[Value] [nchar](10) NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

然后插入两条数据。

insert into dbo.table_2
(id, value)
values
(1, ''),
(2, '');

开始测试

我们知道,在Transaction*享锁在查询语句结束就释放了,而排它锁则在Transaction提交才释放。我们可以利用它来执行一个Insert,不提交Transaction,然后去查看锁的状态。注意,本文中查询窗口配置的Transaction隔离级别是默认值READ COMMITTED。

首先执行以下SQL:

begin tran t1

insert into dbo.table_2
(id, value)
values
(3, '');

然后查看锁:

SELECT
resource_type,
request_mode,
resource_description,
request_session_id,
request_status,
resource_associated_entity_id,
DB_NAME(resource_database_id)as resource_database
FROM
sys.dm_tran_locks
WHERE
resource_type <> 'DATABASE'
ORDER BY
request_session_id;

执行结果如下:

SQL Server Insert操作中的锁

  • 第一个是意向排他锁。它表示这个数据页下存在排他锁(就是第三个排他锁),我们发现它的resource_associated_entity_id和第三个锁一样。那么,这个数据页就是存放这行数据的这个主键的。
  • 第二个也是意向排他。它的resource_type是OBJECT,此对象可以是数据表、视图、存储过程、扩展存储过程或任何具有对象 ID 的对象。它的resource_associated_entity_id这一列其实是object_id, 用函数object_name(object_id)看一下发现结果是Table_2。那么它下面存在的排他锁指的也是第三个锁了。
  • 第三个是排他锁。resou_description指的是插入数据主键的哈希值。

补充1

此时,我们在另外一个命令窗口中执行以下查询语句不会产生阻塞:

SELECT *
FROM dbo.Table_2
WHERE id=1;

但另一条却会产生阻塞:

SELECT *
FROM dbo.Table_2
WHERE id=3;

来看看第一条SQL产生的锁。由于共享锁会在查询结束立即释放,因此我们加一个HOLDLOCK,让它在事务结束再释放:

begin tran t2

SELECT *
FROM dbo.Table_2 WITH(HOLDLOCK)
WHERE id=1;

这是执行完以上语句锁的情况:

SQL Server Insert操作中的锁

第二条SQL会产生阻塞,因此可以直接查询然后看锁的情况:

SQL Server Insert操作中的锁

我们发现第9行的resource_description和第3行是相同的,这也说明了主键的锁只是锁住了某一个值而已。

补充2

这条SQL也会被Insert阻塞:

SELECT
value
FROM
dbo.Table_2
WHERE
value=''

而且查看当前的锁可以发现,Key被锁的值正是Insert语句的Key值。这里有两个疑问:1. 为什么没用到主键列,却产生了主键锁。2.为什么Insert的数据还未commit,这里却会产生这一行主键的锁。

答:1. 我们查看查询计划,可以看到这条语句是用了聚集索引扫描,至于为什么不是表扫描,请看这里。 2. 由于事务隔离级别默认是Read Committed,所以这里会对已插入但未提交的数据主键加一个共享锁。

上一篇:新浪微博客户端(5)-自定义UISearchBar


下一篇:lua语法备忘录