SQL Server Insert操作中的锁
SQL Server Insert操作中的锁
这篇博文简单介绍一下在SQL Server中一条Insert语句中用到的锁。
准备数据
首先我们建立一张表Table_1,它有两列Id(bigint)和Value(varchar),其中Id建立了主键。
View Code
然后插入两条数据。
insert into dbo.table_2 (id, value) values (1, '1'), (2, '2');
开始测试
我们知道,在Transaction中共享锁在查询语句结束就释放了,而排它锁则在Transaction提交才释放。我们可以利用它来执行一个Insert,不提交Transaction,然后去查看锁的状态。注意,本文中查询窗口配置的Transaction隔离级别是默认值READ COMMITTED。
首先执行以下SQL:
begin tran t1 insert into dbo.table_2 (id, value) values (3, '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;
执行结果如下:
- 第一个是意向排他锁。它表示这个数据页下存在排他锁(就是第三个排他锁),我们发现它的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会产生阻塞,因此可以直接查询然后看锁的情况:
我们发现第9行的resource_description和第3行是相同的,这也说明了主键的锁只是锁住了某一个值而已。
补充2
这条SQL也会被Insert阻塞:
SELECT value FROM dbo.Table_2 WHERE value='1'
而且查看当前的锁可以发现,Key被锁的值正是Insert语句的Key值。这里有两个疑问:1. 为什么没用到主键列,却产生了主键锁。2.为什么Insert的数据还未commit,这里却会产生这一行主键的锁。
答:1. 我们查看查询计划,可以看到这条语句是用了聚集索引扫描,至于为什么不是表扫描,请看这里。 2. 由于事务隔离级别默认是Read Committed,所以这里会对已插入但未提交的数据主键加一个共享锁。
![]() |
作者:心亦 出处:http://www.cnblogs.com/szhx/ 本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。 |
相关文章
- SQL Server跨服务器操作数据库
- SQL Server - Management Studio - Client Statistics - Wait time on server replies vs Client processing time
- sql server 2012中red gate的sql source control消失
- Sql Server 函数的操作实例!(返回一条Select语句查询后的临时表)
- SQL SERVER错误:已超过了锁请求超时时段。 (Microsoft SQL Server,错误: 1222)
- Python:利用pymssql模块操作SQL server数据库
- C#数据库教程1-使用ADO.NET操作sql server 2012
- 使用Docker运行Microsoft SQL Server 2017
- sql server 小技巧(8) visual studio 2013里使用Sql server compact 4.0及发布问题处理
- SQL Server: Get table primary key and Foreign Key using sql query
- SQL Server 取日期时间格式 日期与字符串之间的转换
- SQL Server 的索引结构实例
- SQL Server 聚合函数算法优化技巧
- SQL Server-聚焦IN VS EXISTS VS JOIN性能分析(十九)
- SQL Server中的SQL语句优化与效率问题
- SQL Server 自动备份数据脚本
- 怎样使用oracle 的DBMS_SQLTUNE package 来执行 Sql Tuning Advisor 进行sql 自己主动调优
- sql server表分区系列【转】
- (1.2)sql server for linux 开启代理服务(SQL AGENT),使用T-SQL新建作业
- 【sql server复制】教你使用SQL SERVER复制
- SQL CHECK sql server免费监控单实例工具
- (2.1)备份与还原--sql server文件的概念及操作
- How to Kill All Processes That Have Open Connection in a SQL Server Database[关闭数据库链接 最佳方法] -摘自网络
- Importing/Indexing database (MySQL or SQL Server) in Solr using Data Import Handler--转载
- SQL Server 单表数据备份和恢复