SQL Server 2017 AlwaysOn辅助副本数据库的隔离级别

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.