SQL Server事务 #

事务ACID特性：
├── 原子性（Atomicity）
│   └── 事务是不可分割的工作单位
├── 一致性（Consistency）
│   └── 事务使数据库从一个一致状态变到另一个一致状态
├── 隔离性（Isolation）
│   └── 多个事务并发执行时互不干扰
└── 持久性（Durability）
    └── 事务一旦提交，对数据的修改是永久的

-- 开始事务
BEGIN TRANSACTION;
BEGIN TRAN;

-- 提交事务
COMMIT TRANSACTION;
COMMIT TRAN;
COMMIT;

-- 回滚事务
ROLLBACK TRANSACTION;
ROLLBACK TRAN;
ROLLBACK;

-- 保存点
SAVE TRANSACTION savepoint_name;

-- 回滚到保存点
ROLLBACK TRANSACTION savepoint_name;

-- 简单事务
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT TRANSACTION;

-- 带错误处理的事务
BEGIN TRY
    BEGIN TRANSACTION;
    
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    
    COMMIT TRANSACTION;
    PRINT 'Transaction completed';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    PRINT 'Transaction failed: ' + ERROR_MESSAGE();
END CATCH

-- 嵌套事务
BEGIN TRANSACTION;  -- @@TRANCOUNT = 1
    BEGIN TRANSACTION;  -- @@TRANCOUNT = 2
        INSERT INTO users (name) VALUES ('Test');
    COMMIT TRANSACTION;  -- @@TRANCOUNT = 1
COMMIT TRANSACTION;  -- @@TRANCOUNT = 0

-- 注意：嵌套事务的COMMIT只减少@@TRANCOUNT
-- 只有最外层的COMMIT才真正提交
-- 任何ROLLBACK都会回滚所有事务

-- 使用保存点
BEGIN TRANSACTION;

INSERT INTO users (name) VALUES ('User1');

SAVE TRANSACTION after_user1;

INSERT INTO users (name) VALUES ('User2');

-- 可以回滚到保存点
ROLLBACK TRANSACTION after_user1;

-- 继续其他操作
INSERT INTO users (name) VALUES ('User3');

COMMIT TRANSACTION;
-- 结果：User1和User3被插入，User2被回滚

SQL Server隔离级别：
├── READ UNCOMMITTED
│   └── 允许脏读
├── READ COMMITTED（默认）
│   └── 避免脏读，可能不可重复读
├── REPEATABLE READ
│   └── 避免脏读和不可重复读，可能幻读
├── SERIALIZABLE
│   └── 完全隔离，避免所有问题
├── SNAPSHOT
│   └── 行版本控制，避免所有问题
└── READ COMMITTED SNAPSHOT
    └── 读操作使用行版本

并发问题：
├── 脏读（Dirty Read）
│   └── 读取未提交的数据
├── 不可重复读（Non-repeatable Read）
│   └── 同一事务中两次读取结果不同
├── 幻读（Phantom Read）
│   └── 同一事务中两次查询返回的行数不同
└── 丢失更新（Lost Update）
    └── 两个事务同时更新，一个覆盖另一个

-- READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
SELECT * FROM users;  -- 可能读取脏数据
COMMIT;

-- READ COMMITTED（默认）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM users;  -- 只读取已提交数据
COMMIT;

-- REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM users WHERE id = 1;  -- 锁定读取的行
-- 其他事务无法修改这些行
SELECT * FROM users WHERE id = 1;  -- 保证相同结果
COMMIT;

-- SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM users WHERE status = 1;  -- 锁定范围
-- 其他事务无法插入status=1的新行
COMMIT;

-- SNAPSHOT
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM users;  -- 读取事务开始时的数据版本
COMMIT;

SQL Server锁类型：
├── 共享锁（S，Shared）
│   └── 用于读操作
├── 排他锁（X，Exclusive）
│   └── 用于写操作
├── 更新锁（U，Update）
│   └── 用于更新操作
├── 意向锁（I，Intent）
│   └── 表示更细粒度的锁
├── 架构锁（Sch）
│   └── 用于DDL操作
└── 大容量更新锁（BU）
    └── 用于批量复制

