public void Run() { var flag = $"事务B - {Thread.CurrentThread.ManagedThreadId} : "; var optKey = 5; var conn = ConnTools.GetConnection(); dynamic searchInfo; try { if (conn.State != ConnectionState.Open) { conn.Open(); } /** * 2.隔离级别的分类 * (1)未提交读 (READ UNCOMMITTED) * (2)已提交读(READ COMMITTED)(默认值) *(3)可重复读(REPEATABLE READ) * *(4)可序列化(SERIALIZABLE) * *(5)快照(SNAPSHOT) * *(6)已经提交读快照(READ_COMMITTED_SNAPSHOT) */ using (var transaction = conn.BeginTransaction(IsolationLevel.ReadUncommitted))//不同conn 事务互不影响。 { // transaction.IsolationLevel = IsolationLevel.ReadUncommitted;//隔离级别后续不可更改 searchInfo = conn.QueryFirst($"SELECT * FROM bill_info WHERE id = {optKey}"); Console.WriteLine(flag + JsonConvert.SerializeObject(searchInfo)); transaction.Commit(); } } catch (Exception e) { Console.WriteLine($"{flag} error:{e}"); } finally { Console.WriteLine($"{flag} is over"); // conn.Close(); } }
public void Run() { var flag = $"事务A - {Thread.CurrentThread.ManagedThreadId} : "; var optKey = 5; var conn = ConnTools.GetConnection(); dynamic searchInfo; try { if (conn.State != ConnectionState.Open) { conn.Open(); } var transaction = conn.BeginTransaction(); #region 阶段1 //修改信息 请求锁 conn.Execute($"UPDATE bill_info SET money = 10 WHERE id = {optKey}"); #endregion #region step1:未提交读 #region 阶段2 conn.Execute($"UPDATE bill_info SET money = money + 1 WHERE id = {optKey}"); searchInfo = conn.QueryFirst($"SELECT * FROM bill_info WHERE id = {optKey}"); Console.WriteLine(flag + JsonConvert.SerializeObject(searchInfo)); #endregion #region 阶段3 // conn.Execute($"UPDATE bill_info SET money = money + 5 WHERE id = {optKey}"); // // searchInfo = conn.QueryFirst($"SELECT * FROM bill_info WHERE id = {optKey}"); // // Console.WriteLine(flag + JsonConvert.SerializeObject(searchInfo)); #endregion #region 阶段4 // transaction.Commit(); #endregion #endregion } catch (Exception e) { Console.WriteLine($"{flag} error:{e}"); } finally { Console.WriteLine($"{flag} is over"); // conn.Close(); } ThreadPool.QueueUserWorkItem(obj => { //System.InvalidOperationException:“Nested transactions are not supported. -- 不支持嵌套事务 ,,,, using (var transaction = conn.BeginTransaction(IsolationLevel.ReadUncommitted)) //不同conn 事务互不影响。 { searchInfo = conn.QueryFirst($"SELECT * FROM bill_info WHERE id = {optKey}"); Console.WriteLine(flag + JsonConvert.SerializeObject(searchInfo)); transaction.Commit(); } }); }
public void Test() { #region 初级测试 仅使用事务操作 不设计锁操作 结果:以数据库中当前数据为准 修改优先级以时间顺序为依据 var validFlag = new[] { false, false, false, false }; var optKey = 4; #region 事务A update failure ThreadPool.QueueUserWorkItem((state => { var currentThreadId = Thread.CurrentThread.ManagedThreadId; Console.WriteLine($"{currentThreadId} is work [edit failure]"); var conn = ConnTools.GetConnection(); try { if (conn.State != ConnectionState.Open) { conn.Open(); } using (var transaction = conn.BeginTransaction()) { // transaction.IsolationLevel//隔离级别 var billInfos = conn.Query <BillInfo>("SELECT * FROM bill_info"); //拿到最后一个账单 var lastBill = billInfos.OrderByDescending(u => u.Id).FirstOrDefault(); if (lastBill == null) { throw new Exception("no bill"); } lastBill.Money = lastBill.Money + 2019; //金额修改 Console.WriteLine($"{currentThreadId} edit opt"); conn.Execute($@" UPDATE bill_info SET {string.Join(",", typeof(BillInfo).GetProperties().Where(u => !u.Name.Equals(nameof(BaseModel.Id))).Select(u => $"{u.Name}=@{u.Name}"))} WHERE {nameof(BaseModel.Id)} = {lastBill.Id} ", lastBill); var editInfo = conn.QueryFirstOrDefault <BillInfo>($"SELECT * FROM bill_info WHERE {nameof(BaseModel.Id)} = {lastBill.Id}"); Console.WriteLine($"{currentThreadId} editInfo:{editInfo.Money}"); Console.WriteLine($"{currentThreadId} other opt"); //其他操作 Thread.Sleep(5000); var zero = 0; Console.WriteLine(100 / zero); //error transaction.Commit(); } } catch (Exception e) { Console.WriteLine(e); validFlag[0] = true; //work A finish } finally { Console.WriteLine($"{currentThreadId} is over"); conn.Close(); } })); #endregion #region 事务B read ThreadPool.QueueUserWorkItem((state => { var currentThreadId = Thread.CurrentThread.ManagedThreadId; Console.WriteLine($"{currentThreadId} is work [read]"); var conn = ConnTools.GetConnection(); try { if (conn.State != ConnectionState.Open) { conn.Open(); } using (var transaction = conn.BeginTransaction()) { Thread.Sleep(1000);//等待a完成修改 var editInfo = conn.QueryFirstOrDefault <BillInfo>($"SELECT * FROM bill_info WHERE {nameof(BaseModel.Id)} = 4"); Console.WriteLine($"{currentThreadId} read editInfo:{editInfo.Money}");//默认情况下 此处读取的为数据库中的数据 其他待提交事务并不影响此处读取 } } catch (Exception e) { Console.WriteLine(e); validFlag[1] = true; //work A finish } finally { Console.WriteLine($"{currentThreadId} is over"); conn.Close(); } })); #endregion #region 事务C edit success ThreadPool.QueueUserWorkItem((state => { Thread.Sleep(200); var currentThreadId = Thread.CurrentThread.ManagedThreadId; Console.WriteLine($"{currentThreadId} is work [edit success add]"); var conn = ConnTools.GetConnection(); try { if (conn.State != ConnectionState.Open) { conn.Open(); } using (var transaction = conn.BeginTransaction()) { var bill = conn.QueryFirst <BillInfo>($"SELECT * FROM bill_info WHERE {nameof(BaseModel.Id)} = {optKey}"); bill.Money = bill.Money + 201900; //金额修改 Console.WriteLine($"{currentThreadId} edit opt"); conn.Execute($@" UPDATE bill_info SET {string.Join(",", typeof(BillInfo).GetProperties().Where(u => !u.Name.Equals(nameof(BaseModel.Id))).Select(u => $"{u.Name}=@{u.Name}"))} WHERE {nameof(BaseModel.Id)} = {bill.Id} ", bill); var editInfo = conn.QueryFirstOrDefault <BillInfo>($"SELECT * FROM bill_info WHERE {nameof(BaseModel.Id)} = {bill.Id}"); Console.WriteLine($"{currentThreadId} editInfo:{editInfo.Money}"); Console.WriteLine($"{currentThreadId} other opt"); //其他操作 // Thread.Sleep(3000); //no error transaction.Commit(); } } catch (Exception e) { Console.WriteLine(e); validFlag[2] = true; //work A finish } finally { Console.WriteLine($"{currentThreadId} is over"); conn.Close(); } })); #endregion #region 事务D edit success ThreadPool.QueueUserWorkItem((state => { Thread.Sleep(201); var currentThreadId = Thread.CurrentThread.ManagedThreadId; Console.WriteLine($"{currentThreadId} is work [edit success reduce]"); var conn = ConnTools.GetConnection(); try { if (conn.State != ConnectionState.Open) { conn.Open(); } using (var transaction = conn.BeginTransaction()) { var bill = conn.QueryFirst <BillInfo>($"SELECT * FROM bill_info WHERE {nameof(BaseModel.Id)} = {optKey}"); bill.Money = bill.Money - 201900; //金额修改 Console.WriteLine($"{currentThreadId} edit opt"); conn.Execute($@" UPDATE bill_info SET {string.Join(",", typeof(BillInfo).GetProperties().Where(u => !u.Name.Equals(nameof(BaseModel.Id))).Select(u => $"{u.Name}=@{u.Name}"))} WHERE {nameof(BaseModel.Id)} = {bill.Id} ", bill); var editInfo = conn.QueryFirstOrDefault <BillInfo>($"SELECT * FROM bill_info WHERE {nameof(BaseModel.Id)} = {bill.Id}"); Console.WriteLine($"{currentThreadId} editInfo:{editInfo.Money}"); Console.WriteLine($"{currentThreadId} other opt"); //其他操作 // Thread.Sleep(3000); //no error transaction.Commit(); } } catch (Exception e) { Console.WriteLine(e); validFlag[3] = true; //work A finish } finally { Console.WriteLine($"{currentThreadId} is over"); conn.Close(); } })); #endregion Console.WriteLine("running"); while (validFlag.Select(u => !u).Any()) { } Console.WriteLine("work finish"); #endregion }