SqlSugar利用“事务+乐观锁+version”解决并发下的

1. 数据库建表

CREATE TABLE dbo.Test
(
      tId        INT IDENTITY NOT NULL
    , tName      NVARCHAR (20) NOT NULL
    , tSalary    DECIMAL (8, 2) NULL
    , tTimeStamp TIMESTAMP
    , PRIMARY KEY (tId)
)

2. 创建类

    public partial class Test
    {
        [SugarColumn(IsPrimaryKey =true,IsIdentity =true)]
        public int tId { get; set; }
        public string tName { get; set; }
        public decimal? tSalary { get; set; }
        [SugarColumn(IsOnlyIgnoreInsert = true)]
        public byte[] tTimeStamp { get; set; }
    }

3. 代码示例

static async Task Main(string[] args)
{
    for (int i = 1; i <= 5; i++)
    {
        Task.Factory.StartNew(async (id) =>
        {
            await Test((int)id);
        }, i);
    }
}

static async Task Test(int threadID)
{
    var db = SqlSugar.DB;
    for (int k = 1; k <= 50; k++)
    {
        string log = string.Empty;
        log += $"第{threadID,2}线程,第{k}次";
        //客户端从数据库获取数据
        var firstRead = await db.Queryable<Test>().SingleAsync(x => x.tId == 2);
        log += $"   name:{firstRead.tName,5} version:{BitConverter.ToString(firstRead.tTimeStamp).Replace(" - ", "")}";
        //客户端修改数据需要时间
        Thread.Sleep(10);
        try
        {
            db.Ado.BeginTran();
            log += "    事务开始";
            //提交修改前数据进行验证
            var secondRead = await db.Queryable<Test>().SingleAsync(x => x.tId == 2);
            if (BitConverter.ToString(secondRead.tTimeStamp) != BitConverter.ToString(firstRead.tTimeStamp))
            {
                log += $"    不可重复读,version:{BitConverter.ToString(secondRead.tTimeStamp).Replace(" - ", "")}";
                throw new Exception();
            }
            var data = new Test { tId = 2, tName = $"{threadID}-{k}" };
            var result = await db.Updateable(data).Where(c => c.tTimeStamp == firstRead.tTimeStamp).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommandAsync();
            db.Ado.CommitTran();
            log += result > 0 ? $"    修改成功,当前name:{data.tName}" : "    修改失败,数据被其它线程修改";
        }
        catch (Exception)
        {
            db.Ado.RollbackTran();
            log += "    事务回滚";
        }
        finally
        {
            Console.WriteLine(log);
        };
        Thread.Sleep(10);
    }
}