znlgis 博客

GIS开发与技术分享

第八章：事务处理

目录

8.1 事务基础

8.1.1 什么是事务

事务是数据库操作的逻辑单元,包含一组数据库操作,这些操作要么全部成功,要么全部失败:

public class TransactionBasics
{
    private readonly SqlSugarClient _db;

    public TransactionBasics(SqlSugarClient db)
    {
        _db = db;
    }

    // 经典转账示例
    public bool Transfer(int fromUserId, int toUserId, decimal amount)
    {
        try
        {
            // 开启事务
            _db.Ado.BeginTran();

            // 扣除转出方余额
            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance - amount)
                .Where(a => a.UserId == fromUserId)
                .ExecuteCommand();

            // 增加转入方余额
            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance + amount)
                .Where(a => a.UserId == toUserId)
                .ExecuteCommand();

            // 提交事务
            _db.Ado.CommitTran();
            return true;
        }
        catch (Exception ex)
        {
            // 回滚事务
            _db.Ado.RollbackTran();
            Console.WriteLine($"转账失败: {ex.Message}");
            return false;
        }
    }
}

public class Account
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public int UserId { get; set; }
    public decimal Balance { get; set; }
}

8.1.2 ACID特性

事务必须满足ACID四个特性:

public class AcidProperties
{
    private readonly SqlSugarClient _db;

    public AcidProperties(SqlSugarClient db)
    {
        _db = db;
    }

    // 原子性(Atomicity): 事务中的操作要么全部成功,要么全部失败
    public bool AtomicityExample()
    {
        try
        {
            _db.Ado.BeginTran();

            // 操作1: 插入订单
            var order = new Order
            {
                OrderNo = "ORD001",
                UserId = 1,
                Amount = 1000
            };
            var orderId = _db.Insertable(order).ExecuteReturnIdentity();

            // 操作2: 插入订单详情
            var detail = new OrderDetail
            {
                OrderId = orderId,
                ProductId = 1,
                Quantity = 2,
                Price = 500
            };
            _db.Insertable(detail).ExecuteCommand();

            // 操作3: 更新库存
            _db.Updateable<Product>()
                .SetColumns(p => p.Stock == p.Stock - 2)
                .Where(p => p.Id == 1)
                .ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            // 如果任何操作失败,所有操作都会回滚
            _db.Ado.RollbackTran();
            return false;
        }
    }

    // 一致性(Consistency): 事务执行前后,数据库从一个一致性状态转到另一个一致性状态
    public bool ConsistencyExample()
    {
        try
        {
            _db.Ado.BeginTran();

            // 确保转账前后总金额不变
            var totalBefore = _db.Queryable<Account>()
                .Sum(a => a.Balance);

            // 执行转账
            Transfer(1, 2, 100);

            var totalAfter = _db.Queryable<Account>()
                .Sum(a => a.Balance);

            // 验证总金额一致
            if (totalBefore != totalAfter)
            {
                throw new Exception("数据不一致");
            }

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }

    private void Transfer(int from, int to, decimal amount)
    {
        _db.Updateable<Account>()
            .SetColumns(a => a.Balance == a.Balance - amount)
            .Where(a => a.UserId == from)
            .ExecuteCommand();

        _db.Updateable<Account>()
            .SetColumns(a => a.Balance == a.Balance + amount)
            .Where(a => a.UserId == to)
            .ExecuteCommand();
    }

    // 隔离性(Isolation): 一个事务的执行不能被其他事务干扰
    // 持久性(Durability): 事务一旦提交,对数据库的改变是永久性的
}

8.1.3 事务的重要性

public class TransactionImportance
{
    private readonly SqlSugarClient _db;

    public TransactionImportance(SqlSugarClient db)
    {
        _db = db;
    }

    // 没有事务的风险示例
    public bool RiskyOperationWithoutTransaction(Order order)
    {
        try
        {
            // 插入订单
            var orderId = _db.Insertable(order).ExecuteReturnIdentity();

            // 如果这里发生异常,订单已插入,但库存未更新
            // throw new Exception("模拟异常");

            // 更新库存
            _db.Updateable<Product>()
                .SetColumns(p => p.Stock == p.Stock - order.Quantity)
                .Where(p => p.Id == order.ProductId)
                .ExecuteCommand();

            return true;
        }
        catch
        {
            // 数据已经不一致了
            return false;
        }
    }

    // 使用事务保证数据一致性
    public bool SafeOperationWithTransaction(Order order)
    {
        try
        {
            _db.Ado.BeginTran();

            // 插入订单
            var orderId = _db.Insertable(order).ExecuteReturnIdentity();

            // 更新库存
            _db.Updateable<Product>()
                .SetColumns(p => p.Stock == p.Stock - order.Quantity)
                .Where(p => p.Id == order.ProductId)
                .ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            // 所有操作都会回滚,保证数据一致性
            _db.Ado.RollbackTran();
            return false;
        }
    }
}

8.2 基础事务操作

8.2.1 BeginTran开启事务

public class BeginTransactionExamples
{
    private readonly SqlSugarClient _db;

