C#：利用“事务+乐观锁+version”解决并发下的数据一致性问题

本文重点介绍通过事务控制，利用数据库的乐观锁和时间戳，来解决并发（非高并发）环境下的脏读、幻读、不可重复读等问题，同时也能解决超卖等现象，对开发企业管理系统的朋友提供一个思路，为更突出主题思路，文涉及到SqlSugar的一些代码已隐去。

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);
	}
}