sqlserver锁表机制

这个问题要具体分析：

第一，事务隔离级别基本两种模式，一种是阻塞式（read committed，repeatable read，serializable）

，一种是非阻塞式(read uncommitted,snapshot)。

默认是read committed，这种情况一般在更新表的时候，如果不使用hint 提示，基本是先对表添加IX锁，级别不算高，基本和其他锁兼容，但是repeatable read，serializable 事务隔离级别就会先对表添加IX锁，然后向X锁转化，而X锁和大多数锁都不兼容，容易发生表阻塞。

第二种隔离级别不会有以上问题，但是又引入了其它的问题。

以上是一种情况。

另外一种就是 锁升级，一个锁是96B内存，如果太多，sqlserver就会升级为表锁，一般是5000以上行级锁就升级为一个表X锁。

所以适当的文件分组和表分区 是有必要的。

其次就是资源互相引用导致事务长时间不能释放，导致真正的死锁，不过SQL2005以后，这种情况发生的概率很低。

留个问题你自己去想。

两个SQL，两个连接，同时执行。

update A set ANAME=xxx where Aid=55

update A set ANAME=xxx where Aid=56, 如果 56 不存在你说会发生什么情况呢？

先定位一下是哪个程序句柄导致的死锁。

方法一、查看db2diaglog文件

找到DeadLock or Lock timeout 死锁或锁超时信息

db2 force application(句柄ID)

直接结束进程即可。

方法二、DB2快照信息

1、看一下DB2快照信息

db2 get snapshot for locks on sample

可以得到类似信息：

数据库锁定快照

数据库名称 = SAMPLE

数据库路径 = D:\IBM\DB2\NODE0000\SQL00001\

输入数据库别名 = SAMPLE

挂起的锁定 = 8

当前已连接的应用程序 = 2

当前正等待锁定的代理程序数 = 1

应用程序句柄 = 54

应用程序标识 = LOCALDB2140304192925

序号 = 00001

应用程序名 = db2bpexe CONNECT

授权标识 = DB2ADMIN

应用程序状态 = 锁定等待

应用程序代码页 = 1208

挂起的锁定 = 4

总计等待时间（毫秒） = 247867

锁定列表

锁定名称 = 0x5359534C564C3031DDECEF2841

锁定属性 = 0x00000000

发行版标志 = 0x40000000

锁定计数 = 1

挂起计数 = 0

锁定对象名 = 2312

对象类型 = 行

表空间名 = IBMDB2SAMPLEREL

表模式 = DB2ADMIN

表名 = TEST

方式 = IX

查看锁定的详细信息：db2 get snapshot for locks for application agentid 1728

----（1728是句柄ID）

3、观察命令db2 list applications的输出

查看应用程序的状态是否有锁定等待(Lock-wait)状态出现。

执行命令 list applications for db sample show detail;

4、db2 force application(句柄ID)

直接结束进程即可。

where SalesOrderID='43662'SELECT resource_type, request_mode, resource_description,request_session_id, DB_NAME(resource_database_id)as resource_databaseFROM sysdm_tran_locksWHERE resource_type <>'DATABASE'--ROLLBACK TRAN 在事务回滚之前, 查看锁的类型: 其他session对Table只读, 不能更新, 在开一个新的session测试:select from SalesSalesOrderHeader where SalesOrderID='43662'goupdate SalesSalesOrderHeader set OrderDate=GETDATE() where SalesOrderID='43662' select可以正常执行, update语句一直处于等待状态, 等待上面的session释放锁 2 Update locks (U): 更新锁是共享锁和独占锁的组合用UPDLOCK保持更新锁USE AdventureWorks2008BEGIN TRANselect from SalesSalesOrderHeader WITH(UPDLOCK)where SalesOrderID='43662'SELECT resource_type, request_mode, resource_description,request_session_id,DB_NAME(resource_database_id)as resource_databaseFROM sysdm_tran_locksWHERE resource_type <>'DATABASE'ROLLBACK TRAN 查看到锁的信息: 3Exclusive locks (X): 独占锁是为了锁定数据被一个session修改的数据, 而不能够被另外的session修改 只能指定NOLOCK来读取USE AdventureWorks2008BEGIN TRANupdate SalesSalesOrderHeader set ShipDate=GETDATE() where SalesOrderID='43662'WHERE resource_type <>'DATABASE'ROLLBACK TRAN 查看锁: 4Intent locks (I): 意向锁用于建立锁的层次结构 意向锁包含三种类型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。 数据库引擎使用意向锁来保护共享锁(S 锁)或排他锁(X 锁)放置在锁层次结构的底层资源上。 意向锁之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。 意向锁有两种用途： 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。 提高数据库引擎在较高的粒度级别检测锁冲突的效率。

