金肝Code

 

如果對 transaction isolation 不太清楚可以先看 https://openhome.cc/Gossip/HibernateGossip/IsolationLevel.html

各種 Isolation Level 能解決的問題

	Dirty Read	Unrepeatable Read	Phantom Read
Read uncommitted	O	O	O
Read committed	X	O	O
Repreatable read	X	X	O
Serializable	X	X	X

保證不會    DirtyRead    =>    讀到別的Transaction還沒Commit的資料

保證不會    Unrepeatable Read    =>    讀兩次有可能讀到不一樣的資料

Repeatable Read  => 換句話說同一個Transaction內任何時候讀到的資料都是一樣的

FirstTransaction 會分別讀取兩次一樣範圍的資料，中間會見隔10秒讓 SecondTransaction 去改變資料

        private static void Main(string[] args)
        {
            FirstTransaction();

            //確保 FirstTransaction 跑完
            Task.Delay(new TimeSpan(0, 0, 2)).Wait();

            SecondTransaction();

            Console.ReadKey();
        }

        private static async Task FirstTransaction()
        {
            using (var ts = new TransactionScope(
                TransactionScopeOption.Required,
                new TransactionOptions()
                // 設定 level
                { IsolationLevel = IsolationLevel.RepeatableRead },
                TransactionScopeAsyncFlowOption.Enabled))
            {
                using (var db = new StarGateDBEntities())
                {
                    var list = await db.TableA.ToListAsync();

                    Console.WriteLine("-------First Read-------");
                    foreach (var item in list)
                    {
                        Console.WriteLine($"{ item.B }");
                    }
                    Console.WriteLine("-------First Read-------");

                    Console.WriteLine("Transaction 1 wait 10 seconds");

                    // 10 秒後再 讀一次
                    await Task.Delay(new TimeSpan(0, 0, 10));

                    list = await db.TableA.ToListAsync();

                    Console.WriteLine("-------Second Read-------");
                    foreach (var item in list)
                    {
                        Console.WriteLine($"{ item.B }");
                    }
                    Console.WriteLine("-------Second Read-------");
                }
            }
        }

        private static async Task SecondTransaction()
        {
            using (var ts = new TransactionScope(
                TransactionScopeOption.Required,
                new TransactionOptions()
                // 設定 level
                { IsolationLevel = IsolationLevel.ReadCommitted },
                TransactionScopeAsyncFlowOption.Enabled))
            {
                using (var db = new StarGateDBEntities())
                {
                    Console.WriteLine("Transaction 2 querying");

                    var list = await db.TableA.ToListAsync();

                    Console.WriteLine("Transaction 2 queried");

                    foreach (var item in list)
                    {
                        item.B = "new value";
                    }

                    await db.SaveChangesAsync();

                    ts.Complete();

                    Console.WriteLine("Transaction 2 saved!");
                }
            }
        }

 

FirstTransaction 讀第一次資料得到 4 筆的 old value，接著等10秒，SecondTransaction 也成功讀取到一樣的資料，但是更新卻被卡住了

10秒過後，FirstTransaction 開始讀第二次資料得到 4 筆的 old value，SecondTransaction 終於可以更新資料了

Repeatable Read  => 可以讀，要更新等我Transaction結束再說