    public BeginTransactionExamples(SqlSugarClient db)
    {
        _db = db;
    }

    // 基础事务开启
    public void BasicBeginTran()
    {
        _db.Ado.BeginTran();
        
        try
        {
            // 执行数据库操作
            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();
            
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    // 指定隔离级别开启事务
    public void BeginTranWithIsolationLevel()
    {
        _db.Ado.BeginTran(IsolationLevel.ReadCommitted);
        
        try
        {
            // 执行数据库操作
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }
}

8.2.2 CommitTran提交事务

public class CommitTransactionExamples
{
    private readonly SqlSugarClient _db;

    public CommitTransactionExamples(SqlSugarClient db)
    {
        _db = db;
    }

    // 提交事务
    public bool CommitExample()
    {
        try
        {
            _db.Ado.BeginTran();

            // 执行多个操作
            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();
            _db.Insertable(new User { Name = "李四" }).ExecuteCommand();

            // 提交事务,使所有更改永久生效
            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }

    // 条件提交
    public bool ConditionalCommit(List<User> users)
    {
        try
        {
            _db.Ado.BeginTran();

            foreach (var user in users)
            {
                _db.Insertable(user).ExecuteCommand();
            }

            // 检查业务规则
            var count = _db.Queryable<User>().Count();
            if (count > 1000)
            {
                // 不满足业务规则,回滚
                _db.Ado.RollbackTran();
                return false;
            }

            // 满足业务规则,提交
            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }
}

8.2.3 RollbackTran回滚事务

public class RollbackTransactionExamples
{
    private readonly SqlSugarClient _db;

    public RollbackTransactionExamples(SqlSugarClient db)
    {
        _db = db;
    }

    // 基础回滚
    public void BasicRollback()
    {
        _db.Ado.BeginTran();
        
        try
        {
            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();
            
            // 模拟错误
            throw new Exception("发生错误");
            
            _db.Ado.CommitTran();
        }
        catch
        {
            // 回滚所有更改
            _db.Ado.RollbackTran();
            Console.WriteLine("事务已回滚");
        }
    }

    // 部分回滚(使用保存点)
    public void PartialRollback()
    {
        _db.Ado.BeginTran();
        
        try
        {
            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();
            
            // 创建保存点
            _db.Ado.ExecuteCommand("SAVEPOINT sp1");
            
            _db.Insertable(new User { Name = "李四" }).ExecuteCommand();
            
            // 回滚到保存点
            _db.Ado.ExecuteCommand("ROLLBACK TO SAVEPOINT sp1");
            
            // 只有张三被插入,李四被回滚
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
        }
    }

    // 业务规则验证回滚
    public bool ValidateAndRollback(Order order)
    {
        _db.Ado.BeginTran();
        
        try
        {
            // 插入订单
            var orderId = _db.Insertable(order).ExecuteReturnIdentity();
            
            // 验证业务规则
            var product = _db.Queryable<Product>()
                .First(p => p.Id == order.ProductId);
            
            if (product.Stock < order.Quantity)
            {
                // 库存不足,回滚
                _db.Ado.RollbackTran();
                return false;
            }
            
            // 更新库存
            _db.Updateable<Product>()
                .SetColumns(p => p.Stock == p.Stock - order.Quantity)
                .Where(p => p.Id == order.ProductId)
                .ExecuteCommand();
            
            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }
}

8.3 事务模式

8.3.1 自动事务模式

public class AutoTransactionMode
{
    private readonly SqlSugarClient _db;

    public AutoTransactionMode(SqlSugarClient db)
    {
        _db = db;
    }

    // 使用UseTran自动管理事务
    public bool UseAutoTransaction()
    {
        var result = _db.Ado.UseTran(() =>
        {
            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();
            _db.Insertable(new User { Name = "李四" }).ExecuteCommand();
        });

        return result.IsSuccess;
    }

    // 带返回值的自动事务
    public int UseAutoTransactionWithReturn()
    {
        var result = _db.Ado.UseTran(() =>
        {
            var user = new User { Name = "王五" };
            return _db.Insertable(user).ExecuteReturnIdentity();
        });

        return result.IsSuccess ? result.Data : 0;
    }

    // 异常处理
    public void AutoTransactionWithError()
    {
        var result = _db.Ado.UseTran(() =>
        {
            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();
            
            // 发生异常会自动回滚
            throw new Exception("模拟异常");
            
            _db.Insertable(new User { Name = "李四" }).ExecuteCommand();
        });

        if (!result.IsSuccess)
        {
            Console.WriteLine($"事务失败: {result.ErrorMessage}");
        }
    }
}

8.3.2 手动事务模式

public class ManualTransactionMode
{
    private readonly SqlSugarClient _db;

    public ManualTransactionMode(SqlSugarClient db)
    {
        _db = db;
    }

    // 手动控制事务
    public bool ManualTransaction()
    {
        try
        {
            _db.Ado.BeginTran();

            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();
            _db.Insertable(new User { Name = "李四" }).ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch (Exception ex)
        {
            _db.Ado.RollbackTran();
            Console.WriteLine($"事务失败: {ex.Message}");
            return false;
        }
    }

    // 复杂业务逻辑的手动事务
    public bool ComplexManualTransaction(Order order, List<OrderDetail> details)
    {
        try
        {
            _db.Ado.BeginTran();

            // 1. 插入订单
            var orderId = _db.Insertable(order).ExecuteReturnIdentity();

            // 2. 插入订单详情
            foreach (var detail in details)
            {
                detail.OrderId = orderId;
                _db.Insertable(detail).ExecuteCommand();

                // 3. 更新库存
                var updated = _db.Updateable<Product>()
                    .SetColumns(p => p.Stock == p.Stock - detail.Quantity)
                    .Where(p => p.Id == detail.ProductId)
                    .Where(p => p.Stock >= detail.Quantity)
                    .ExecuteCommand();

                if (updated == 0)
                {
                    throw new Exception($"产品{detail.ProductId}库存不足");
                }
            }

            // 4. 创建交易记录
            var transaction = new Transaction
            {
                OrderId = orderId,
                Amount = order.Amount,
                Status = "Completed"
            };
            _db.Insertable(transaction).ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch (Exception ex)
        {
            _db.Ado.RollbackTran();
            Console.WriteLine($"交易失败: {ex.Message}");
            return false;
        }
    }
}

8.3.3 异步事务

public class AsyncTransactionMode
{
    private readonly SqlSugarClient _db;

    public AsyncTransactionMode(SqlSugarClient db)
    {
        _db = db;
    }

    // 异步事务
    public async Task<bool> AsyncTransaction()
    {
        try
        {
            _db.Ado.BeginTran();

            await _db.Insertable(new User { Name = "张三" }).ExecuteCommandAsync();
            await _db.Insertable(new User { Name = "李四" }).ExecuteCommandAsync();

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }

    // 异步自动事务
    public async Task<bool> AsyncAutoTransaction()
    {
        var result = await _db.Ado.UseTranAsync(async () =>
        {
            await _db.Insertable(new User { Name = "张三" }).ExecuteCommandAsync();
            await _db.Insertable(new User { Name = "李四" }).ExecuteCommandAsync();
        });

        return result.IsSuccess;
    }

    // 异步复杂事务
    public async Task<bool> ComplexAsyncTransaction(List<User> users)
    {
        try
        {
            _db.Ado.BeginTran();

            foreach (var user in users)
            {
                await _db.Insertable(user).ExecuteCommandAsync();
                
                // 为每个用户创建默认设置
                var profile = new UserProfile
                {
                    UserId = user.Id,
                    Avatar = "default.png"
                };
                await _db.Insertable(profile).ExecuteCommandAsync();
            }

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }
}

8.4 嵌套事务

8.4.1 嵌套事务原理

public class NestedTransactionPrinciple
{
    private readonly SqlSugarClient _db;

    public NestedTransactionPrinciple(SqlSugarClient db)
    {
        _db = db;
    }

    // 嵌套事务示例
    public bool NestedTransaction()
    {
        try
        {
            _db.Ado.BeginTran(); // 外层事务

            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();

            // 调用另一个方法,该方法也使用事务
            InnerTransaction();

            _db.Ado.CommitTran(); // 提交外层事务
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran(); // 回滚外层事务
            return false;
        }
    }

    private void InnerTransaction()
    {
        // 内层事务会加入外层事务
        _db.Ado.BeginTran();
        
        try
        {
            _db.Insertable(new User { Name = "李四" }).ExecuteCommand();
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw; // 抛出异常,让外层事务处理
        }
    }
}

8.4.2 保存点(SavePoint)

public class SavePointExamples
{
    private readonly SqlSugarClient _db;

    public SavePointExamples(SqlSugarClient db)
    {
        _db = db;
    }

    // 使用保存点
    public bool UseSavePoint()
    {
        try
        {
            _db.Ado.BeginTran();

            // 插入用户1
            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();

            // 创建保存点
            _db.Ado.ExecuteCommand("SAVEPOINT sp1");

            try
            {
                // 插入用户2
                _db.Insertable(new User { Name = "李四" }).ExecuteCommand();
                
                // 模拟错误
                throw new Exception("用户2插入失败");
            }
            catch
            {
                // 回滚到保存点,只撤销用户2的插入
                _db.Ado.ExecuteCommand("ROLLBACK TO SAVEPOINT sp1");
            }

            // 插入用户3
            _db.Insertable(new User { Name = "王五" }).ExecuteCommand();

            _db.Ado.CommitTran();
            // 结果: 张三和王五被插入,李四被回滚
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }

    // 多个保存点
    public bool MultipleSavePoints()
    {
        try
        {
            _db.Ado.BeginTran();

            _db.Insertable(new User { Name = "User1" }).ExecuteCommand();
            _db.Ado.ExecuteCommand("SAVEPOINT sp1");

            _db.Insertable(new User { Name = "User2" }).ExecuteCommand();
            _db.Ado.ExecuteCommand("SAVEPOINT sp2");

            _db.Insertable(new User { Name = "User3" }).ExecuteCommand();
            _db.Ado.ExecuteCommand("SAVEPOINT sp3");

            // 回滚到sp2,撤销User3的插入
            _db.Ado.ExecuteCommand("ROLLBACK TO SAVEPOINT sp2");

            _db.Insertable(new User { Name = "User4" }).ExecuteCommand();

            _db.Ado.CommitTran();
            // 结果: User1, User2, User4被插入
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }
}

8.4.3 嵌套事务实践

public class NestedTransactionPractice
{
    private readonly SqlSugarClient _db;

    public NestedTransactionPractice(SqlSugarClient db)
    {
        _db = db;
    }

    // 实际业务场景:批量处理订单
    public BatchResult ProcessOrders(List<Order> orders)
    {
        var result = new BatchResult();
        
        try
        {
            _db.Ado.BeginTran();

            foreach (var order in orders)
            {
                try
                {
                    // 为每个订单创建保存点
                    var savepoint = $"sp_{order.OrderNo}";
                    _db.Ado.ExecuteCommand($"SAVEPOINT {savepoint}");

                    // 处理单个订单
                    ProcessSingleOrder(order);
                    
                    result.SuccessCount++;
                }
                catch (Exception ex)
                {
                    // 回滚单个订单,继续处理其他订单
                    var savepoint = $"sp_{order.OrderNo}";
                    _db.Ado.ExecuteCommand($"ROLLBACK TO SAVEPOINT {savepoint}");
                    
                    result.FailedOrders.Add(order.OrderNo);
                    result.Errors.Add(ex.Message);
                }
            }

            _db.Ado.CommitTran();
            return result;
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    private void ProcessSingleOrder(Order order)
    {
        // 插入订单
        var orderId = _db.Insertable(order).ExecuteReturnIdentity();

        // 更新库存
        _db.Updateable<Product>()
            .SetColumns(p => p.Stock == p.Stock - order.Quantity)
            .Where(p => p.Id == order.ProductId)
            .ExecuteCommand();
    }
}

public class BatchResult
{
    public int SuccessCount { get; set; }
    public List<string> FailedOrders { get; set; } = new List<string>();
    public List<string> Errors { get; set; } = new List<string>();
}

8.5 分布式事务

8.5.1 分布式事务概念

public class DistributedTransactionConcept
{
    // 分布式事务涉及多个数据库或服务
    // SqlSugar通过多数据库连接支持分布式事务

    public class DistributedTransactionExample
    {
        private readonly SqlSugarScope _db;

        public DistributedTransactionExample(SqlSugarScope db)
        {
            _db = db;
        }

        // 跨数据库事务
        public bool CrossDatabaseTransaction()
        {
            var db1 = _db.GetConnection("db1");
            var db2 = _db.GetConnection("db2");

            try
            {
                // 开启分布式事务
                db1.Ado.BeginTran();
                db2.Ado.BeginTran();

                // 在数据库1中操作
                db1.Insertable(new User { Name = "张三" }).ExecuteCommand();

                // 在数据库2中操作
                db2.Insertable(new Order { OrderNo = "ORD001" }).ExecuteCommand();

                // 提交两个数据库的事务
                db1.Ado.CommitTran();
                db2.Ado.CommitTran();

                return true;
            }
            catch
            {
                // 回滚两个数据库的事务
                db1.Ado.RollbackTran();
                db2.Ado.RollbackTran();
                return false;
            }
        }
    }
}

8.5.2 跨数据库事务

public class CrossDatabaseTransaction
{
    private readonly SqlSugarScope _db;

    public CrossDatabaseTransaction(SqlSugarScope db)
    {
        _db = db;
    }

    // 主从数据库事务
    public bool MasterSlaveTransaction()
    {
        var master = _db.GetConnection("master");
        var slave = _db.GetConnection("slave");

        try
        {
            master.Ado.BeginTran();

            // 在主库写入
            var user = new User { Name = "张三" };
            var userId = master.Insertable(user).ExecuteReturnIdentity();

            // 同步到从库
            user.Id = userId;
            slave.Insertable(user).ExecuteCommand();

            master.Ado.CommitTran();
            return true;
        }
        catch
        {
            master.Ado.RollbackTran();
            return false;
        }
    }

    // 多数据库协调事务
    public bool MultiDatabaseTransaction()
    {
        var dbOrder = _db.GetConnection("order_db");
        var dbInventory = _db.GetConnection("inventory_db");
        var dbPayment = _db.GetConnection("payment_db");

        try
        {
            // 开启所有数据库事务
            dbOrder.Ado.BeginTran();
            dbInventory.Ado.BeginTran();
            dbPayment.Ado.BeginTran();

            // 1. 订单库:创建订单
            var order = new Order { OrderNo = "ORD001", Amount = 1000 };
            var orderId = dbOrder.Insertable(order).ExecuteReturnIdentity();

            // 2. 库存库:扣减库存
            var updated = dbInventory.Updateable<Product>()
                .SetColumns(p => p.Stock == p.Stock - 1)
                .Where(p => p.Id == 1)
                .ExecuteCommand();

            if (updated == 0)
            {
                throw new Exception("库存不足");
            }

            // 3. 支付库:创建支付记录
            var payment = new Payment
            {
                OrderId = orderId,
                Amount = order.Amount,
                Status = "Paid"
            };
            dbPayment.Insertable(payment).ExecuteCommand();

            // 提交所有事务
            dbOrder.Ado.CommitTran();
            dbInventory.Ado.CommitTran();
            dbPayment.Ado.CommitTran();

            return true;
        }
        catch (Exception ex)
        {
            // 回滚所有事务
            dbOrder.Ado.RollbackTran();
            dbInventory.Ado.RollbackTran();
            dbPayment.Ado.RollbackTran();

            Console.WriteLine($"分布式事务失败: {ex.Message}");
            return false;
        }
    }
}

public class Payment
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public int OrderId { get; set; }
    public decimal Amount { get; set; }
    public string Status { get; set; }
}

8.5.3 两阶段提交

public class TwoPhaseCommit
{
    private readonly SqlSugarScope _db;

    public TwoPhaseCommit(SqlSugarScope db)
    {
        _db = db;
    }

    // 两阶段提交实现
    public bool TwoPhaseCommitTransaction()
    {
        var databases = new List<SqlSugarClient>
        {
            _db.GetConnection("db1"),
            _db.GetConnection("db2"),
            _db.GetConnection("db3")
        };

        // 第一阶段:准备(Prepare)
        var preparedDatabases = new List<SqlSugarClient>();
        
        try
        {
            foreach (var database in databases)
            {
                database.Ado.BeginTran();
                preparedDatabases.Add(database);
            }

            // 执行业务逻辑
            databases[0].Insertable(new User { Name = "张三" }).ExecuteCommand();
            databases[1].Insertable(new Order { OrderNo = "ORD001" }).ExecuteCommand();
            databases[2].Insertable(new Payment { Amount = 1000 }).ExecuteCommand();

            // 第二阶段:提交(Commit)
            foreach (var database in preparedDatabases)
            {
                database.Ado.CommitTran();
            }

            return true;
        }
        catch
        {
            // 回滚所有已准备的数据库
            foreach (var database in preparedDatabases)
            {
                database.Ado.RollbackTran();
            }

            return false;
        }
    }
}

8.6 事务隔离级别

8.6.1 四种隔离级别

public class IsolationLevels
{
    private readonly SqlSugarClient _db;

    public IsolationLevels(SqlSugarClient db)
    {
        _db = db;
    }

    // Read Uncommitted (读未提交)
    // 最低隔离级别,可能出现脏读、不可重复读、幻读
    public void ReadUncommitted()
    {
        _db.Ado.BeginTran(IsolationLevel.ReadUncommitted);
        
        try
        {
            // 可以读取未提交的数据
            var users = _db.Queryable<User>().ToList();
            
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
        }
    }

    // Read Committed (读已提交)
    // 避免脏读,但可能出现不可重复读、幻读
    public void ReadCommitted()
    {
        _db.Ado.BeginTran(IsolationLevel.ReadCommitted);
        
        try
        {
            // 只能读取已提交的数据
            var users = _db.Queryable<User>().ToList();
            
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
        }
    }

    // Repeatable Read (可重复读)
    // 避免脏读和不可重复读,但可能出现幻读
    public void RepeatableRead()
    {
        _db.Ado.BeginTran(IsolationLevel.RepeatableRead);
        
        try
        {
            // 同一事务中多次读取相同数据结果一致
            var users1 = _db.Queryable<User>().ToList();
            // ... 其他操作
            var users2 = _db.Queryable<User>().ToList();
            // users1 和 users2 相同
            
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
        }
    }

    // Serializable (可串行化)
    // 最高隔离级别,完全隔离,避免所有并发问题
    public void Serializable()
    {
        _db.Ado.BeginTran(IsolationLevel.Serializable);
        
        try
        {
            // 完全串行化执行,性能最低但最安全
            var users = _db.Queryable<User>().ToList();
            
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
        }
    }
}

8.6.2 设置隔离级别

public class SetIsolationLevel
{
    private readonly SqlSugarClient _db;

    public SetIsolationLevel(SqlSugarClient db)
    {
        _db = db;
    }

    // 在BeginTran时设置
    public void SetAtBeginTran()
    {
        _db.Ado.BeginTran(IsolationLevel.ReadCommitted);
        
        try
        {
            // 执行操作
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
        }
    }

    // 使用SQL设置
    public void SetUsingSql()
    {
        _db.Ado.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
        _db.Ado.BeginTran();
        
        try
        {
            // 执行操作
            _db.Ado.CommitTran();
        }
        catch
        {
            _db.Ado.RollbackTran();
        }
    }
}

8.6.3 隔离级别选择

public class IsolationLevelSelection
{
    private readonly SqlSugarClient _db;

    public IsolationLevelSelection(SqlSugarClient db)
    {
        _db = db;
    }

    // 金融交易:使用Serializable确保数据准确性
    public bool FinancialTransaction(decimal amount)
    {
        _db.Ado.BeginTran(IsolationLevel.Serializable);
        
        try
        {
            // 转账操作需要最高级别保护
            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance - amount)
                .Where(a => a.Id == 1)
                .ExecuteCommand();

            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance + amount)
                .Where(a => a.Id == 2)
                .ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }

    // 一般查询:使用ReadCommitted平衡性能和一致性
    public List<User> NormalQuery()
    {
        _db.Ado.BeginTran(IsolationLevel.ReadCommitted);
        
        try
        {
            var users = _db.Queryable<User>().ToList();
            _db.Ado.CommitTran();
            return users;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return new List<User>();
        }
    }

    // 报表统计:使用RepeatableRead确保数据一致性
    public object GenerateReport()
    {
        _db.Ado.BeginTran(IsolationLevel.RepeatableRead);
        
        try
        {
            var totalUsers = _db.Queryable<User>().Count();
            var totalOrders = _db.Queryable<Order>().Count();
            var totalAmount = _db.Queryable<Order>().Sum(o => o.Amount);

            _db.Ado.CommitTran();

            return new
            {
                TotalUsers = totalUsers,
                TotalOrders = totalOrders,
                TotalAmount = totalAmount
            };
        }
        catch
        {
            _db.Ado.RollbackTran();
            return null;
        }
    }
}

8.7 死锁处理

8.7.1 死锁产生原因

public class DeadlockCauses
{
    // 死锁示例:两个事务互相等待对方释放锁

    // 事务1
    public void Transaction1(SqlSugarClient db)
    {
        db.Ado.BeginTran();
        
        // 锁定资源A
        db.Updateable<Account>()
            .SetColumns(a => a.Balance == a.Balance - 100)
            .Where(a => a.Id == 1)
            .ExecuteCommand();

        Thread.Sleep(1000); // 模拟处理时间

        // 尝试锁定资源B (可能被事务2锁定)
        db.Updateable<Account>()
            .SetColumns(a => a.Balance == a.Balance + 100)
            .Where(a => a.Id == 2)
            .ExecuteCommand();

        db.Ado.CommitTran();
    }

    // 事务2
    public void Transaction2(SqlSugarClient db)
    {
        db.Ado.BeginTran();
        
        // 锁定资源B
        db.Updateable<Account>()
            .SetColumns(a => a.Balance == a.Balance - 100)
            .Where(a => a.Id == 2)
            .ExecuteCommand();

        Thread.Sleep(1000); // 模拟处理时间

        // 尝试锁定资源A (可能被事务1锁定)
        db.Updateable<Account>()
            .SetColumns(a => a.Balance == a.Balance + 100)
            .Where(a => a.Id == 1)
            .ExecuteCommand();

        db.Ado.CommitTran();
    }
}

8.7.2 死锁检测

public class DeadlockDetection
{
    private readonly SqlSugarClient _db;

    public DeadlockDetection(SqlSugarClient db)
    {
        _db = db;
    }

    // 检测并处理死锁
    public bool TransactionWithDeadlockDetection()
    {
        int retryCount = 0;
        int maxRetries = 3;

        while (retryCount < maxRetries)
        {
            try
            {
                _db.Ado.BeginTran();

                // 执行操作
                _db.Updateable<Account>()
                    .SetColumns(a => a.Balance == a.Balance - 100)
                    .Where(a => a.Id == 1)
                    .ExecuteCommand();

                _db.Updateable<Account>()
                    .SetColumns(a => a.Balance == a.Balance + 100)
                    .Where(a => a.Id == 2)
                    .ExecuteCommand();

                _db.Ado.CommitTran();
                return true;
            }
            catch (Exception ex)
            {
                _db.Ado.RollbackTran();

                // 检查是否是死锁错误
                if (IsDeadlockException(ex))
                {
                    retryCount++;
                    Console.WriteLine($"检测到死锁,重试 {retryCount}/{maxRetries}");
                    Thread.Sleep(100 * retryCount); // 递增等待时间
                }
                else
                {
                    throw;
                }
            }
        }

        return false;
    }

    private bool IsDeadlockException(Exception ex)
    {
        // MySQL死锁错误码: 1213
        // SQL Server死锁错误码: 1205
        return ex.Message.Contains("1213") || ex.Message.Contains("1205");
    }
}

8.7.3 死锁避免策略

public class DeadlockAvoidance
{
    private readonly SqlSugarClient _db;

    public DeadlockAvoidance(SqlSugarClient db)
    {
        _db = db;
    }

    // 策略1: 按固定顺序访问资源
    public bool AccessResourcesInOrder(int accountId1, int accountId2)
    {
        // 始终按ID升序访问账户
        int firstId = Math.Min(accountId1, accountId2);
        int secondId = Math.Max(accountId1, accountId2);

        try
        {
            _db.Ado.BeginTran();

            // 先访问ID较小的账户
            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance - 100)
                .Where(a => a.Id == firstId)
                .ExecuteCommand();

            // 再访问ID较大的账户
            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance + 100)
                .Where(a => a.Id == secondId)
                .ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }

    // 策略2: 缩短事务时间
    public bool ShortTransaction()
    {
        // 在事务外完成准备工作
        var account1 = _db.Queryable<Account>().First(a => a.Id == 1);
        var account2 = _db.Queryable<Account>().First(a => a.Id == 2);

        // 验证业务规则
        if (account1.Balance < 100)
        {
            return false;
        }

        try
        {
            _db.Ado.BeginTran();

            // 事务中只执行必要的更新操作
            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance - 100)
                .Where(a => a.Id == 1)
                .ExecuteCommand();

            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance + 100)
                .Where(a => a.Id == 2)
                .ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }

    // 策略3: 使用较低的隔离级别
    public bool UseLowerIsolationLevel()
    {
        try
        {
            _db.Ado.BeginTran(IsolationLevel.ReadCommitted);

            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance - 100)
                .Where(a => a.Id == 1)
                .ExecuteCommand();

            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance + 100)
                .Where(a => a.Id == 2)
                .ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }

    // 策略4: 使用锁超时
    public bool WithLockTimeout()
    {
        try
        {
            // 设置锁超时时间(秒)
            _db.Ado.ExecuteCommand("SET innodb_lock_wait_timeout = 5");

            _db.Ado.BeginTran();

            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance - 100)
                .Where(a => a.Id == 1)
                .ExecuteCommand();

            _db.Updateable<Account>()
                .SetColumns(a => a.Balance == a.Balance + 100)
                .Where(a => a.Id == 2)
                .ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch (Exception ex)
        {
            _db.Ado.RollbackTran();
            Console.WriteLine($"锁超时: {ex.Message}");
            return false;
        }
    }
}

8.8 事务超时

public class TransactionTimeout
{
    private readonly SqlSugarClient _db;

    public TransactionTimeout(SqlSugarClient db)
    {
        _db = db;
    }

    // 设置事务超时
    public bool WithTimeout()
    {
        try
        {
            // 设置命令超时时间(秒)
            _db.Ado.CommandTimeOut = 30;

            _db.Ado.BeginTran();

            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch (Exception ex)
        {
            _db.Ado.RollbackTran();
            Console.WriteLine($"事务超时: {ex.Message}");
            return false;
        }
    }

    // 使用CancellationToken控制超时
    public async Task<bool> WithCancellationToken()
    {
        using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(30));

        try
        {
            _db.Ado.BeginTran();

            await _db.Insertable(new User { Name = "张三" }).ExecuteCommandAsync();

            _db.Ado.CommitTran();
            return true;
        }
        catch (OperationCanceledException)
        {
            _db.Ado.RollbackTran();
            Console.WriteLine("操作超时");
            return false;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }
}

8.9 最佳实践

public class TransactionBestPractices
{
    private readonly SqlSugarClient _db;

    public TransactionBestPractices(SqlSugarClient db)
    {
        _db = db;
    }

    // 1. 保持事务简短
    public bool KeepTransactionShort()
    {
        // 不好的做法
        // _db.Ado.BeginTran();
        // var data = LoadDataFromExternalApi(); // 耗时操作
        // _db.Insertable(data).ExecuteCommand();
        // _db.Ado.CommitTran();

        // 好的做法
        var data = LoadDataFromExternalApi(); // 在事务外完成耗时操作

        try
        {
            _db.Ado.BeginTran();
            _db.Insertable(data).ExecuteCommand(); // 事务中只做必要操作
            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            return false;
        }
    }

    // 2. 正确处理异常
    public bool HandleExceptionsProperly()
    {
        try
        {
            _db.Ado.BeginTran();

            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch (SqlSugarException sqlEx)
        {
            _db.Ado.RollbackTran();
            // 记录数据库异常
            LogDatabaseError(sqlEx);
            return false;
        }
        catch (Exception ex)
        {
            _db.Ado.RollbackTran();
            // 记录其他异常
            LogError(ex);
            throw; // 向上层抛出
        }
    }

    // 3. 使用工作单元模式
    public class UnitOfWork : IDisposable
    {
        private readonly SqlSugarClient _db;
        private bool _disposed = false;

        public UnitOfWork(SqlSugarClient db)
        {
            _db = db;
            _db.Ado.BeginTran();
        }

        public void Commit()
        {
            _db.Ado.CommitTran();
        }

        public void Rollback()
        {
            _db.Ado.RollbackTran();
        }

        public void Dispose()
        {
            if (!_disposed)
            {
                try
                {
                    Rollback(); // 如果没有显式提交,则回滚
                }
                catch { }
                _disposed = true;
            }
        }
    }

    public bool UseUnitOfWork()
    {
        using var uow = new UnitOfWork(_db);
        
        try
        {
            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();
            _db.Insertable(new User { Name = "李四" }).ExecuteCommand();

            uow.Commit();
            return true;
        }
        catch
        {
            // uow.Dispose()会自动回滚
            return false;
        }
    }

    // 4. 避免事务中的用户交互
    public bool AvoidUserInteractionInTransaction()
    {
        // 不好的做法
        // _db.Ado.BeginTran();
        // var confirm = AskUserConfirmation(); // 等待用户输入
        // if(confirm) _db.Ado.CommitTran();

        // 好的做法
        var confirm = AskUserConfirmation(); // 先获取用户确认
        
        if (confirm)
        {
            try
            {
                _db.Ado.BeginTran();
                _db.Insertable(new User { Name = "张三" }).ExecuteCommand();
                _db.Ado.CommitTran();
                return true;
            }
            catch
            {
                _db.Ado.RollbackTran();
                return false;
            }
        }

        return false;
    }

    // 5. 使用事务日志
    public bool WithTransactionLogging()
    {
        var transactionId = Guid.NewGuid();
        
        try
        {
            LogTransactionStart(transactionId);
            
            _db.Ado.BeginTran();

            _db.Insertable(new User { Name = "张三" }).ExecuteCommand();

            _db.Ado.CommitTran();
            
            LogTransactionCommit(transactionId);
            return true;
        }
        catch (Exception ex)
        {
            _db.Ado.RollbackTran();
            LogTransactionRollback(transactionId, ex);
            return false;
        }
    }

    private object LoadDataFromExternalApi() => new { };
    private void LogDatabaseError(Exception ex) { }
    private void LogError(Exception ex) { }
    private bool AskUserConfirmation() => true;
    private void LogTransactionStart(Guid id) { }
    private void LogTransactionCommit(Guid id) { }
    private void LogTransactionRollback(Guid id, Exception ex) { }
}

本章小结

本章全面介绍了SqlSugar的事务处理:

  1. 事务基础: 理解了事务的概念和ACID特性
  2. 基础操作: 掌握了BeginTran、CommitTran、RollbackTran的使用
  3. 事务模式: 学习了自动事务、手动事务和异步事务
  4. 嵌套事务: 了解了嵌套事务和保存点的使用
  5. 分布式事务: 掌握了跨数据库事务和两阶段提交
  6. 隔离级别: 学习了四种隔离级别及其应用场景
  7. 死锁处理: 理解了死锁原因及避免策略
  8. 事务超时: 掌握了事务超时的处理方法
  9. 最佳实践: 总结了事务处理的最佳实践和注意事项

通过本章的学习,你应该能够熟练使用SqlSugar进行事务处理,确保数据的一致性和完整性,并能够处理各种复杂的事务场景。

恭喜你完成了SqlSugar基础教程的学习!

↑