事务和锁的应用

一.事务管理

SQL Server中事务主要可分为自动提交事务、隐式事务、显示事务和分布式事务4中类型。

类型	含义
自动提交事务	每条语句都是一个事务
隐式事务	前一个事务完成时，新事物隐式启动，每个事务仍以COMMIT或ROLLBACK语句显示结束
显示事务	每个事务均以BEGIN TRANSACTION语句显示开始，以COMMIT或ROLLBACK语句显示结束
分布式事务	跨越多个服务器的事务

1.事务的含义
事务要有非常明确的开始和结束点。例如SELECT、INSERT、UPDATE和DELETE都是隐式事务的一部分。即使只有一条语句，系统也会把这条语句当作一个事务，要么执行所有语句，要么什么都不执行。
2.事务属性
事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性，称为原子性、一致性、隔离性和持久性，检查ACID属性，只有这样才能构成一个事务。
（1）原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
（2）一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
（3）隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
（4）持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
3.事务管理的常用语句
BEGIN TRANSACTION:建立一个事务
COMMIT TRANSACTION:提交事务
ROLLBACK TRANSACTION：事务失败是执行回滚操作
SAVE TRANSACTION:保存事务
4.事务应用案例

--开启事务
BEGIN TRAN
INSERT INTO goodes VALUES(3,'空调',100,7000)
INSERT INTO goodes VALUES(4,'电视',100,3000)
DECLARE @num INT 
SELECT @num=COUNT(*) FROM goodes
IF(@num>3)
BEGIN
--当表中数据大于3条，回滚事务
ROLLBACK TRAN
PRINT '数据大于3条，插入失败'
END
ELSE
BEGIN
--表中数据小于3条，提交事务
COMMIT TRAN
PRINT '插入成功'
END

二.锁

1.锁的内涵和作用
数据库中数据的并发操作经常发生，而对数据的并发操作会带来下面一些问题：脏读、幻读、非重复性读取、丢失更新。
（1）丢失更新
A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果,比如订票系统。
（2）脏读
A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致
（3）非重复性读取
A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致
并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致
2.锁的类型
SQL Server中提供了多种锁模式，在这些类型的锁中，有些类型之间可以兼容，有些类型的锁之间是不可以兼容的。锁模式决定了并发事务访问资源的方式。
更新锁
从数据库系统的角度来看：**分为独占锁（即排它锁），共享锁和更新锁
锁模式描述
共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。
更新 (U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排它 (X) 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。
意向锁用于建立锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。
锁在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
大容量更新 (BU) 向表中大容量复制数据并指定了 TABLOCK 提示时使用。
（1）共享锁
共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。
（2）更新锁
更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。
若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。
（3）排它锁
排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。
（4）意向锁
意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。
（5）死锁
在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，会造成这些任务永久阻塞，从而出现死锁。此时系统处于死锁状态。
形成死锁有四个必要条件：

请求与保持条件：获取资源的进程可以同时申请新的资源。
非剥夺条件：已经分配的资源不能从该进程中剥夺。
循环等待条件：多个进程构成环路，并且其中每个进程都在等待相邻进程正占用的资源。
互斥条件：资源只能被一个进程使用。
减少死锁的策略：
使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务，事务持有锁的时间越短，锁竞争发生的机会就越少；将不是事务所管理的工作单元锁必需的命令移出事务。
设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；
优化程序，检查并避免死锁现象出现；
对所有的脚本和SP都要仔细测试，在正式版本之前。
所有的SP都要有错误处理（通过@error）
一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁
将组成事务的语句作为一个的单独的批命令处理，以消除 BEGIN TRAN 和 COMMIT TRAN 语句之间的网络延迟造成的不必要的延迟。
考虑完全地使用存储过程编写事务代码。典型地，存储过程比批命令运行更快。
在游标中尽可早地Commit更新。因为游标处理比面向集合的处理慢得多，因此导致锁被持有的时间更久。
使用每个进程所需的最低级别的锁隔离。比如说，如果脏读是可接受的并且不要求结果必须精确，那么可以考虑使用事务隔离级别0（Read Uncommitted）,仅在绝对必要时才使用Repeatable Read or Serializable隔离级别。
在 BEGIN TRAN 和 COMMIT TRAN 语句之间，绝不允许用户交互，因为这样做可能锁被持有无限期的时间。

三.锁的应用案例

1.锁定行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM goodes ROWLOCK WHERE id=2

2.锁定数据表

--对表加锁后，其他用户奖不能对该表进行访问
SELECT price FROM goodes TABLELOCKX WHERE price=4200

3.排他锁

--ts2事务中的SELECT语句必须等待ts1执行完毕5秒后才能执行
BEGIN TRAN ts1
UPDATE goodes SET price=6000 WHERE name='洗衣机'
WAITFOR DELAY '00:00:05'
COMMIT TRAN

BEGIN TRAN ts2
SELECT * FROM goodes WHERE name='洗衣机'
COMMIT TRAN

4.共享锁

--允许两个事务同时执行查询操作，如果ts4事务要执行更新操作，则必须等待15秒。
BEGIN TRAN ts3
SELECT * FROM goodes WITH(HOLDLOCK) WHERE name='洗衣机';
WAITFOR DELAY '00:00:15';
COMMIT TRAN

BEGIN TRAN ts4
SELECT * FROM goodes WHERE name='冰箱';
COMMIT TRAN

5.减少死锁可以采用的规则有以下几个。
（1）按同意顺序访问对象
（2）避免事务中的用户交互
（3）保持事务简短并处于一个批处理中。
（4）使用较低的隔离级别。
（5）使用基于行版本控制的隔离级别。
（6）使用绑定连接。