SQL transaction事务以及各种锁、waitfor、脏读、幻读

事务定义

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL语句执行之前的状态。

事务的四个特性ACID

原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）

原子性: 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性: 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性: 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
持久性: 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离级别

读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
未提交读是最低的事务隔离级别，允许读取其他事务已经修改但未提交的数据行。SQL SERVER 当此事务等级进行尝试读取数据时，不会放置共享锁，直接读取数据，所以忽略已存在的互斥锁。换句话说，即使该资源已经受到了独占锁的保护，当使用未提交读隔离级别时，此数据还是可以被读取，加快查询速度，但是会读取到别人未修改的数据，所以此种读取被称为脏读。此种隔离级别适合不在乎数据变更的查询场景。此隔离级别与SELECT 语句搭配 NOLOCK 所起到的效果相同
读提交（read committed） Oracle默认的隔离级别
已提交读是SQL SERVER 默认的事务隔离级别。当事务正在读取数据时，SQL SERVER 会放置共享锁以防止其他事务修改数据，当数据读取完成之后，会自动释放共享锁，其他事务可以进行数据修改。因为共享锁会同时封锁封锁语句执行，所以在事务完成数据修改之前，是无法读取该事务正在修改的数据行。因此此隔离级别可以防止脏读。
可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
可重复读事务隔离级别在事务过程中，所有的共享锁均保留到事务结束，而不是读取结束就释放，这与已提交读的行为截然不同，虽然在事务过程中，重复查询相同记录时不受其他事务的影响，但可能由于锁定数据过久，而导致其他人无法处理数据，影响并发率，更严重的可能提高发生死锁的机率。

总之，如果使用可重复读隔离级别读取数据，数据读出之后，其他事务只能对此范围中的数据进行读取或新增，但不可以进行修改，直到读取事务完成。因此，使用此隔离级别需要谨慎小心，根据实际情况进行设置。

串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发
可序列化是事务隔离级别中最高的级别，为最严谨的隔离级别，因为它会锁定整个范围的索引键，使事务与其他事务完全隔离。在现行事务完成之前，其他事务不能插入新的数据行和修改数据，其索引键值存在于现行事务所读取的索引键范围之中。此隔离级别与Select 搭配holdlock效果一样。

脏读（一个事务读取了另一个事物未提交的数据）
简单来说，就是事务1处理数据已经被修改，在期间被事务2读取，事务1最后被回滚，事务读出的数据就是错误的。当一个事务读取的记录是另一个事务的一部分时，如果第一个事务正常完成，就没有什么问题；如果此时另一个事务未完成，就产生了脏读。例如，员工表中编号为1001的员工工资为1740元，如果事务1将工资修改为1900元，但还没有提交确认；此时事务2读取员工的工资为1900元；事务1中的操作因为某种原因执行了ROLLBACK回滚，取消了对员工工资的修改，但事务2已经把编号为1001的员工的数据读走了，此时就发生了脏读。
不可重复读（一个事务范围内两个相同的查询返回不同的数据）
两个事务同时读数据,一个事务改了数据,另一个事务仍为原数据。如果一个事务不止一次地读取相同的记录，但在两次读取中间有另一个事务刚好修改了数据，则两次读取的数据将出现差异，此时就发生了非重复性读取。例如，事务1和事务2都读取一条工资为2310元的数据行，如果事务1将记录中的工资修改为2500元并提交，则事务2使用的员工的工资仍为2310元。
幻读（事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有 没有修改的数据行）
简单来说，就是删除/更新数据的同时读取数据。当某一数据行执行INSERT或DELETE操作，而该数据行恰好属于某个事务正在读取的范围时，就会发生幻读现象。例如，现在要对员工涨工资，将所有低于1700元的工资都涨到新的1900元，事务1使用UPDATE语句进行更新操作，事务2同时读取这一批数据，但是在其中插入了几条工资小于1900元的记录，此时事务1如果查看数据表中的数据，会发现UPDATE之后还有工资小于1900元的记录。幻读事件是在某个凑巧的环境下发生的，简而言之，它是在运行UPDATE 语句的同时有人执行了INSERT操作。因为插入了一个新记录行，所以没有被锁定，并且能正常运行。
丢失更新
一个事务更新了数据库之后，另一个事务再次对数据库进行更新，此时系统只能保留最后一个数据的修改。

友情提示

对于一般的用户，通过系统的自动锁管理机制【排他锁】基本可以满足使用要求，但如果对数据安全、数据库完整性和一致性有特殊要求，则需要亲自控制数据库的锁和解锁，这就需要了解 SQL Server的锁机制，掌握锁的使用方法。

可锁定的资源

