Sql Server 锁、事务隔离级别

最新推荐文章于 2025-05-22 11:15:38 发布

原创最新推荐文章于 2025-05-22 11:15:38 发布 · 441 阅读

1 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

4 篇文章

订阅专栏

本文深入探讨了SQLServer中锁的必要性，包括解决丢失更新、脏读、不可重复读和幻读等问题。介绍了锁的种类，如共享锁、排它锁、更新锁等，以及锁的粒度对并发性和开销的影响。详细解释了如何通过SQL语句使用不同类型的锁，并讨论了事务隔离级别的选择，如readuncommitted、readcommitted、repeatablecommitted和serializable，及其对数据一致性和并发性的平衡。

1.为什么需要锁（锁是为了解决什么问题）

丢失更新：在这种情况下，事务与事务之间没有隔离。多个事务能够读取同一份数据并且修改它。最后对数据集做出修改的事务将胜出，而其他所有事务所作的修改都丢失了。（Sql Server中对数据进行修改时需要排它锁锁住修改，其他任何事务对该数据的查询或修改均需要等待排它锁被释放，所以个人认为Sql Server并不会发生这种问题。）
脏读：在这种情况下，一个事务能够读取正被其他事务修改的数据。第一个事务读取的数据，另一个事务可能会回滚所作的修改。（read uncommitted、select中使用nolock均会出现这种问题）
不可重复读：一个事务两次读取数据，在第二次读取发生前，另一个事务修改了该数据；导致两次读取所得到的结果是不同的。（read uncommitted、read committed均会出现这种问题）
幻读：一个事务两次读取数据，在第二次读取发生前，另一个事务插入了新数据；导致第二次查出了额外的记录行。（read uncommitted、read committed、repeatable committed均会出现这种问题）
事务隔离级别是read committed或更高级、查询时未使用nolock，可避免脏读
事务隔离级别是repeatable committed或更高级、第一次查询时使用holdlock或使用其他锁住查询数据的方式，可避免不可重复读
事务隔离级别是serializable或更高级、第一次查询时使用表级锁锁住表，可避免幻读

2.锁的种类

共享锁(S)：允许其他事务读出数据，但不能修改数据。一旦已经读取数据，便立即释放资源上的共享锁(S)，除非将事务隔离级别设置为repeatable committed或更高级别，或者在事务生存周期内用锁定提示保留共享锁(S)。（共享锁之间不互斥，与更行锁也不互斥，但和排它锁互斥）
更新锁(U)：一次只有一个事务可以获得资源的更新锁(U)。更新锁之间互斥，资源在同一时间只能被一个事务持有更新锁。当SQL Server准备更新数据时，它首先对数据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到SQL Server确定要进行更新数据操作时，他会自动将更新锁换为排它锁。
排它锁(X)（独占锁）：其他事务不能读取或修改排它锁(X)锁定的数据。资源同一时间只能被一个事务持有排它锁，排他锁与任何锁互斥。
意向锁(I)：表示 SQL Server 需要在层次结构中的某些底层资源上获取共享锁(S)、更新锁(U)或排它锁(X)。
架构锁：在执行依赖于表架构的操作时使用。架构稳定性锁（Sch-S）- 当事务引用了索引或数据页时，SQL Server在对象上加Sch-S锁。这确保当其他进程仍然引用着该对象时，没有其他事务能够修改该对象的Schema，如删除索引或删除、修改存储过程或表。架构修改锁（Sch-M） - 当一个进程需要修改某对象的结构（如修改表，重编译存储过程）时， Lock Manager在对象上加Sch-M锁。在锁存在期间，没有其他任何事务能够引用该对象，直到（对象结构的）修改完成并提交为止。
大容量更新锁(BU)：大容量更新锁是一种特殊类型的锁，仅用于使用bcp实用程序或者BULK INSERT命令向表中大容量复制数据时。仅仅当给bcp或BULK INSERT命令指定了TABLOCK提示，或者使用 sp_tableoption 设置了 table lock on bulk load 表选项时,BU锁才能用于大容量数据复制操作。大容量更新 (BU) 锁允许多个 bulk copy 进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。如果有任何其他进程在该表上持有锁，则不能给该表施加BU锁。
Key-Range

3.锁的粒度

锁的粒度：被封锁目标的大小（封锁粒度小则并发性高,但开销大,封锁粒度大则并发性低但开销小）
SQL Server支持的锁粒度可以分为为行、页、键、键范围、索引、表或数据库获取锁
锁的粒度越小，能够同时访问同一张表的并发用户的数量就越大，不过维护这些锁的管理开销也越大。
锁的粒度越大，管理锁需要的开销就越少，而并发性也降低了。

4.锁的使用

语法：SQL语句表名后使用 with(锁类型)

holdlock：在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。
nolock：不添加共享锁和排它锁，无视其他事务添加的排它锁，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于select语句。
readpast：跳过已经加锁的数据行，将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，readpast仅仅应用于read committed或repeatable read隔离性级别下事务操作中的select语句
rowlock：使用行级锁，而不使用粒度更粗的页级锁和表级锁。
paglock：指定添加页锁。
tablock：指定使用表级锁，而不是使用行级或页面级的锁，在该语句执行完后释放这个锁，而如果同时指定了holdlock，该锁一直保持到这个语句或整个事务结束。
tablockx：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到整个事务结束。
updlock：指定在读表中数据时设置更新锁（update lock）而不是设置共享锁，一直保持到这个语句或整个事务结束，
使用updlock的作用是允许用户先读取数据（而且不阻塞其他用户读数据），
并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改。
xlock ：指定在读表中数据时设置排它锁

粒度锁：ROWLOCK, NOLOCK, PAGLOCK, TABLOCK, TABLOCKX
模式锁：HOLDLOCK, UPDLOCK, XLOCK

5.事务隔离级别

read uncommitted（未提交读）：等同于nolock，能读到未提交的数据，可能出现脏读。
read committed （已提交读，默认级别）：只能读到提交的数据，避免了脏读，但存在不可重复读问题。在这个级别使用能锁定读出数据的方式（holdlock、updlock、xlock、tablockx......）可以避免不可重复读
repeatable committed（可重复读）：查询语句持有的共享锁直到事务结束才会释放，避免了不可重复读，但存在幻读问题。在这个级别使用锁定表的方式（tablock、tablockx）可以避免幻读
serializable（可序列化）：可以避免幻读。
snapshot（快照）：update和delete时，数据库会将修改前的行存到tempdb中，snapshot模式在读出数据时只会读出事务开始前的版本。
```
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;
```
```
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;
```

read committed snapshot：读取读取前最后提交的版本

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON;

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;

参考：https://blog.csdn.net/huwei2003/article/details/4047191