SQL server 中锁机制详解

"SQL Server 锁机制详解" SQL Server 中的锁机制是为了提供并发控制，防止多个事务同时访问同一个资源时出现的问题。锁机制可以分为悲观锁和乐观锁两种。 悲观锁是一种保守的锁机制，为任何操作（即使是 select）锁定资源，前提是假定用户冲突和回滚的可能性极大。乐观锁是在数据变化时才加锁，并发性高，前提是假定用户冲突是很小的。 在 SQL Server 中，锁机制分为四个级别：表级、盘区级、页级、行级。锁的分类有共享锁、更新锁、排它锁、意图锁、系统锁等。 共享锁（S）：SQL Server 对 select 实施共享锁。共享锁可以和一些锁共存，尤其是其它共享锁，但不能和排它锁共存。 排它锁（X）：在事务对记录修改时，SQL Server 实施排它锁，其它事务看不到该数据，直到提交。 更新锁（U）：当数据修改时，首先实施更新锁，遍历整张表，找到要修改的记录，再将更新锁提升为排它锁，进行修改。更新锁不能与更新锁或者排它锁共存，但可与共享锁共存。 意图锁（I）：用来处理有多个所要求的情况。例如：表级的意图排它锁就是在表内部放置锁定某个页的排它锁。这将会阻止另一个事务在整个表上放置排它锁。这有利于提高性能。 锁饥饿问题：例如，事物 1 对记录施加了共享锁，事物 2 要更新数据，因此施加更新锁，并等待事务 1 完成后变为排它锁，在事务 2 等待时，事物 3 也在该位置放置了共享锁，然后事务 4 页放置了共享锁……这样下去事务 2 就不能更新记录，因为共享锁优先于排它锁，这是 SQL Server 7 之前的版本，为了避免锁饥饿，Lock Manager 会阻塞其他共享锁。 死锁：典型例子为两个进程都要更新对方锁定的记录，谁也不愿释放自己已实施的锁。这将导致无限等待。SQL Server 发现这个问题时，会选择一个事务杀掉。避免死锁的方法是施加一定的规则，例如按照一定顺序来更新表。 设置锁选项一般让 SQL Server 自动处理锁。有时用户需要控制锁，例如其它用户在修改数据时，也想让用户能浏览数据。例如： select * from table1 with(锁的类别) HoldLock：将共享锁保留到事务完成，等同于 serializable； select * from table1 with (HoldLock) PagLock：页级锁； select * from table1 with (PagLock, HoldLock) TabLock：表级锁； select * from table1 with (TabLock, HoldLock) 要查看锁，打开企业管理器，展开控制台根目录\Microsoft SQL Servers\SQL Server 组\机器名\管理\当前活动\锁/对象。在每次事务执行后，提交之前，刷新“当前活动”，再展开到“锁/对象”。 HoldLock：将使锁定一直保持，直到 commit，若无 HoldLock，则在语句结束后释放锁。 NoLock 选项（with (NoLock)）若想浏览数据，但不更改，且不需要精确的数据，则可用 with(NoLock)，可以提高响应速度。 事务隔离级别缺省情况下，SQL Server 采用保守的方法（悲观锁）来进行并发控制。Oracle 使用乐观锁作为缺省。决定悲观锁还是乐观锁的直接因素：被访问的数据集的大小和参与访问的用户数目。 SQL Server 中采用悲观锁还是乐观锁，取决于： 1：对用户实施不正确数据的可能性； 2：事务回滚的可能性； 3：事务自身的长度。 ANSI 定义了 4 种不同的隔离级别： 读提交（READCOMMITTED）：SQL Server 缺省行为，采用悲观锁； 读未提交（READUNCOMMITTED）：乐观锁，最低的隔离级别，等同于 NoLock； 可重复读（REPEATABLEREAD）：和 2 个 select 语句联系起来的，要求 2 个 select 返回同一个数据集，当两次读之间有记录插入时，会造成幻觉记录； 顺序读（SERIALIZABLE）：最高的隔离级别。事务彼此隔离，等同于 HoldLock。 SQL Server 默认是读提交隔离级别，在服务器级不能改变，只能在当前会话中改变。例如： 打开两个查询窗口，第一个窗口运行一个事务： begin transaction select * from table1 update table1 set col1 = 'new value' commit 第二个窗口运行一个事务： begin transaction select * from table1 commit 在第一个窗口 commit 之前，第二个窗口中的 select 语句将不能返回最新的数据，因为第一个窗口的更新操作还没有提交。