使用SQL Server 2019中的锁机制可以锁定不同类型的资源，即具有多粒度锁，为了使锁的成本降至最低，SQL Server会自动将资源锁定在合适的层次，锁的层次越高，它的粒度就越粗。锁定在较高的层次（例如数据表）就限制了其他事务对数据表中任意部分进行访问，但需要的资源少，因为需要维护的锁较少；锁在较小的层次（例如行）可以增加并发但需要较大的开销，因为锁定了许多行，需要控制更多的锁。对于SQL Server来说，可以根据粒度大小分为6种可锁定的资源，这些资源由粗到细分别是：

数据库：锁定整个数据库，这是一种最高层次的锁，使用数据库锁将禁止任何事务或者用户对当前数据库的访问。

数据表：锁定整个数据表，包括实际的数据行和与该表相关联的所有索引中的键。其他任何事务在同一时间都不能访问表中的任何数据。

数据表锁定的特点是占用较少的系统资源，但是数据资源占用量较大。

区段页：一组连续的8个数据页，例如数据页或索引页。区段锁可以锁定控制区段内的8个数据或索引页以及在这8页中的所有数据行。

页：锁定该页中的所有数据或索引键。在事务处理过程中，不管事务处理数据量的大小，每一次都锁定一页，在这个页上的数据不能被其他事务占用。使用页层次锁时，即使一个事务只处理一个页上的一行数据，那么该页上的其他数据行也不能被其他事务使用。

键：索引中的特定键或一系列键上的锁，相同索引页中的其他键不受影响。

行：在SQL Server2019中可以锁定的最小对象空间就是数据行，行锁可以在事务处理数据过程中，锁定单行或多行数据，行级锁占用资源较少，因而在事务处理过程中，其他事务可以继续处理同一个数据表或同一个页的其他数据，极大地降低了其他事务等待处理所需要的时间，提高了系统的并发性。

锁粒度是被封锁目标的大小,封锁粒度小则并发性高,但开销大,封锁粒度大则并发性低但开销小
锁的粒度和锁的类型都是由SQL Server进行控制的（当然你也可以使用锁提示，但不推荐）。锁会给数据库带来阻塞，因此越大粒度的锁造成更多的阻塞，但由于大粒度的锁需要更少的锁，因此会提升性能。而小粒度的锁由于锁定更少资源，会减少阻塞，因此提高了并发，但同时大量的锁也会造成性能的下降。

步骤为：

开启事务：start transaction； 需要执行的SQL语句集
结束事务：commit transaction 或rollback transaction。
事务存储点：save transaction release transaction
设置存储点目的：将事务退回到事务某个事务存储点，无需撤销整个事务，提高运行效率

语法 格式：

1、启动事务
begin tran[saction] [tran_name | @tran_name_variable [with mark[‘desp’]]]
—with mark [‘desp’]：在日志中标记事务，desp表示描述的字符串
2、结束事务
commit Tran[saction] [tran_name | @tans_name_variable]
或
commit work
3、回滚事务
rollback tran[saction] [tran_name | @tran_name_variable | savepoint_name | @savepoint_variable]
或
rollback work
4、在事务内设置保存点
save tran[saction] [savepoint_name| @savepoint_variable]
5、只读事务
事务对数据库操作只能有读语句
set transaction read only
6、读写事务
set transaction read write
此种定义可缺省，默认事务为读写型

Commit和rollback的区别

Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。

显示事务 隐式事务

显示事务：通过Begin 语句明确指出开始事务，并通过commit和rollback语句明确指出提交或回滚事务
隐式事务：不用显示启动一个事务，但需要用commit和rollback语句结束一个事务
SQL SERVER中可用设置（和取消）隐式事务方法
语法： set IMPLICIT_TRANSACTION ON | OFF
SQL SERVER同时提供自动提交事务模式，是默认事务提交方式
***** 自动提交的事务，若存在编译错误会造成系统不执行批处理中的任何语句，而非回滚错误之前的语句*****

并发控制

事务串行和并发执行
1、串行执行（访问）：一个事务处理完全结束、另一事务才开始执行
2、并发执行：DBMS同时接纳多个事务，使得事务在时间上可交叠执行
单CPU系统的交叉（或分时）并发执行
多CPU系统的同时并发执行。

事务并发执行调度必须符合可串行化标准，即并发执行结果必须与串行执行结果一致
事务并发控制最基本的技术就是封锁（locking）技术

封锁概念

封锁是系统对事务并发执行的一种调度和控制技术，保证系统对系统访问以一种互斥方式进行
封锁技术的基本点对数据对象的操作实施专有控制。
一段时间之内：
1、防止其它事务访问指定资源
2、禁止某些用户对数据对象进行某些操作

