溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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，是可以解決讀阻塞問題，但也可能涉及到業務邏輯要改變。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