The logical basis for the theory of the integers that eucpd presented in books vii to ix of the "elements" was woefully deficient

欧几里德在《原本》的第七到第九册中提出的整数理论的逻辑基础是十分令人遗憾地有缺陷的。

Ix a copy of the apppcant s identification document

Ix申请人身分证明文件副本

Live pfe at its bestpart ix 02 05 04

圣灵果子系列：活出精彩人生之九02 05 04

The zoo ix an hour ' s driving outside the city

动物园在市外一小时的车程处。

Chapter ix supplementary provisions chapter i general provisions

第九章附则

Chapter ix supplementary provisions chapter i basic principles

第九章附则

192 31 07 05 once upon all time : creation part ix

第一百九十二集31 07 05

Amsterdam inter exchange see ix

阿姆斯特丹因特网交换中心。参见ix 。

175 03 04 05 dont throw away tomorrow part ix

第一百七十五集03 04 05

One minute s putonghua part ix - xiii boxset

笑谈普通话第九至十三辑

An ix lock on the clustered index and another on the table

聚集索引上的ix锁和表上的ix锁。

252 24 09 06 dont throw away tomorrow part ix

信有明天之九24 09 06

This is my semi - half - brother ix , i ' m sorry , ford

这是我的亲戚福特

Ix 2 copies of a layout plan of the club

Ix会社的平面图副本两份

Live pfe at its best - part ix

圣灵果子系列：活出精彩人生之九

Live pfe at its bestpart ix

圣灵果子系列：活出精彩人生之九

Voting record in appendix ix

录载于会议纪要英文版附录ix 。

Ix standard employment contract and terms of employment for helpers

Ix 标准雇佣合约及佣工的雇用条款

[ zaphod ] is that you ford ! praxibetel ix

是你吗？福特！

Once upon all time : creation part ix

一切从创造开始之九

Zaphod is that you ford ! praxibetel ix

是你吗？福特！

Ixes industry the management about our country financial industry to ponder

关于我国金融业混业经营的思考

Universit de paris ix - paris dauphine

巴黎第九大学

" ix obtain of reservation form

Ix 索取申请表办法

Iu locks are converted to ix locks if an update operation takes place

如果进行了更新 *** 作， iu锁将转换为ix锁。

One minute s putonghua part ix

笑谈普通话第九辑

Chapter ix : supplementary provisions

第九章:附则

Testing and acceptance criteria shall be in accordance with a e section ix

试验和合格标准均应遵循a e第ix卷。

Chapter ix supplementary provisions

第九章附则

Chapter ix supplementary provisions

第九章附则

Chapter ix the gorgon s head

第九章果刚的脑袋

Universit de paris ix - dauphine

巴黎第九大学

Dont throw away tomorrow part ix

信有明天之九

Thi i room 213 could you give me a wake - u call at ix tomorrow morning

这里是213房。明天早晨六点能请你叫我起床吗？

Strength , security and serenity for you in 2002 ! ix 20 04 02 by robert h schuller

带领我供给我与我同在的神20 04 02

Cv01p - ix doctor display card

功能侦测卡

Cv01v - ix doctor display card

功能侦测卡

Ix simppfication of the work architecture to rid connections beeen equipment

简化网络结构，避免设备之间的连接部件

Cv04p - ix pc test card

主机板维修卡

Francis ii of france dies and is succeeded by charles ix of france

1560年，法兰西弗朗西斯二世去世，法兰西的查尔斯九世继位。

No lock on the clustered index or table because they are already protected by ix locks

在聚集索引或表上不会获取锁，因为它们已被ix锁保护。

This passage is taken from chapter ix the art of pving , part iii eating and drinking