封锁类型

1. 排他锁（exclusive lock）：又称为写锁或X锁
   排他锁：某一事务对数据加X锁后，即可对该数据读写；其它事务只能等待这一事务对数据解锁后，才能操作该数据
   排他锁保证了事务对数据的独占性
   排他锁缺点：降低事务的并发执行程度
   

2、共享锁（sharing lock）：又称读锁或S锁
共享锁：某一事务对数据加S锁后，只可读该数据不可写；其它事务在这一事务释放数据之前均只可读该数据不可写，并且只能对该数据加S锁，不能加X锁
共享锁降低了封锁要求，保证多个事务可以同时读取被封锁的数据

更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

封锁粒度 granularity

实行事务封锁的数据目标的大小称为封锁粒度
关系数据库中封锁粒度一般分为逻辑单元和物理单元：
逻辑单元：属性（值）、属性（值）集合、元组、关系表、关系数据库等
物理单元：存数页面、索引等

封锁协议 locking protocol

运用X锁和S锁这两种基本锁时，需要遵循封锁协议
封锁协议可分为三级协议和两段（扩展阶段与收缩阶段）协议
实行三级封锁协议以防止并发执行的三类错误发生
遵循两段协议进行封锁，可保证并发执行符合串行化标准，正确执行
DBMS提供一个锁表记载各个数据对象加锁情况

活锁和死锁

封锁技术可能会引发活锁与死锁问题
活锁：封锁过程中，系统可能使某个事务永远处于等待状态，得不到封锁机会
可采用队列方式作为解决活锁问题的控制策略
死锁：：若干事务都处于等待状态，相互等待对方解锁，造成事务都无法进行，系统进入对锁的循环等待
可通过定时与检测工具，以及主管管理控制解决死锁问题

锁语法

查看锁

SELECT * FROM sys.dm_tran_locks;

锁定行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT *   FROM stu_info
ROWLOCK
WHERE s_id=2;
GO

锁定表

SELECT s_age   FROM stu_info   TABLELOCKX
WHERE s_age=18;
GO

waitfor用法介绍

功能：指定触发语句块、存储过程或事务执行的时间、时间间隔或事件。
语法：WAITFOR { DELAY ‘time’ | TIME ‘time’ }
参数：
　　DELAY：指等过了指定的时间过去后再执行SQL。
　　TIME：指等到了指定的时间点后再执行SQL。
　　‘time’：要等待的时间。可以按 datetime 数据可接受的格式指定 time，也可以用局部变量指定此参数。格式为"HH:MM:SS",不支持日期，最长可达 24 小时。

排他锁

创建名为transactionl和 transaction2的事务，在transactionl 事务上面添加排他锁，事务1执行10s之后才能执行 transaction2 事务。

BEGIN TRAN transaction1
UPDATE stu_info SET s_score = 88 WHERE s_name='许三';
WAITFOR DELAY '00:00:10';
COMMIT TRAN

GO

BEGIN TRAN transaction2
SELECT* FROM stu_info WHERE s_name='许三';
COMMIT TRAN
GO

共享锁

创建名为transactionl和transaction2的事务，在transactionl 事务上面添加共享锁，许两个事务同时执行查询操作，如果第二个事务要执行更新操作，必须等待10秒。

BEGIN TRAN transaction1
SELECT s_score,s_sex,s_age FROM stu_info WITH(HOLDLOCK) WHERE s_name='许三';
WAITFOR DELAY '00:00:10';
COMMIT TRAN
GO
BEGIN TRAN transaction2
SELECT*FROM stu_info WHERE s name='许三';
COMMIT TRAN
GO

解锁语法

设置5秒后锁自动解锁
Set Lock TimeOut 5000
设置立即解锁
Set Lock TimeOut 0

SQL SERVER 锁类型

1． HOLDLOCK: 在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。

2． NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。

3． PAGLOCK：指定添加页锁（否则通常可能添加表锁）。
4． READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL Server 2000 在此隔离级别上操作。
5． READPAST: 跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，READPAST仅仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作。
6． READUNCOMMITTED：等同于NOLOCK。
7． REPEATABLEREAD：设置事务为可重复读隔离性级别。
8． ROWLOCK：使用行级锁，而不使用粒度更粗的页级锁和表级锁。
9． SERIALIZABLE：用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。
10． TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQL Server在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。
11． TABLOCKX：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。
12． UPDLOCK ：指定在读表中数据时设置更新 锁（update lock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改

