SQL Server 2017 AlwaysOn辅助副本数据库的隔离级别
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，是可以解决读阻塞问题，但也可能涉及到业务逻辑要改变。
