金肝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的資料

作法   =>   如果資料被其他Transaction更新，要等人家Commit完才能讀

請注意兩個 function 都是 async

FirstTransaction 會去更新資料，等10秒後才 commit

SecondTransaction 會去讀 FirstTransaction 還沒 commit 的資料

        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.ReadCommitted },
                TransactionScopeAsyncFlowOption.Enabled))
            {
                using (var db = new StarGateDBEntities())
                {
                    var list = await db.TableA.ToListAsync();

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

                    // 更新
                    await db.SaveChangesAsync();

                    Console.WriteLine("Transaction 1 saved but wait for commit");

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

                    ts.Complete();

                    Console.WriteLine("Transaction 1 commit");
                }
            }
        }

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

你會看到 FirstTransaction 已經更新資料等著 commit，而在這等待的時間 SecondTransaction 也卡在讀資料，因為他再等 FirstTransaction 完成

終於 FirstTransaction commit，馬上 SecondTransaction 也把資料讀回來了，當然這資料是被更新過的