SQL事务及锁的整理

最新推荐文章于 2023-06-03 01:23:20 发布

我是小数位

最新推荐文章于 2023-06-03 01:23:20 发布

阅读量735

点赞数

分类专栏： SQLServer 基本原理文章标签： sql server 数据库事务死锁

本文链接：https://blog.csdn.net/CXJ0062008/article/details/77362242

版权

SQLServer 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

基本原理

2 篇文章 0 订阅

订阅专栏

1.数据库SQL语句执行原理

1.1.当客户端执行一条T-SQL语句给SQL Server服务器时，会首先到达服务器的网络接口，网络接口和客户端之间有协议层。

1.2.客户端和网络接口之间建立连接。使用称为“表格格式数据流”(TDS) 数据包的Microsoft 通信格式来格式化通信数据。

1.3.客户端发送TDS包给协议层。协议层接收到TDS包后，解压并分析包里面包含了什么请求。

1.4.命令解析器解析T-SQL语句。命令解析器会做下面几件事情：

A.检查语法。发现有语法错误就返回给客户端。下面的步骤不执行。

B.检查缓冲池（Buffer Pool）中是否存在一个对应该T-SQL语句的执行计划缓存。

C.如果找到已缓存的执行计划，就从执行计划缓存中直接读取，并传输给查询执行器执行。

D.如果未找到执行计划缓存，则在查询执行器中进行优化并产生执行计划，存放到Buffer Pool中。

1.5.查询优化器优化SQL语句

当Buffer Pool中没有该SQL语句的执行计划时，就需要将SQL传到查询优化器，通过一定的算法，分析SQL语句，产生一个或多个候选执行计划选出开

销最小的计划作为最终执行计划。然后将执行计划传给查询执行器。

