SQLServer教程FG015-SQLServer事务与锁机制实战
目录大纲
内容简介
本文档基于SQLServer官方文档的事务和锁机制内容，结合生产环境实际情况，详细讲解SQLServer事务管理、锁类型、锁兼容性、死锁处理等内容。风哥教程参考SQLServer官方文档Transactions、Locking、Deadlocks等相关章节。
Part01-基础概念与理论知识
1.1 SQLServer事务概念
SQLServer事务是一组逻辑操作单元，具有ACID特性：
- 原子性(Atomicity)：事务中的操作要么全部成功，要么全部失败
- 一致性(Consistency)：事务执行前后数据库状态保持一致
- 隔离性(Isolation)：并发事务之间相互隔离
- 持久性(Durability)：事务提交后结果永久保存
更多视频教程www.fgedu.net.cn
1.2 SQLServer锁类型
SQLServer支持多种锁类型：
- 共享锁(S)：用于读取操作，允许多个事务同时读取
- 排他锁(X)：用于写入操作，阻止其他事务访问
- 更新锁(U)：用于更新操作的准备阶段
- 意向锁(I)：表示在更低粒度级别获取锁的意图
- 架构锁(Sch)：用于架构修改操作
- 大容量更新锁(BU)：用于大容量数据复制
学习交流加群风哥微信: itpux-com
1.3 SQLServer锁兼容性
锁兼容性矩阵：
S锁 兼容 不兼容 兼容 兼容 不兼容
X锁 不兼容 不兼容 不兼容 不兼容 不兼容
U锁 兼容 不兼容 不兼容 兼容 不兼容
IS锁 兼容 不兼容 兼容 兼容 兼容
IX锁 不兼容 不兼容 不兼容 兼容 兼容
学习交流加群风哥QQ113257174
Part02-生产环境规划与建议
2.1 SQLServer事务设计原则
事务设计原则：
- 保持事务简短，减少锁持有时间
- 避免在事务中进行用户交互
- 合理设置事务隔离级别
- 使用适当的错误处理机制
- 避免长事务导致阻塞
风哥提示：事务设计应考虑性能和一致性的平衡
2.2 SQLServer锁优化策略
锁优化策略：
- 使用适当的索引减少锁粒度
- 避免锁升级（Lock Escalation）
- 使用NOLOCK提示减少阻塞
- 合理设置锁超时时间
- 使用乐观并发控制
更多学习教程公众号风哥教程itpux_com
2.3 SQLServer死锁预防
死锁预防策略：
- 按相同顺序访问对象
- 避免事务中的用户交互
- 保持事务简短
- 使用较低的隔离级别
- 使用绑定连接
from SQLServer视频:www.itpux.com
Part03-生产环境项目实施方案
3.1 SQLServer事务管理
事务管理操作：
USE fgedudb;
GO
CREATE TABLE fgedu.accounts (
account_id INT PRIMARY KEY,
account_name VARCHAR(100),
balance DECIMAL(15,2) DEFAULT 0,
create_time DATETIME DEFAULT GETDATE()
);
GO
— 插入测试数据
INSERT INTO fgedu.accounts (account_id, account_name, balance)
VALUES
(1, ‘张三账户’, 10000.00),
(2, ‘李四账户’, 5000.00),
(3, ‘王五账户’, 8000.00);
GO
— 显式事务示例
BEGIN TRANSACTION;
BEGIN TRY
UPDATE fgedu.accounts SET balance = balance – 1000 WHERE account_id = 1;
UPDATE fgedu.accounts SET balance = balance + 1000 WHERE account_id = 2;
COMMIT TRANSACTION;
PRINT ‘转账成功’;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT ‘转账失败: ‘ + ERROR_MESSAGE();
END CATCH;
GO
执行结果：
(1 row affected)
(1 row affected)
转账成功
3.2 SQLServer锁监控
锁监控操作：
SELECT
request_session_id AS ‘会话ID’,
resource_type AS ‘资源类型’,
resource_database_id AS ‘数据库ID’,
DB_NAME(resource_database_id) AS ‘数据库名’,
resource_associated_entity_id AS ‘资源ID’,
request_mode AS ‘锁模式’,
request_status AS ‘状态’
FROM sys.dm_tran_locks
WHERE resource_type = ‘OBJECT’;
GO
— 查看阻塞信息
SELECT
t1.resource_type AS ‘资源类型’,
DB_NAME(t1.resource_database_id) AS ‘数据库名’,
OBJECT_NAME(t1.resource_associated_entity_id) AS ‘对象名’,
t1.request_session_id AS ‘被阻塞会话ID’,
t2.blocking_session_id AS ‘阻塞源会话ID’,
t2.wait_type AS ‘等待类型’,
t2.wait_time/1000 AS ‘等待时间(秒)’,
t2.status AS ‘状态’
FROM sys.dm_tran_locks t1
JOIN sys.dm_exec_requests t2 ON t1.request_session_id = t2.session_id
WHERE t2.blocking_session_id > 0;
GO
— 查看锁超时设置
SHOW LOCK_TIMEOUT;
GO
— 设置锁超时
SET LOCK_TIMEOUT 5000; — 5秒
GO
执行结果：
——- ——— ——— ———- ——————- ——- ——
52 OBJECT 7 fgedudb 123456789012345 IX GRANT
53 OBJECT 7 fgedudb 123456789012345 IS GRANT
资源类型 数据库名 对象名 被阻塞会话ID 阻塞源会话ID 等待类型 等待时间(秒) 状态
——— ———- ————— ———— ———— ——— ———— ——
OBJECT fgedudb fgedu_accounts 54 52 LCK_M_X 3 SUSPENDED
Option Value
——– ———–
lock_timeout 5000
3.3 SQLServer死锁处理
死锁处理操作：
DBCC TRACEON(1222, -1);
GO
— 查看死锁图
SELECT
xed.value(‘@timestamp’, ‘datetime’) AS ‘时间’,
xed.query(‘.’) AS ‘死锁信息’
FROM
(
SELECT CAST([target_data] AS XML) AS TargetData
FROM sys.dm_xe_session_targets AS st
INNER JOIN sys.dm_xe_sessions AS s ON s.address = st.event_session_address
WHERE s.name = ‘system_health’
AND st.target_name = ‘ring_buffer’
) AS Data
CROSS APPLY TargetData.nodes(‘RingBufferTarget/event[@name=”xml_deadlock_report”]/data/value’) AS XEventData(xed);
GO
— 查看死锁统计
SELECT
cntr_value AS ‘死锁次数’
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Number of Deadlocks/sec’
AND instance_name = ‘_Total’;
GO
— 关闭死锁跟踪
DBCC TRACEOFF(1222, -1);
GO
执行结果：
时间 死锁信息
———————– ————————————————
2026-04-08 10:30:15.123 <deadlock-list>…
死锁次数
———–
0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Part04-生产案例与实战讲解
4.1 SQLServer转账事务案例
银行转账事务实战：
CREATE PROCEDURE fgedu.sp_transfer
@from_account INT,
@to_account INT,
@amount DECIMAL(15,2)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @error INT = 0;
DECLARE @balance DECIMAL(15,2);
BEGIN TRANSACTION;
BEGIN TRY
— 检查转出账户余额
SELECT @balance = balance FROM fgedu.accounts WHERE account_id = @from_account;
IF @balance < @amount BEGIN RAISERROR('余额不足', 16, 1); SET @error = 1; END IF @error = 0 BEGIN -- 执行转账 UPDATE fgedu.accounts SET balance = balance - @amount WHERE account_id = @from_account; UPDATE fgedu.accounts SET balance = balance + @amount WHERE account_id = @to_account; -- 记录转账日志 INSERT INTO fgedu.transfer_log (from_account, to_account, amount, transfer_time) VALUES (@from_account, @to_account, @amount, GETDATE()); COMMIT TRANSACTION; PRINT '转账成功，金额: ' + CAST(@amount AS VARCHAR(20)); END END TRY BEGIN CATCH ROLLBACK TRANSACTION; PRINT '转账失败: ' + ERROR_MESSAGE(); END CATCH; END; GO -- 执行转账 EXEC fgedu.sp_transfer @from_account = 1, @to_account = 2, @amount = 500.00; GO -- 查询结果 SELECT * FROM fgedu.accounts; GO
执行结果：
转账成功，金额: 500.00
account_id account_name balance create_time
———– ————- ———– ———————–
1 张三账户 9500.00 2026-04-08 10:00:00.000
2 李四账户 5500.00 2026-04-08 10:00:00.000
3 王五账户 8000.00 2026-04-08 10:00:00.000
4.2 SQLServer锁等待案例
锁等待问题分析：
BEGIN TRANSACTION;
UPDATE fgedu.accounts SET balance = balance + 100 WHERE account_id = 1;
— 不提交，模拟长时间持有锁
GO
— 会话2：查询被阻塞
SET LOCK_TIMEOUT 10000; — 10秒超时
SELECT * FROM fgedu.accounts WHERE account_id = 1;
GO
— 查看阻塞情况
SELECT
blocking_session_id AS ‘阻塞源’,
session_id AS ‘被阻塞会话’,
wait_type AS ‘等待类型’,
wait_time/1000 AS ‘等待秒数’,
status AS ‘状态’
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
GO
— 终止阻塞源会话
— KILL 52;
GO
执行结果：
Msg 1222, Level 16, State 51, Line 3
Lock request time out period exceeded.
阻塞源 被阻塞会话 等待类型 等待秒数 状态
——- ———– ——— ——— ——-
52 53 LCK_M_S 5 SUSPENDED
4.3 SQLServer死锁分析案例
死锁模拟与分析：
CREATE TABLE fgedu.deadlock_test_a (
id INT PRIMARY KEY,
value VARCHAR(100)
);
GO
CREATE TABLE fgedu.deadlock_test_b (
id INT PRIMARY KEY,
value VARCHAR(100)
);
GO
INSERT INTO fgedu.deadlock_test_a VALUES (1, ‘A1’), (2, ‘A2’);
INSERT INTO fgedu.deadlock_test_b VALUES (1, ‘B1’), (2, ‘B2’);
GO
— 会话1执行
BEGIN TRANSACTION;
UPDATE fgedu.deadlock_test_a SET value = ‘A1_updated’ WHERE id = 1;
WAITFOR DELAY ’00:00:05′;
UPDATE fgedu.deadlock_test_b SET value = ‘B1_updated’ WHERE id = 1;
COMMIT TRANSACTION;
GO
— 会话2执行（同时进行）
BEGIN TRANSACTION;
UPDATE fgedu.deadlock_test_b SET value = ‘B2_updated’ WHERE id = 1;
WAITFOR DELAY ’00:00:05′;
UPDATE fgedu.deadlock_test_a SET value = ‘A2_updated’ WHERE id = 1;
COMMIT TRANSACTION;
GO
执行结果：
(2 rows affected)
(1 row affected)
Msg 1205, Level 13, State 51, Line 8
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Part05-风哥经验总结与分享
5.1 SQLServer事务最佳实践
- 保持事务简短，减少锁持有时间
- 使用适当的隔离级别
- 正确处理事务错误
- 避免在事务中进行用户交互
- 使用保存点实现部分回滚
5.2 SQLServer锁优化建议
- 使用索引减少锁粒度
- 避免锁升级
- 合理使用锁提示
- 设置适当的锁超时
- 监控锁等待和阻塞
5.3 SQLServer并发控制技巧
- 使用乐观并发控制减少锁争用
- 使用快照隔离级别提高并发
- 按相同顺序访问资源避免死锁
- 使用READ COMMITTED SNAPSHOT隔离级别
- 定期检查和优化事务性能
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