--------------------------A客户端连接 Lock(乐观锁)------------------------
--DROP TABLE Coupon
-----------------创建优惠券表-----------------
CREATE TABLE Coupon
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Number VARCHAR(50) NOT NULL,
    [User] VARCHAR(50),
    UseTime DATETIME,
    IsFlag BIT DEFAULT(0) NOT NULL,
    CreateTime DATETIME DEFAULT(GETDATE()) NOT NULL
)
INSERT INTO dbo.Coupon(Number) VALUES ( '10000001')
INSERT INTO dbo.Coupon(Number) VALUES ( '10000002')
INSERT INTO dbo.Coupon(Number) VALUES ( '10000003')
INSERT INTO dbo.Coupon(Number) VALUES ( '10000004')
INSERT INTO dbo.Coupon(Number) VALUES ( '10000005')
INSERT INTO dbo.Coupon(Number) VALUES ( '10000006')

--SELECT * FROM dbo.Coupon WITH(NOLOCK) --查询数据
--UPDATE Coupon SET [User]=NULL, UseTime=NULL, IsFlag=0 --还原数据

-----------------1、模拟高并发普通更新-----------------
DECLARE @User VARCHAR(50)    --模拟要使用优惠券的用户
DECLARE @TempId INT            --模拟抽选出来的要使用的优惠券
SET @User='a'
BEGIN TRAN
SELECT @TempId=Id FROM dbo.Coupon WHERE IsFlag=0    --高并发时此语句有可能另外一个该事务已取出的Id
--WAITFOR DELAY '00:00:05'    --改用此方式要开两个SQL Management客户端
UPDATE dbo.Coupon SET IsFlag=1, [User]=@User, UseTime=GETDATE() WHERE Id=@TempId
COMMIT TRAN
--ROLLBACK TRAN

-----------------2、悲观锁解决方案-----------------
DECLARE @User VARCHAR(50)    --模拟要使用优惠券的用户
DECLARE @TempId INT            --模拟抽选出来的要使用的优惠券
SET @User='a'
BEGIN TRAN
SELECT @TempId=Id FROM dbo.Coupon WITH(UPDLOCK) WHERE IsFlag=0    --高并发时此语句会锁定取出的Id数据行
--WAITFOR DELAY '00:00:05'    --改用此方式要开两个SQL Management客户端
UPDATE dbo.Coupon SET IsFlag=1, [User]=@User, UseTime=GETDATE() WHERE Id=@TempId
COMMIT TRAN
--ROLLBACK TRAN

-----------------3、乐观锁解决方案-----------------
ALTER TABLE dbo.Coupon ADD RowVer ROWVERSION NOT NULL --增加数据行版本戳类型字段（微软新推荐数据字段，该字段每张表只能有一个，会在创建行或更新行时自动进行修改无需人为干涉，该字段不能建立索引及主键因为会频繁修改）

DECLARE @User VARCHAR(50)    --模拟要使用优惠券的用户
DECLARE @TempId INT            --模拟抽选出来的要使用的优惠券
DECLARE @RowVer BINARY(8)    --抽选出来的优惠券的版本（ROWVERSION数据类型存储大小为8字节）
SET @User='a'

BEGIN TRY
    BEGIN TRAN
    SELECT @TempId=Id, @RowVer=RowVer FROM dbo.Coupon WHERE IsFlag=0    --取出可用的Id及对应的版本戳
    --WAITFOR DELAY '00:00:05'    --改用此方式要开两个SQL Management客户端
    UPDATE dbo.Coupon SET IsFlag=1, [User]=@User, UseTime=GETDATE() WHERE Id=@TempId AND RowVer=@RowVer
    IF(@@ROWCOUNT > 0)
        BEGIN
            PRINT('修改成功')
            COMMIT TRAN
        END
    ELSE
        BEGIN
            PRINT('该数据已被其他用户修改')
            ROLLBACK TRAN
        END
END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH

--------------------------B客户端连接 Lock(乐观锁)------------------------
--此测试需要开两个SQL Management Studio客户端，在A客户端使用WAITFOR DELAY来模拟并发占用，在B客户端执行与A客户端相同的SQL脚本即可（注释掉WAITFOR），所以在此不放相同代码了。

在乐观锁和悲观锁之间进行选择的标准是：冲突的频率与严重性。如果冲突很少，或者冲突的后果不会很严重，那么通常情况下应该选择乐观锁，因为它能得到更好的并发性，而且更容易实现。但是，如果冲突的结果对于用户来说痛苦的，那么就需要使用悲观策略。

我认为如果同一张表的并发很高，但并发处理同一条数据的冲突几率很低，那就应该使用乐观锁，反之，如果同一张表的并发不高，但同时处理同一条数据的几率很高，就应该使用悲观锁。