SQL Server 2017 AlwaysOn輔助副本資料庫的隔離級別

cow977發表於2019-08-19
SQLServer資料庫

SQL Server 2017 AlwaysOn 輔助副本資料庫的隔離級別

 

 

一、引子

前幾天，在交流群中有網友貼出圖，說明“ 輔助節點上的庫是READ COMMITTED隔離級別，這意味著輔助節點上執行的查詢(讀操作)和來自主庫的同步(寫操作)，是‘相互阻塞’的。 ”。

也有網友提出瞭解決辦法：“ 做always on之前可以先改成read committed snapshot ”。

這個ALWAYSON輔助節點上的資料庫，snap_isolation_state都等於0，說明都是READ COMMITTED預設事務級別，沒用SNAPSHOT隔離級別

二、猜測

1 、可能還有其他系統控制引數，來決定未提交事務是否阻塞讀操作。

2 、輔助資料庫的所有儲存在本身資料庫中的屬性，都是從主庫帶過來的，不能修改的。

3 、可能是MS判定是輔助資料庫，是Read-Only庫，不會有更新操作，就不阻塞了。

三、驗證

開兩個會話，分別連線主庫和從庫。下表從上至下反映了操作的前後順序，同一行中的操作不分先後。

會話1，連線主庫

會話2，連線從庫

1> select name, snapshot_isolation_state,   snapshot_isolation_state_desc, is_read_committed_snapshot_on from   sys.databases

2> go

name      snapshot   snapshot    is_read

         _isolation _isolation   _committed

         _state   _state_desc  _snapshot_on

------- ---------- -------------   ---------

BRIGHT           0 OFF                    0

1> select name, snapshot_isolation_state,   snapshot_isolation_state_desc, is_read_committed_snapshot_on from sys.databases

2> go

name      snapshot   snapshot    is_read

         _isolation _isolation   _committed

         _state   _state_desc  _snapshot_on

------- ---------- --------- ------------

BRIGHT           0 OFF                  0

1> BEGIN TRANSACTION

2> insert into bright..testtlb(val)   values ('8/18 1122');

3> go

(1 rows affected)

1> select top 2 * from   bright..testtlb order by dt desc;

2> go

ID          dt                      val      

------ -----------------------   ----------

 10607 2019-08-12 14:20:49.710 8/12 1420

 10606 2019-08-12 14:16:44.333 8/12 1416

(2 rows affected)

1> commit

2> go

1> select top 2 * from   bright..testtlb order by dt desc;

2> go

ID          dt                      val      

------ -----------------------   ----------

 10608 2019-08-18 11:23:33.340 8/18 1122

 10607 2019-08-12 14:20:49.710 8/12 1420

(2 rows affected)

1> BEGIN TRANSACTION

2> update bright..testtlb set val =   '8/18 11-22' where id=10608;

3> go

(1 rows affected)

1> select top 2 * from   bright..testtlb order by dt desc;

2> go

ID          dt                      val      

------ -----------------------   ----------

 10608 2019-08-18 11:23:33.340 8/18 1122

 10607 2019-08-12 14:20:49.710 8/12 1420

(2 rows affected)

1> commit

2> go

1> select top 2 * from bright..testtlb   order by dt desc;

2> go

ID          dt                      val      

------ -----------------------   ----------

 10608 2019-08-18 11:23:33.340 8/18 11-22

 10607 2019-08-12 14:20:49.710 8/12 1420

(2 rows affected)

1> alter database bright set read_committed_snapshot   on

2> go

1> select name, snapshot_isolation_state,   snapshot_isolation_state_desc, is_read_committed_snapshot_on from   sys.databases

2> go

name      snapshot   snapshot    is_read

         _isolation _isolation   _committed

         _state   _state_desc  _snapshot_on

------- ---------- -------------   ---------

BRIGHT           0 OFF                  1

1> select name, snapshot_isolation_state,   snapshot_isolation_state_desc, is_read_committed_snapshot_on from   sys.databases

2> go

name      snapshot   snapshot    is_read

         _isolation _isolation   _committed

         _state   _state_desc  _snapshot_on

------- ---------- -------------   ---------

BRIGHT           0 OFF                  1

1> alter database bright set   read_committed_snapshot on

2> go

Msg 1468, Level 16, State 3, Server   server02, Line 1

The operation cannot be performed on   database "BRIGHT" because it is involved in a database mirroring   session or an availability group. Some operations are not allowed on a   database that is participating in a database mirroring session or in an   availability group.

Msg 5069, Level 16, State 1, Server   server02, Line 1

ALTER DATABASE statement failed.

四、結論

1 、輔助資料庫的隔離級別雖然顯示為READ COMMITED，但實際上主庫未提交的事務並不會阻塞輔助庫上的讀；

2 、輔助資料庫不能讀到主庫未提交的資料變更；

3 、輔助庫狀態確認是從主庫同步過來的；

4 、因為輔助庫是Read-Only庫，所以不允許對庫進行修改操作；

五、依據

找到一份關於輔助資料庫上，摘錄如下：

一個可讀的輔助副本可能會同時受到讀操作和寫操作。讀操作來自於直接連線它的客戶端或者透過只讀路由被重定向到它的客戶端。而寫操作只會來自於主資料庫和輔助資料庫之間的資料庫同步。輔助資料庫只有在重做日誌的時候才會發生資料更改。客戶端無法直接在輔助資料庫上執行資料修改操作。

由於存在讀寫同時發生的可能性，在輔助資料庫上可能會發生阻塞問題。為了保障讀操作的穩定執行和效能，AlwaysOn使用行版本控制來消除輔助資料庫上的阻塞問題。對輔助資料庫執行的所有查詢都會被自動執行在快照隔離級別之下。即使你顯式的為查詢設定了其他事務隔離級別，情況也是如此。此外，所有鎖定提示(Lock Hint)都將被忽略。這些都有助於消除了讀寫操作互相爭搶鎖定資料所造成的阻塞問題。

雖然由於快照隔離級別的原因，讀操作不會在資料上佔用共享鎖，但是快照隔離級別會導致讀操作佔用Sch-S鎖。Sch-S鎖還是會阻塞那些在輔助資料庫上重做的DDL語句。因為那些DDL語句需要佔用Sch-M鎖，而Sch-M鎖和Sch-S鎖是互斥的。

除了阻塞，讀操作的Sch-S鎖還可能造成和寫操作之間的死鎖問題。為了保證資料同步的完整性，AlwaysOn規定來自於資料同步(重做日誌)所做的寫操作永遠不會被選為死鎖的犧牲者，無論該寫操作的代價是多小。

五、其他

輔助資料庫上不需要 改用read committed snapshot，或者語句裡面加nolock ，因為已經自動使用行版本控制來消除了輔助資料庫上的阻塞問題。

另外，在主庫上 改用read committed snapshot，或者語句裡面加nolock，是可以解決讀阻塞問題，但也可能涉及到業務邏輯要改變。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/81227/viewspace-2654074/，如需轉載，請註明出處，否則將追究法律責任。

相關文章