当前位置：首页 > article >正文

SQL server中的事务与锁

article 2025/3/31 16:41:22

一、事务

1.1、事务的概念

1.2、事务的特性和分类

1.3、事务的隔离级别

1.4、事务的保存点

1.5、并发事务造成的问题

二、锁

2.1、锁的分类

2.1.1、按照锁模式分类

2.1.2、按照锁的颗粒度分类

2.1.3、HOLDLOCK 锁

2.2、死锁

2.2.1、死锁产生原因

2.2.2、死锁的检测和处理

一、事务

1.1、事务的概念

事务是一组不可分割的数据库操作序列，这些操作要么全部执行成功，要不全部不执行，以此来保证数据库数据的一致性和完整性。

1.2、事务的特性和分类

事务的特性：原子性、一致性、隔离性和持久性。

原子性：事务是不可分割的工作单元，要么全部执行，要么全部不执行。
一致性：事务结束的时候，所有的内部数据都是正确的。
隔离性：多个事务并发执行时，每个事务的执行都不能被其它事务干扰。各个事务不干涉内部数据，处理的都是另外一个事务处理之前或之后的数据。
持久性：事务提交后，对数据的修改是永久的，不可再回滚。

事务的分类：自动提交事务、显示事务、隐式事务。

自动提交事务：这是 SQL Server 的默认事务模式，每条 SQL 语句都被视为一个单独的事务，执行完毕后自动提交。

显示事务：用户显示地定义事务的开始和结束。使用 BEGIN TRANSACTION 语句开始一个事务，使用 COMMIT TRANSACTION 语句来提交事务，使用 ROLLBACK TRANSACTION 语句回滚事务。

- - 开始一个显示事务

BEGIN TRANSACTION

- - 执行一系列数据库操作

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;

UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;

- - 检查是否有错误发生

IF @@ERROR = 0

BEGIN

- - 如果没有错误发生，提交事务

COMMITE TRANSACTION;

PRINT '事务已提交';

END

ELSE

BEGIN

- - 如果有错误，回滚事务

ROLLBACK TRANSACTION;

PRINT '事务回滚';

END

隐式事务：在隐式事务中会自动启动一个事务，在用户执行下一个事务之前，前一个事务会自动提交或回滚。使用 SET IMPLICIT_TRANSACTIONS ON 语句开启隐式事务模式，使用 SET IMPLICIT_TRANSACTIONS OFF 语句关闭隐式事务。

- - 开启隐式事务模式

SET IMPLICIT_TRANSACTIONS ON;

- - 执行数据库操作，此时会自动开启一个事务

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;

- - 提交或回滚事务

IF @@ERROR = 0

COMMIT TRANSACTION;

ELSE

ROLLBACK TRANSACTION;

- - 关闭隐式事务模式

SET IMPLICIT_TRANSACTIONS OFF;

1.3、事务的隔离级别

SQL Server提供了 5 中不同的隔离级别，用于控制事务之间的隔离程度，不同的隔离级别会影响事务的并发性能和数据的一致性。隔离级别分为快照隔离、可串行化、可重复读、读未提交和读已提交。

READ UNCOMMITTED（读未提交）：允许事务读取其它事务未提交的数据，可能会造成脏读，隔离级别最低，可以提高并发性能。适用于大数据分析、日志分析等对数据一致性要求不高的场景。

理解：允许事务读取另一个事务修改了但还未提交的数据，如果被读取的事务出现回滚时，该事务就读取到了数据库中没有真正存在过的数据，造成了脏读。

READ UNCOMMITTED（读已提交）：这是 SQL Server 的默认隔离级别，事务只能读取其它事务已提交的数据，避免了脏读的问题，但可能会造成不可重复读和幻读现象。适用于购物车、银行余额查询等虽然要求一定的数据正确性，但能容忍一定程度的延迟效应存在的场景。

理解：事务在读取数据时，会等待其它事务对该数据的写操作提交后再进行读取，保证了读取的数据已经确定。但在事务中多次读取同一数据资源期间，可能会在有其它事务对该资源进行了修改而导致读取到的结果不一致，可能会出现不可重复度和幻读问题。

REPEATABLE READ（可重复读）：在一个事务中，多次读取同一数据时，结果是一致的，避免了脏读和不可重复度的问题，但在多次读取时可能会出现幻读问题。适用于对数据一致性要求较高，且需要在一个事务中多次读取同一数据的场景，如生成报表、统计数据等。