锁粒度（从大到小）：
├── 数据库锁（Database）
├── 表锁（Table）
├── 页锁（Page）
├── 行锁（Row）
└── 键锁（Key）

-- 查看当前锁
SELECT 
    request_session_id AS 'Session',
    resource_type AS 'Type',
    resource_database_id AS 'DB',
    resource_associated_entity_id AS 'Resource',
    request_mode AS 'Mode',
    request_status AS 'Status'
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

-- 使用sp_lock（已废弃）
EXEC sp_lock;

-- 使用sp_who2查看阻塞
EXEC sp_who2;

-- NOLOCK：不获取锁，允许脏读
SELECT * FROM users WITH (NOLOCK);

-- UPDLOCK：获取更新锁
SELECT * FROM users WITH (UPDLOCK) WHERE id = 1;

-- XLOCK：获取排他锁
SELECT * FROM users WITH (XLOCK) WHERE id = 1;

-- ROWLOCK：使用行锁
UPDATE users WITH (ROWLOCK) SET name = 'Test' WHERE id = 1;

-- TABLOCK：使用表锁
SELECT * FROM users WITH (TABLOCK);

-- HOLDLOCK：持有锁到事务结束
SELECT * FROM users WITH (HOLDLOCK) WHERE id = 1;

-- 会话1
BEGIN TRANSACTION;
UPDATE users SET name = 'Test1' WHERE id = 1;
-- 等待会话2释放id=2的锁
UPDATE users SET name = 'Test1' WHERE id = 2;
COMMIT;

-- 会话2
BEGIN TRANSACTION;
UPDATE users SET name = 'Test2' WHERE id = 2;
-- 等待会话1释放id=1的锁
UPDATE users SET name = 'Test2' WHERE id = 1;
COMMIT;

-- 死锁发生！

-- 启用死锁跟踪
DBCC TRACEON (1222, -1);  -- 输出死锁信息到错误日志
DBCC TRACEON (1204, -1);  -- 输出死锁格式化信息

-- 使用扩展事件
-- SSMS: Management → Extended Events → system_health

-- 查看死锁图
SELECT 
    xed.value('@timestamp', 'datetime') AS CreationTime,
    xed.query('.') AS DeadlockGraph
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 = N'system_health'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]/data/value') AS XEventData(xed);

避免死锁策略：
├── 按相同顺序访问对象
├── 保持事务简短
├── 使用较低的隔离级别
├── 使用适当的锁提示
├── 避免用户交互
└── 设置死锁优先级

-- 设置死锁优先级
SET DEADLOCK_PRIORITY LOW;   -- 低优先级，容易被牺牲
SET DEADLOCK_PRIORITY NORMAL; -- 正常优先级
SET DEADLOCK_PRIORITY HIGH;   -- 高优先级

-- 设置锁超时
SET LOCK_TIMEOUT 5000;  -- 5秒超时

-- 查看阻塞
SELECT 
    t1.resource_type AS 'Resource Type',
    OBJECT_NAME(t1.resource_associated_entity_id) AS 'Object',
    t1.request_session_id AS 'Blocked Session',
    t2.blocking_session_id AS 'Blocking Session',
    t2.wait_type AS 'Wait Type',
    t2.wait_time AS 'Wait Time',
    t2.status AS 'Status'
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;

-- 使用sp_who2
EXEC sp_who2 'active';

-- 使用sp_whoisactive（第三方工具）
-- EXEC sp_whoisactive;

-- 终止阻塞会话
KILL 53;  -- 53是会话ID

-- 设置锁超时
SET LOCK_TIMEOUT 30000;  -- 30秒

-- 使用NOLOCK提示
SELECT * FROM users WITH (NOLOCK);

事务设计原则：
├── 保持事务简短
├── 避免用户交互
├── 使用适当的隔离级别
├── 按相同顺序访问资源
├── 使用错误处理
├── 避免长事务
└── 及时提交或回滚

-- 使用SET XACT_ABORT ON
SET XACT_ABORT ON;  -- 出错时自动回滚

BEGIN TRANSACTION;
-- 操作
COMMIT TRANSACTION;

-- 使用适当的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 使用锁提示
SELECT * FROM users WITH (NOLOCK);