SQL Server lock mechanism (iii) Overview (lock and transaction isolation level)

Link to SQL Server's lock mechanism (ii) Overview (Lock compatibility and lockable resources) v. Lock and transaction isolation level simply put, when a transaction is activated, control the number of rows that need to be retained for the locks generated by SQL statements in the transaction and the impact scope to prevent data query errors in the transaction when multiple users access the transaction. Set transactions

Next, SQL SERVER's lock mechanism (ii) Overview (Lock compatibility and lockable resources) 5. Lock and transaction isolation level simply put, it means that when activating a transaction, the lock generated by the SQL statement in the transaction needs to be retained for multiple inbound operations and the scope of impact should be controlled to prevent data query errors in the transaction when multiple users access the transaction. Set transactions

Link to the SQL SERVER lock mechanism (II) -- Overview (Lock compatibility and lockable resources)

5. Lock and transaction isolation level

Transaction isolation level simply put, the Hong Kong server means that when activating a transaction, the locks generated by the SQL statement in the transaction need to be retained and the impact scope is large to prevent access by multiple users, a Data Query error occurs in the transaction. Setting the transaction isolation level will affect the entire connection.

The SQL Server database engine supports all these isolation levels:

· Uncommitted read (the lowest level of the isolation transaction, only ensure that the physical damage data is not read)

· Committed read (default database engine level)

· Repeatable read

· Serializable (the highest level of isolation transactions, full isolation between transactions)

SQL Server also supports two transaction isolation levels controlled by row version. One is the new implementation of committed read isolation, and the other is the new transaction isolation level (snapshot ).

The setting statement is as follows:

SET TRANSACTION ISOLATION LEVEL

{READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SNAPSHOT

| SERIALIZABLE

}

[;]

(1) uncommitted read

Uncommitted read is the lowest transaction isolation level. It allows you to read data rows that have been modified but not committed by other transactions. When SQL SERVER attempts to read data at this transaction level, it does not place a shared lock and directly reads data. Therefore, it ignores the existing mutex lock. In other words, even if the resource has been protected by an exclusive lock, the data can still be read when the uncommitted read isolation level is used to speed up the query, but it will read data that has not been modified by others, so such read is called dirty read. This isolation level is suitable for queries that do not care about data changes. This isolation level works the same as that of the SELECT statement and NOLOCK statement.

Example of uncommitted read:

-- 1. -- 1. Create a test table

Create table tbUnRead

(Id int,

Name nvarchar (20)

)

-- 2 Add a record

Insert tbUnRead

Select 1, 'Tom'

Union

Select 2, 'jack'

-- 3. Start and update the transaction

Begin tran

Update tbUnRead

Set name = 'Jack _ upd'

Where ID = 2

SELECT @ TRANCOUNT

The transaction query result is as follows:

Set Transaction isolation level read uncommitted

-- 6. query data. The queried data is the modified data.

Select * from tbUnRead where ID = 2

For example:

(2) Read committed

Read data under the isolation control. The read operation will not obtain the shared lock (S lock) on the data being read, so it will not block the transaction that is modifying the data. At the same time, the Hong Kong server minimizes the overhead of locking resources because it reduces the number of locks obtained. Committed read isolation using row version control and Snapshot isolation are designed to provide statement-level or transaction-level read consistency for copy data.

Example 1: Set READ_COMMITTED_SNAPSHOT to OFF

-- 1. Create a test table

Create table tbUnRead

(Id int,

Name nvarchar (20)

)

-- 2 Add a record

Insert tbUnRead

Select 1, 'Tom'

Union

Select 2, 'jack'

-- 3. Start and update the transaction

Begin tran

Update tbUnRead

Set name = 'Jack _ upd'

Where ID = 2

SELECT @ TRANCOUNT

Set Transaction isolation level read committed

-- 6 query data. The data cannot be queried because the current transaction is not committed.

Select * from tbUnRead where ID = 2

6. The data query result is as follows:

Example 2: Set READ_COMMITTED_SNAPSHOT to ON

Use master

Go

--- Create a Test Database

Create database read_committed_SNAPSHOT_Test

Go

--- Activate data row Version Control

Alter database read_committed_SNAPSHOT_Test set read_committed_SNAPSHOT on

Go

Use read_committed_SNAPSHOT_Test

Go

-- 1. Create a test table

Create table tbReadLevel

(Id int,

Name nvarchar (20)

)

-- 2 Add a record

Insert tbReadLevel

Select 1, 'test'

Go

Select ID, name as "data before modification" from tbReadLevel

For example:

Go

-- 3. Start and update the transaction

Begin tran

Update tbReadLevel

Set name = 'Jack _ upd'

Where ID = 1

SELECT @ TRANCOUNT

-- Query data. The queried data is the data submitted last time.

Select * from tbReadLevel where ID = 1

5. the query result is as follows:

(3) Repeatable read