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
}
