详解SQLServer事务嵌套导致的死锁问题，提供包含事务拆分、锁优化、隔离级别调整在内的完整解决方案

-- 技术栈：SQL Server 2019
BEGIN TRAN  -- @@TRANCOUNT = 1
    UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 100
    
    BEGIN TRAN  -- @@TRANCOUNT = 2
        UPDATE Orders SET Status = 2 WHERE OrderID = 500
    COMMIT     -- @@TRANCOUNT = 1（实际未提交！）

    BEGIN TRAN  -- @@TRANCOUNT = 2
        UPDATE Payments SET Paid = 1 WHERE OrderID = 500
    COMMIT     -- @@TRANCOUNT = 1（仍未提交！）
COMMIT       -- @@TRANCOUNT = 0（真正提交）

BEGIN TRAN
    UPDATE Products...  -- 获得X锁
    UPDATE Orders...    -- 等待会话B的X锁
COMMIT

BEGIN TRAN
    UPDATE Orders...    -- 获得X锁
    UPDATE Products...  -- 等待会话A的X锁 
COMMIT

-- 错误示例：冗长事务
BEGIN TRAN
    EXEC UpdateInventory @ProductID=100  -- 包含业务逻辑
    EXEC ProcessPayment @OrderID=500    -- 包含外部服务调用
    EXEC GenerateInvoice @OrderID=500   -- 包含文件操作
COMMIT

-- 优化方案：拆分事务
BEGIN TRAN
    EXEC UpdateInventory @ProductID=100
COMMIT

BEGIN TRAN
    EXEC ProcessPayment @OrderID=500
COMMIT

-- 文件操作不需要事务
EXEC GenerateInvoice @OrderID=500

CREATE PROCEDURE ProcessOrder 
AS
BEGIN
    DECLARE @Sequence INT = 1
    
    -- 第一阶段：锁定产品
    UPDATE Products WITH (ROWLOCK) SET ... WHERE ...;

    -- 第二阶段：处理订单
    UPDATE Orders WITH (ROWLOCK) SET ... WHERE ...;

    -- 第三阶段：更新支付
    UPDATE Payments WITH (ROWLOCK) SET ... WHERE ...;
END

-- 默认级别可能引发范围锁
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 优化方案：使用快照隔离
ALTER DATABASE MyDB SET ALLOW_SNAPSHOT_ISOLATION ON;

BEGIN TRAN
    SELECT * FROM Products WITH (SNAPSHOT) -- 避免共享锁
    UPDATE ...
COMMIT

// C# 重试示例（关联技术演示）
int retryCount = 0;
while(retryCount < 3)
{
    try 
    {
        using (var conn = new SqlConnection(connStr))
        {
            conn.Open();
            using (var tran = conn.BeginTransaction()) 
            {
                // 业务操作...
                tran.Commit();
                break;
            }
        }
    }
    catch (SqlException ex) when (ex.Number == 1205)
    {
        retryCount++;
        Thread.Sleep(new Random().Next(100, 300));
    }
}

-- 查看当前锁状态
SELECT 
    resource_type,
    request_mode,
    resource_description 
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

-- 高风险实现
BEGIN TRAN
    UPDATE Inventory SET ...  -- 获得X锁
    INSERT OrderDetails...    -- 范围锁
    UPDATE UserPoints...      -- 另一个X锁
COMMIT

-- 安全方案
BEGIN TRAN
    UPDATE Inventory WITH (ROWLOCK) ...
COMMIT

BEGIN TRAN
    INSERT OrderDetails WITH (TABLOCKX) ...
COMMIT

BEGIN TRAN
    UPDATE UserPoints WITH (READPAST) ...
COMMIT

-- 死锁监控脚本
CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'Deadlock_Monitor.xel')