Shulou(Shulou.com)06/01 Report--

Isolation level of SQL Server 2017 AlwaysOn secondary replica database

I. introduction

A few days ago, some netizens in the exchange group posted a picture saying that "the libraries on the secondary node are READ COMMITTED isolation level, which means that the queries (read operations) and the synchronization (write operations) from the primary database on the secondary node are 'blocking' each other."

Some netizens also put forward a solution: "you can change it to read committed snapshot before you do always on."

For the databases on this ALWAYSON secondary node, snap_isolation_state is equal to 0, indicating that they are all READ COMMITTED default transaction level, and SNAPSHOT isolation level is not used.

Second, guess

1. There may be other system control parameters to determine whether uncommitted transactions block read operations.

2. All the attributes of the auxiliary database stored in its own database are brought from the main database and cannot be modified.

3. It may be that MS determines that it is an auxiliary database and a Read-Only library, so there will be no update operation and no blocking.

III. Verification

Open two sessions, connecting the master library and the slave library respectively. The following table reflects the order of operations from top to bottom, with operations on the same line in no particular order.

Session 1, connect to the main library

Session 2, connect to slave library

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 ('8ax 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 purl 20 purl 49.710 8 picket 12 1420

10606 2019-08-12 14 purl 16 purl 44.333 8 apperance 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 purl 23 purl 33.340 8 Universe 18 1122

10607 2019-08-12 14 purl 20 purl 49.710 8 picket 12 1420

(2 rows affected)

1 > BEGIN TRANSACTION

2 > update bright..testtlb set val = '8Compact 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 purl 23 purl 33.340 8 Universe 18 1122

10607 2019-08-12 14 purl 20 purl 49.710 8 picket 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 purl 23 purl 33.340 8 DUP 18 11-22

10607 2019-08-12 14 purl 20 purl 49.710 8 picket 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 orin an availability group.

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

ALTER DATABASE statement failed.

IV. Conclusion

1. Although the isolation level of the secondary database is shown as READ COMMITED, in fact, uncommitted transactions in the primary database will not block reads on the secondary database.

2. The secondary database cannot read the uncommitted data changes in the main database.

3. The auxiliary library status confirmation is synchronized from the main database.

4. Because the auxiliary library is a Read-Only library, it is not allowed to modify the library

V. basis

Find an excerpt on the secondary database as follows:

A readable secondary copy may be subject to both read and write operations. The read operation comes from the client that connects to it directly or is redirected to it through read-only routing. The write operation only comes from the database synchronization between the primary and secondary databases. Data changes occur in the secondary database only when the log is redone. The client cannot perform data modification operations directly on the secondary database.

Due to the possibility of simultaneous read and write, blocking problems may occur on the secondary database. To ensure the stable operation and performance of read operations, AlwaysOn uses row versioning to eliminate blocking on the secondary database. All queries run against the secondary database are automatically run below the snapshot isolation level. This is true even if you explicitly set other transaction isolation levels for the query. In addition, all lock prompts (Lock Hint) are ignored. All these help to eliminate the blocking problem caused by the scramble between read and write operations to lock data.

Although read operations do not occupy shared locks on data due to snapshot isolation levels, snapshot isolation levels cause reads to occupy Sch-S locks. Sch-S locks still block DDL statements that are redone on the secondary database. Because those DDL statements need to occupy Sch-M locks, and Sch-M locks and Sch-S locks are mutually exclusive.

In addition to blocking, Sch-S locks for read operations can also cause deadlock problems with write operations. In order to ensure the integrity of data synchronization, AlwaysOn stipulates that writes made from data synchronization (redo logs) will never be chosen as victims of deadlocks, no matter how small the cost of the write operation.

V. other

There is no need to switch to read committed snapshot on the secondary database, or add nolock to the statement, because row versioning has been automatically used to eliminate blocking on the secondary database.

In addition, switching to read committed snapshot on the main library, or adding nolock to the statement, can solve the read blocking problem, but it may also involve a change in business logic.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.