而本篇片段取自第九章“生活的艺术”中的第三节“餐饮” 。

B accessions in accordance with article ix ; declarations and notifications under articles i , x and xi

三第一第十和第十一条项下的声明和通知

术式之后皆为逻辑，一切皆为需求和实现。希望此文能从需求、现状和解决方式的角度帮大家理解隔离级别。

隔离级别的产生

在串型执行的条件下，数据修改的顺序是固定的、可预期的结果，但是并发执行的情况下，数据的修改是不可预期的，也不固定，为了实现数据修改在并发执行的情况下得到一个固定、可预期的结果，由此产生了隔离级别。

所以隔离级别的作用是用来平衡数据库并发访问与数据一致性的方法。

事务的4种隔离级别

READ UNCOMMITTED       未提交读，可以读取未提交的数据。READ COMMITTED         已提交读，对于锁定读(select with for update 或者 for share)、update 和 delete 语句，                       InnoDB 仅锁定索引记录，而不锁定它们之间的间隙，因此允许在锁定的记录旁边自由插入新记录。                       Gap locking 仅用于外键约束检查和重复键检查。REPEATABLE READ        可重复读，事务中的一致性读取读取的是事务第一次读取所建立的快照。SERIALIZABLE           序列化

在了解了 4 种隔离级别的需求后，在采用锁控制隔离级别的基础上，我们需要了解加锁的对象(数据本身&间隙)，以及了解整个数据范围的全集组成。

数据范围全集组成

SQL 语句根据条件判断不需要扫描的数据范围（不加锁）；

SQL 语句根据条件扫描到的可能需要加锁的数据范围；

以单个数据范围为例，数据范围全集包含：（数据范围不一定是连续的值，也可能是间隔的值组成）

1 数据已经填充了整个数据范围：（被完全填充的数据范围，不存在数据间隙）

整形，对值具有唯一约束条件的数据范围 1～5 ，

已有数据1、2、3、4、5，此时数据范围已被完全填充；

整形，对值具有唯一约束条件的数据范围 1 和 5 ，

已有数据1、5，此时数据范围已被完全填充；

2 数据填充了部分数据范围：（未被完全填充的数据范围，是存在数据间隙）

整形的数据范围 1～5 ，

已有数据 1、2、3、4、5，但是因为没有唯一约束，

所以数据范围可以继续被 1～5 的数据重复填充；

整形，具有唯一约束条件的数据范围 1～5 ，

已有数据 2，5，此时数据范围未被完全填充，还可以填充 1、3、4 ；

3 数据范围内没有任何数据（存在间隙）

如下：

整形的数据范围 1～5 ，数据范围内当前没有任何数据。

在了解了数据全集的组成后，我们再来看看事务并发时，会带来的问题。

无控制的并发所带来的问题

并发事务如果不加以控制的话会带来一些问题，主要包括以下几种情况。

1 范围内已有数据更改导致的：

更新丢失：当多个事务选择了同一行，然后基于最初选定的值更新该行时，

由于每个事物不知道其他事务的存在，最后的更新就会覆盖其他事务所做的更新；

脏读： 一个事务正在对一条记录做修改，这个事务完成并提交前，这条记录就处于不一致状态。

这时，另外一个事务也来读取同一条记录，如果不加控制，

第二个事务读取了这些“脏”数据，并据此做了进一步的处理，就会产生提交的数据依赖关系。

这种现象就叫“脏读”。

2 范围内数据量发生了变化导致：

不可重复读：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，

却发现其读出的数据已经发生了改变，或者某些记录已经被删除了。

这种现象就叫“不可重复读”。

幻读：一个事务按相同的查询条件重新读取以前检索过的数据，

却发现其他事务插入了满足其查询条件的新数据，这种现象称为“幻读”。

可以简单的认为满足条件的数据量变化了。

因为无控制的并发会带来一系列的问题，这些问题会导致无法满足我们所需要的结果。因此我们需要控制并发，以实现我们所期望的结果(隔离级别)。

MySQL 隔离级别的实现

InnoDB 通过加锁的策略来支持这些隔离级别。

行锁包含：

Record Locks