理解：当一个事务读取某个数据时，会对该数据加共享锁，其它事务不能对该数据进行修改，直到该事务结束。但在该事务读取数据期间，可能会有其它事务添加的新数据刚好满足该事务读取的条件，从而导致该事务前后读取到的数据不一致出现幻读现象。

SERIALIZABLE（可串行化）：最高隔离级别，事务之间完全串行执行，避免了脏读、不可重复读和幻读的问题，但严重影响了响应并发性能。适用于对数据一致性要求极高，且对并发性能要求不高的场景，如银行转账、库存管理等关键业务操作。

理解：在该隔离级别下会对整个读取范围加锁，其它事务不能在范围内进行插入、更改和删除操作，直到该事务结束。

SNAPSHOT ISOLATION（快照隔离）：使用数据快照来实现事务的隔离，事务读取是事务开始时的数据快照，避免了脏读、不可重复读和幻读的问题，同时还提高了并发性能。需要在数据库级别启用快照隔离功能。适用于对数据一致性要求较高，且需要高并发性能的场景，如电商平台的商品展示、新闻网站的文章等。

理解：在事务开始时，会创建数据快照，该事务执行过程中读取的数据都是这个快照中的数据，不受其它事务修改的影响。同时其它事务的些操作也不会阻塞，提高了并发性能。

隔离级别概述表
隔离级别	脏读	不可重复读	幻读	并发性	实现机制
read uncommitted	可能	可能	可能	最高	无锁读取
read committed（默认）	不可能	可能	可能	高	读后释放共享锁
repeatatle read	不可能	不可能	可能	中	持有共享锁至事务结束
serializable	不可能	不可能	不可能	低	范围锁
snapshot	不可能	不可能	不可能	中高	行版本控制

各隔离级别下的锁行为
隔离级别	读操作锁行为	写操作锁行为	持有锁时间
read uncommitted	不获取 S 锁 (允许脏读)	获取 X 锁	写锁保持到事务结束
read committed（默认）	获取 S 锁，读取后立即释放	获取 X 锁	读锁立即释放，写锁到事务结束
repeatatle read	获取 S 锁并保持到事务结束	获取 X 锁	所有锁保持到事务结束
serializable	获取 S 锁和范围锁	获取 X 锁和范围锁	所有锁保持到事务结束
snapshot	不适用锁 (读取版本)	获取 X 锁	写锁保持到事务结束

1.4、事务的保存点

在事务中，可以使用 SAVE TRANSACTION 语句创建一个保存点，使用 ROLLBACK TRANSACTION 语句回滚到指定的保存点。保存点允许在事务中部分回滚操作，而不是整个事务回滚。

BEGIN TRANSACTION;

- - 执行一些操作

UPDATE Table1 SET Cloumn1 = 'Value1' WHERE Id = 1;

- - 创建一个保存点

SAVA TRANSACTION SavaPoint1;

- - 执行更多操作

UPDATE Table2 SET Cloumn2 = 'Value2' WHERE Id = 2;

- - 如果出现错误，回滚到保存点

IF @@ERROR <> 0

BEGIN

ROLLBACK TRANSACTION SavaPoint1;

END

- - 提交事务

COMMIT TRANSACTION;

1.5、并发事务造成的问题

在并发环境中，多个用户使用事务同时访问同一资源的情况下，可能会造成一下问题：

丢失更新（Lost Update）：多个用户同时对一数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。
不可重复度（Non-Repeatable Read）：一个用户在一个事务中多次读取数据期间，另一个用户对该数据进行了更新，造成前一个用户多次读取数据不一致。
脏读（Dirty Read）：一个用户在一个事务中读取了另一个事务已修改但还未提交的数据，但被读取的事务发生了回滚，导致这个用户读取了数据库中从未真正存在过的数据，造成了该用户出现脏读现象。
幻读（Phantom Read）：一个用户在一个事务中多次读取数据资源，其它事务不能对该数据资源进行修改，另一个用户新增了数据，且恰好在第一个用户读取的范围内，造成了第一个用户在事务中多次读取不一致的情况。
死锁（Deadlock）：两个或多个事务相互等待对方持有的资源，导致所有事务都无法继续执行。
资源竞争（Resource Contention）：多个事务同时请求同一资源，可能导致性能瓶颈，特别是在高并发环境下。

为了解决这些问题，数据库系统通常采用以下策略：