1.6.查询执行器执行查询(查询执行器把执行计划通过OLE DB接口传给存储引擎的数据访问方法。

1.7.数据访问方法生成执行代码(数据访问方法将执行计划生成SQL Server可操作数据的代码，不会实际执行这些代码，传送给缓冲区管理器来执行。)

1.8.缓冲区管理器读取数据。

先在缓冲池的数据缓存中检查是否存在这些数据，如果存在，就把结果返回给存储引擎的数据访问方法；如果不存在，则从磁盘（数据文件）中读出数据并

放入数据缓存中，然后将读出的数据返回给存储引擎的数据访问方法。

1.9.对于读取数据，将会申请共享锁，事务管理器分配共享锁给读操作。

1.10.存储引擎的数据访问方法将查询到的结果返回关系引擎的查询执行器。

1.11.查询执行器将结果返回给协议层。

1.12.协议层将数据封装成TDS包，然后协议层将TDS包传给客户端。

2.事务

是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，SQLServer能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。

   2.1 事务4大属性：

A:原子性(Atomicity)

事务是数据库的逻辑工作单位，事务中包括的所有操作要么全做，要么全不做。

事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

B:一致性(Consistency)

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以保证数据的一致.

以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护数据的一致性。

C:隔离性(Isolation)

一个事务的执行不能被其他事务干扰。隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

D:持续性/永久性(Durability)

一个事务一旦提交，它对数据库中数据的改变就应该是永久性的,不会被回滚。

2.2 事务分类:

A:显式事务

用begintransaction明确指定事务的开始。

用Committransaction提交事务

用rollbacktrans回滚事务

另显示事务中还有一个特殊命令语句

Set xact_abort on/off , 指定是否回滚当前事务，为on时如果当前sql出错，回滚整个事务，为off时如果sql出错回滚当前sql语句，其它语句照常运行。

B:隐性事务

打开隐性事务：set implicit_transactions on，当以隐性事务模式操作时，SQL Servler将在提交或回滚事务后自动启动新事务。无法描述事务的开始，只需要提交或回滚事务。

C:自动提交事务

SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交，否则回滚。

2.3事务的隔离级别

A.未提交读（Read uncommitted）：在未提交读级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。这个级别会导致很多问题，从性能上来说，未提交读不会比其他的级别好太多，但是缺乏其他级别的很多好处，在实际应用中一般很少使用。

B.已提交读（Read committed）：大多数数据库系统的默认隔离级别都是提交读（mySQL不是）。提交读满足前面提到的隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。

C.可重复读（Repeatable read）：可重复读解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读（Phantom read）问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务中又在该范围插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom row）即没有修改成功的数据。可重复读是MySQL的默认事务隔离级别。

D.可序列化（Serializable）：可序列化是最高的隔离级别。它通过强制事务串行执行，避免了前面所说的幻读问题。简单来说，可串行化会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性的情况下，才考虑用该级别。

E.快照(SanpShot)：SanpShot和Read Committed SanpShot两种隔离（可以把事务已经提交的行的上一版本保存在TEMPDB数据库中）SanpShot隔离级别在逻辑上与Serializable类似
Read Committed SanpShot隔离级别在逻辑上与 Read Committed类似
不过在快照隔离级别下读操作不需要申请获得共享锁，所以即便是数据已经存在排他锁也不影响读操作。而且仍然可以得到和Serializable与Read Committed隔离级别类似的一致性；如果目前版本与预期的版本不一致，读操作可以从TEMPDB中获取预期的版本。

如果启用任何一种基于快照的隔离级别，DELETE和UPDATE语句在做出修改前都会把行的当前版本复制到TEMPDB中，而INSERT语句不需要在

TEMPDB中进行版本控制，因为此时还没有行的旧数据，无论启用哪种基于快照的隔离级别都会对更新和删除操作产生性能的负面影响，但是有利于

提高读操作的性能因为读操作不需要获取共享锁

3.锁：

3.1为什么要引入锁?

解决多个用户同时对数据库的并发操作时会带来以下数据不一致的问题,所以需要引入锁。

A:丢失更新

A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果

B:脏读

A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致

C:不可重复读

A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致

D: 幻象读(虚渎)

是指当事务不是独立执行时发生的一种现象，例如A事务对一个表中的数据进行了批次修改。同时，B事务也修改这个表中的数据，这种修改是向表中插入新的数据。那么，以后事后A事务的用户就发现表中还有没有修改的数据行，就好象发生了幻觉一样。

3.2 锁的分类

锁的类别有两种分法：

A:从数据库系统的角度来看

分为共享锁,独占锁（即排它锁）,和更新锁

共享锁(S锁): 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。

共享 (S) 锁允许事务并发读取(SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。在较低的事务隔离级别中一旦已经读取数据，便立即释放资源的的共享 (S) 锁（所以共享锁的上锁时间一般来说是很短的）。

更新锁(U锁): 用于可更新的操作中。防止当多个会话在读取、随后可能进行的资源更新时发生常见形式的死锁。

更新锁更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。(在可重复读或可序列化事务中）假如两个事务分别获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。

排它锁(X锁): 用于数据修改操作，例如 INSERT、UPDATE 或DELETE。确保同一时刻同一资源不会进行多重更新（做以上操作时，数据库会自动转换成排他锁）。

常用锁的关键字:

Nolock:用于select语句，指定不加共享锁

Holdlock：保持锁，使锁在整个事务中都有效.

Rowlock：使用行级锁，而不使用粒度更粗的页级锁和表级锁。

SERIALIZABLE 用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。

Tablock：使用表锁代替粒度更细的行级锁或页级锁。在语句结束前，SQL Server 一直持有该锁。但是，如果同时指定 HOLDLOCK，

那么在事务结束之前，锁将被一直持有TABLOCKX 使用表的排它锁。该锁可以防止其它事务读取或更新表，并在语句或事务结束前一

直持有。

Updlock：读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结束

另外还有意向锁
意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。详情请参考连接

1.http://www.cnblogs.com/woodytu/p/5500270.html

2.http://www.cnblogs.com/xwdreamer/archive/2012/09/19/2694161.html

3.http://www.jb51.net/softjc/126050.html

B.从程序员的角度看：

分为乐观锁和悲观锁。

乐观锁：程序员自己管理数据或对象上的锁处理(例如用字段标识值来判断)。

悲观锁：完全依靠数据库的锁来管理锁的工作。

悲观锁及乐观锁之间的选择时，一般我们可以从如下几个方面来判断：

1.响应速度：如果需要非常高的响应速度，建议采用乐观锁方案，成功就执行，不成功就失败，不需要等待其他并发去释放锁

2.冲突频率：(冲突是指对同一资源的需求),如果冲突频率非常高，建议采用悲观锁，保证成功率，如果冲突频率大，乐观锁会需要多次重试才能成功，代价比较大

3.重试代价：如果重试代价大，建议采用悲观锁

4.事务时间：如果事务所花费的时候是很少的，建议采用悲观锁。

3.3锁的粒度和资源的类型层次结构

Microsoft SQLServer 数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。为了尽量减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以提高并发度，但开销较高，因为如果锁定了许多行，则需要持有更多的锁。锁定在较大的粒度（例如表）会降低了并发度，因为锁定整个表限制了其他事务对表中任意部分的访问。但其开销较低，因为需要维护的锁较少。数据库引擎通常必须获取多粒度级别上的锁才能完整地保护资源。这组多粒度级别上的锁称为锁层次结构。例如，为了完整地保护对索引的读取，数据库引擎实例可能必须获取行上的共享锁以及页和表上的意向共享锁。

RID、KEY（行）、PAGE（页）、对象（例如表）、数据库、EXTENT（区）、分配单元（ALLOCATION_UNIT）、堆（HEAP）、以及B树（B-tree）。

4.死锁：

死锁是不同事务之间为了抢占数据资源，而自己所需要的资源都被别人占用,所以造成永久的等待,即够成死锁。

4.1死锁的四个必要条件
A:互斥条件(Mutualexclusion)：资源不能被共享，只能由一个进程使用。
B:请求与保持条件(Hold and wait)：已经得到资源的进程可以再次申请新的资源。
C:非剥夺条件(No pre-emption)：已经分配的资源不能从相应的进程中被强制地剥夺。
D:循环等待条件(Circular wait)：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源

4.2常见的死锁情形

A:事务A拥有对A表的X(独占)锁,事务B拥有对B表的X(独占)锁,在某一时间,事务

A需要B表的资源,而事务B需要A表的资源,此时即够同了死锁

示例代码:

事务A:

begintransaction

update a set address='TT'fromtesta a where id=1

waitfordelay'00:00:10'

select*fromtestb where id=1

committransaction

事务B:

begintransaction

update a set address='TT'fromtestb a where id=1

waitfordelay'00:00:10'

select*fromtesta where id=1

committransaction

B.在同一个表中由书签查找产生的死锁

如果表有聚集索引(区段结构)，那么书签就是从非聚集索引找到聚集索引后，利用聚集索引定位到数据。此处的书签就是聚集索引。如果表没有聚集索引(堆结构)，那么扫描非聚集索引后，通过RID定位到数据，那么此处书签就是RID。

这类死锁产生的原因是书签查找和更新数据产生的僵持状态。简单来说,就是由于Update语句对基本表产生X锁，然后需要对表上的索引也进行更新，而表上的索引正好被另一个连接进行查找，加了S锁，此时又产生书签查找去基本表加了X锁的数据进行书签查找，此时形成死锁。

我们先看一下update和select语句的执行计划

Setstatisticsprofileon

UPDATE testa SET age =age+1 WHEREid = 1

selectid,name,age,addressfromtesta where name='joe'

为什么会产生嵌套循环查询

请参考

http://www.cnblogs.com/lzrabbit/archive/2012/05/21/2499389.html

--高频率update

while(1=1)execp1

--高频率select

while(1=1)execp2

如何消除书签查找?
　 1.使用聚集索引查找，聚集索引的叶子节点就是数据行本身，因此不存在书签查找
　　 2.堆表聚集索引扫描、表扫描，说白了就是啥索引都不建直接全表扫描，肯定不会发生书签查找，不过效率肯定不高.
　　 3.使用非聚集索引的键列包含所有查询或返回的列，这个不靠谱，非聚集索引最大键列数为16，最大索引键大小为900字节，就算你有勇气在16列上全部建立索引，那如果表的列数超过16列了你咋办，还有索引列长度之和不能超过900字节，所以不可能让非聚集索引包含所有列，而且索引涉及到得列越多维护索引的开销也就越大。
　　 4.使用include，索引做到只能包含16列且不能超过900字节，include不受此限制，最多可以包含1023列怎么也够用了，而且对长度也没有限制你可以随心所欲的包含nvarchar(max)这也的列，但是text,image类型的列是不行的。(所以创建数据库表时注意，像我们现在系统中就很多表结构都是text字段类型，会对现有的性能及后续的优化带来不必要的麻烦，今后最少尽量少用text字段)