数据库管理事务和锁

第 3 节：理解 SQL Server 锁定体系结构
• 可通过锁防止的并发问题 • 可锁定资源 • 锁类型 • 锁兼容性
可通过锁防止的并发问题
若没有锁定机制，可能发生以下问题： • 丢失更新 • 未提交的依赖关系（脏读） • 不一致的分析（不可重复读） • 幻读 • 由于行更新导致读取缺失和重复读
可锁定资源
SQL Server 可锁定以下资源：
RID KEY PAGE EXTENT
HoBT
用于锁定堆中单个行的行标识符。 索引中用于保护可序列化事务中的键范围的行锁。 数据库中的 8 KB 页，例如数据页或索引页。 一组连续的八页，例如数据页或索引页。
堆或 B 树。该锁保护没有聚集索引的表中的 B 树 （索引）或堆数据页。
• 锁定超时 • 限制等待锁定的资源的时间 • 使用 SET LOCK_TIMEOUT
锁升级
锁升级将很多细粒度的锁转换为较少的粗粒度锁
动态锁定
动态 锁定
成本
行 锁定成本 并发成本
页
表
粒度
死锁
• 如果有两个或更多任务，其中每个任务都锁定了其他任务试图锁定的 资源，从而造成永久相互阻塞，此时就会发生死锁。
锁
不兼容
共享(S)
排他(X)
更新(U)
除共享(S)外的所有锁
排他(X)
所有其他锁
第 4 节：管理锁
• 会话级锁定选项 • 锁升级 • 动态锁定 • 死锁 • 查看锁定信息的方法 • 演示：查看锁定信息
会话级锁定选项
• 事务隔离级别 • READ UNCOMMITTED • READ COMMITTED（默认） • REPEATABLE READ • SERIALIZABLE
用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。
意向
用于建立锁的层次结构。
架构
在执行依赖于表架构的操作时使用。
大容量更新 (BU) 在将数据大批量复制到表，且指定了 TABLOCK 提示时使用。
键范围
在使用可序列化事务隔离级别时，保护查询读取的行范围。
锁兼容性
• 并非所有锁都与其他锁兼容。例如：
任务 1
资源 1
任务 2
资源 2
查看锁定信息的方法
活动监视器
Leabharlann Baidu
动态管理视图 sys.dm_tran_
locks
SQL Server Profiler
可靠性和性能监 视器
演示：查看锁定信息
在本演示中，你将看到如何： • 使用活动监视器查看锁定信息 • 使用动态管理视图查看锁定信息
实验：管理事务和锁
显式事务
显式声明事务开始和结束的事务 • BEGIN TRANSACTION • COMMIT TRANSACTION • ROLLBACK TRANSACTION
BEGIN TRANSACTION fund_transfer EXEC debit_checking '100', 'account1' EXEC credit_savings '100', 'account2'
• 实验 13-1：使用事务 • 实验 13-2：管理锁
登录信息
虚拟机 用户名 密码
NY-SQL-01 Administrator
Pa$$w0rd
预估时间：60 分钟
演示：介绍事务
在本演示中，你将看到如何： • 对一次简单数据更新使用事务 • 如果事务成功如何提交事务 • 如果出现错误如何回滚事务
第 2 节：管理事务
• 自动提交事务 • 显式事务 • 隐式事务 • 事务恢复 • 使用事务的注意事项 • 受限语句
自动提交事务
• 默认事务模式 • 每个 TSQL 语句完成时，都将提交或回滚。如果成功，则提交；如果有
-任务 T1 有资源 R1 的锁（从 R1 到 T1 的箭头），并请求 获得资源 R2 的锁（从 T1 到 R2 的箭头）。
-任务 T2 有资源 R2 的锁（从 R2 到 T2 的箭头），并请求 获得资源 R1 的锁（从 T2 到 R1 的箭头）。
-由于两个任务要在资源可用后才能继续，而两个资源要在任务 继续后才能释放，于是出现死锁状态。
锁
• 用来同步多用户同时访问同一数据的机制
• 两种主要类型的锁：
-读锁– 允许其他人读取，但不允许写入 -写锁– 阻止其他人读取和写入
• 可能发生死锁
• 锁防止更新冲突
-锁定确保事务是串行化 -锁定是自动的 -锁使得并发使用数据成为可能
并发控制
两种主要的并发控制类型： • 悲观
-在读取数据准备更新时锁定数据 -其他用户将受到阻止，直到锁释放 -在数据争用较高时使用 • 乐观 -执行更新时锁定数据 -如果数据自初次读取后发生更改会收到错误 -在数据争用较低的情况下使用
第 13 章 管理事务和锁
第 13 章：管理事务和锁
• 事务和锁概述 • 管理事务 • 理解 SQL Server 锁定体系结构 • 管理锁
第 1 节：事务和锁概述
• 事务 •锁 • 并发控制 • 演示：介绍事务
事务
事务是原子工作单元 事务使数据处于一致的状态
事务与其他并发事务隔离 事务是持久的
错，则回滚 • 编译错误将导致整个批处理不执行 • 运行时错误可能允许批处理中的一部分提交 • 数据库引擎以自动提交模式工作，直到显式事务启动 • XACT_ABORT 设置 ON 确保整个批处理将在出现任何运行时错误时
回滚；编译错误不受 XACT_ABORT ON 影响
SET XACT_ABORT {ON | OFF }
INSERT OPEN REVOKE SELECT TRUNCATE TABLE UPDATE
• 事务必须以 COMMIT 或 ROLLBACK TRANSACTION 显式完成
事务恢复
事务恢复 1
2 3 4 5
需要的操作
无 前滚 回滚 前滚 回滚
检查点
系统失败
使用事务的注意事项
使事务尽可能短 -不要求用户输入 -不浏览数据 -访问尽可能少的数据 -需要事务时才打开事务
嵌套事务的注意事项 -允许，但不建议 -使用 @@trancount 确定嵌套级别
受限语句
• 受限语句不能包含在任何显式事务中 • 全文系统存储过程调用不可包含在显式事务中 • 在隐式或显式事务中不可使用以下存储过程：
• sp_dboption（不推荐使用） • 修改 master 的系统存储过程
TABLE FILE APPLICATION METADATA ALLOCATION_UNIT DATABASE
整个表，包括所有数据和索引的。 数据库文件。 应用程序指定的资源。 元数据锁。 分配单元。 整个数据库。
锁类型
锁模式 共享 (S) 更新 (U)
说明 用于读操作。 对可更新的资源使用。
排他 (X)
COMMIT TRANSACTION
• SAVE TRANSACTION • 事务日志 – 使用 WITH MARK 指定日志中标记的事务
隐式事务
• 将隐式事务模式设置为 ON
SET IMPLICIT_TRANSACTION ON
• 执行以下语句时，隐式事务将启动
ALTER TABLE CREATE DELETE DROP FETCH GRANT