索引记录锁，索引记录锁始终锁定索引记录，即使表中未定义索引，

这种情况下，InnoDB 创建一个隐藏的聚簇索引，并使用该索引进行记录锁定。

Gap Locks

间隙锁是索引记录之间的间隙上的锁，或者对第一条记录之前或者最后一条记录之后的锁。

间隙锁是性能和并发之间权衡的一部分。

对于无间隙的数据范围不需要间隙锁，因为没有间隙。

Next-Key Locks

索引记录上的记录锁和索引记录之前的 gap lock 的组合。

假设索引包含 10、11、13 和 20。

可能的next-key locks包括以下间隔，其中圆括号表示不包含间隔端点，方括号表示包含端点：

(负无穷大, 10]    (10, 11]    (11, 13]    (13, 20]    (20, 正无穷大)        对于最后一个间隔，next-key将会锁定索引中最大值的上方，

左右滑动进行查看

"上确界"伪记录的值高于索引中任何实际值。

上确界不是一个真正的索引记录，因此，实际上，这个 next-key 只锁定最大索引值之后的间隙。

基于此，当获取的数据范围中，数据已填充了所有的数据范围，那么此时是不存在间隙的，也就不需要 gap lock。

对于数据范围内存在间隙的，需要根据隔离级别确认是否对间隙加锁。

默认的 REPEATABLE READ 隔离级别，为了保证可重复读，除了对数据本身加锁以外，还需要对数据间隙加锁。

READ COMMITTED 已提交读，不匹配行的记录锁在 MySQL 评估了 where 条件后释放。

对于 update 语句，InnoDB 执行 "semi-consistent" 读取，这样它会将最新提交的版本返回到 MySQL，

以便 MySQL 可以确定该行是否与 update 的 where 条件相匹配。

总结&延展：

唯一索引存在唯一约束，所以变更后的数据若违反了唯一约束的原则，则会失败。

当 where 条件使用二级索引筛选数据时，会对二级索引命中的条目和对应的聚簇索引都加锁；所以其他事务变更命中加锁的聚簇索引时，都会等待锁。

行锁的增加是一行一行增加的，所以可能导致并发情况下死锁的发生。

例如，

在 session A 对符合条件的某聚簇索引加锁时，可能 session B 已持有该聚簇索引的 Record Locks，而 session B 正在等待 session A 已持有的某聚簇索引的 Record Locks。

session A 和 session B 是通过两个不相干的二级索引定位到的聚簇索引。

session A 通过索引 idA，session B通过索引 idB 。

当 where 条件获取的数据无间隙时，无论隔离级别为 rc 或 rr，都不会存在间隙锁。

比如通过唯一索引获取到了已完全填充的数据范围，此时不需要间隙锁。

间隙锁的目的在于阻止数据插入间隙，所以无论是通过 insert 或 update 变更导致的间隙内数据的存在，都会被阻止。

rc 隔离级别模式下，查询和索引扫描将禁用 gap locking，此时 gap locking 仅用于外键约束检查和重复键检查（主要是唯一性检查）。

rr 模式下，为了防止幻读，会加上 Gap Locks。

事务中，SQL 开始则加锁，事务结束才释放锁。

就锁类型而言，应该有优化锁，锁升级等，例如rr模式未使用索引查询的情况下，是否可以直接升级为表锁。

就锁的应用场景而言，在回放场景中，如果确定事务可并发，则可以考虑不加锁，加快回放速度。

锁只是并发控制的一种粒度，只是一个很小的部分：

从不同场景下是否需要控制并发，(已知无交集且有序的数据的变更，MySQL 的 MTS 相同前置事务的多事务并发回放)

并发控制的粒度，(锁是一种逻辑粒度，可能还存在物理层和其他逻辑粒度或方式)

相同粒度下的优化，(锁本身存在优化，如IX、IS类型的优化锁)

粒度加载的安全&性能(如获取行锁前，先获取页锁，页锁在执行获取行锁 *** 作后即释放，无论是否获取成功)等多个层次去思考并发这玩意。

以上就是关于sqlserver锁表机制全部的内容，包括:sqlserver锁表机制、DB2数据库发生死锁了怎么办、SQL Server表锁定原理以及如何解除锁定等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！