锁机制（Locking）：通过行级锁、表级锁或其他锁类型来控制对数据的访问，以防止并发事务间的冲突。
事务隔离级别（Transaction Isolation Levels）：数据库提供了不同的隔离级别，如 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE，以控制事务间的可见性和并发性。
乐观并发控制（Optimistic Concurrency Control）：在事务提交时检查是否违反了一致性约束，而不是在事务执行时锁定资源。
悲观并发控制（Pessimistic Concurrency Control）：在事务开始时就锁定资源，以确保事务的一致性。
死锁检测和解决策略：数据库系统通常内置了死锁检测机制，能够在检测到死锁时自动回滚其中一个事务。
索引和查询优化：通过优化索引和查询来减少锁的竞争，提高并发性能。

二、锁

锁是数据库用于控制并发访问共享资源的机制。事务对某个资源进行操作时，会对资源加锁，其它事务需要等待该锁释放资源后才能对统一资源进行操作，可以避免数据不一致的问题。

2.1、锁的分类

锁的分类可以从锁的模式和锁的颗粒度分为两大类，锁的模式定义了事务对资源的访问方式，而锁的颗粒度分类决定了锁的作用域范围大小。

2.1.1、按照锁模式分类

共享锁（Shared Lock，S锁）：用于读取操作，允许多个事务读取数据。只允许读取，不允许写操作。

兼容性：与其它 S 锁兼容，与 X 锁不兼容。

排它锁（Exclusive Lock, X）：用于写操作（如 INSERT, UPDATE, DELETE），确保事务独享资源，其它事务不得读取、写操作。

其它事务只有使用 READ UNCOMMITTED（读未提交）才能读取。

兼容性：与其它锁都不兼容（包括 S、U、X）。

更新锁（Update Lock，U锁）：用于先读取后再修改的情况（UPDATE），防止多个事务同时更新同一资源导致的死锁。

行为：事务在修改数据前先获取 U 锁，在修改时才升级为 X 锁，事务修改提交后释放 X 锁。U 锁的设计是为了减少单资源修改的锁竞争，但它无法完全解决跨资源交叉访问导致的循环等待问题。锁转换过程：S → U → X

兼容性：U 锁与 S 锁兼容，如 X 锁不兼容，与其它 U 锁不兼容。

锁类型	S 锁	X 锁	U 锁
S 锁	✔	❌	✔
X 锁	❌	❌	❌
U 锁	✔	❌	❌

意向锁（Intent Lock）：用于表明某个事务打算对某个表的某个资源加上共享锁或排它锁。意向锁是一种表级锁，用于协调不同粒度的锁（如行锁与表锁）。

示例：如果一个事务打算对表中的某一行数据添加排它锁，它会先在表上加上意向排它锁（IX 锁），以表明该事务有在表的行上加上排它锁的意图。

IS（意向共享锁）：表明事务将在某些行上获取 S 锁。
IX（意向排他锁）：表明事务将在某些行上获取 X 锁。
SIX（共享+意向排他锁）：表级锁，表示事务持有 S 锁并将在某些行上获取 X 锁。

架构锁（Schema Lock）：在修改数据库结构时（如给表添加字段等），阻止事务对该表的任何操作。

大容量锁（Bulk Update Lock，BU 锁）：允许多个线程将大容量数据并发的插入到同一个表中，在加载的同时，不允许其它进程访问该表。

2.1.2、按照锁的颗粒度分类

锁的颗粒度指的是锁作用的数据库范围大小。锁的颗粒度小，并发性能高但开销大，锁的颗粒度大，并发性能小，但开销小。

行锁（ROW LOCK）：锁定单行记录，是粒度最小的锁。

页锁（Page Lock）：锁定一个页的数据，通常包含多行（一页大小为8KB）。介于行锁与表锁之间，并发性能也介于两者之间。

表锁（Table Lock）：是颗粒度最大的锁，锁定整个表，阻止其它事务对该表的并发访问。

2.1.3、HOLDLOCK 锁

HOLDLOCK 锁是 SQL Server中的一个表提示，严格意义上来讲它不是锁，它的目的是让与他一起使用的锁延迟释放，一直持有锁到事务结束。因为它会让其它锁一直保持到事务结束才释放，故我称HOLDLOCK 锁为保持锁。

BEGIN TRANSACTION;

SELECT * FROM TableName WITH (HOLDLOCK)

WHERE ID = 1;

COMMIT TRANSACTION;

如果不使用 HOLDLOCK锁，共享锁会在查询数据后就释放锁资源，当使用了HOLDLOCK锁后，共享锁会延迟释放，直到事务提交为止。

个人理解：HOLDLOCK锁的目的就是保持与它一起使用的锁保持到事务结束。