Isolation Level 測試 Serializable

2018-11-15

Isolation Level Serializable

如果對 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

Serializable

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

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

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

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.Serializable },
                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 inserting");

                    db.TableA.Add(new TableA() { A = "A1", B = "add" });

                    await db.SaveChangesAsync();

                    ts.Complete();

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

可以觀察到 SecondTransaction 的 insert 被卡住了