lock mechanism for SQL Server (iii)--Overview (Locks and transaction isolation levels)

V. Lock and TRANSACTION isolation levels

Transaction ISOLATION level The simple thing is that when the transaction is activated, the control transaction causes the locking of the SQL statement to be kept multiple, and the extent of the impact, in order to prevent multi-person access, the data query within the transaction error. Setting the transaction isolation level affects the entire connection.

The SQL Server database engine supports all these isolation levels:

· READ UNCOMMITTED (the lowest level of isolation transaction, only guaranteed not to read physically corrupted data)

· Read Committed (the default level of the database engine)

· REPEATABLE READ

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

SQL Server also supports two transaction isolation levels that use row versioning. One is a new implementation of read-committed isolation, and the other is the new transaction isolation level (snapshot).

Set the statement as follows:

SET TRANSACTION Isolation Level {READ UNCOMMITTED | READ COMMITTED | Repeatable READ | SNAPSHOT | SERIALIZABLE } [ ; ]

(i) Non-submitted reading

Uncommitted reads are the lowest transaction isolation level, allowing reading of data rows that other transactions have modified but not committed. SQL SERVER When this transaction level attempts to read data, the shared lock is not placed and the data is read directly, so the existing mutex is ignored. In other words, even if the resource is already protected by an exclusive lock, when the uncommitted read isolation level is used, the data can be read, speed up the query, but read the unmodified data of others, so this kind of reading is called Dirty reading. This isolation level is appropriate for query scenarios that do not care about data changes. This isolation level has the same effect as a SELECT statement paired with NOLOCK

Non-committed Read example:

--1.--1. Creating a Test table

CREATE TABLE Tbunread

(ID INT,

Name nvarchar (20)

)

--2 New Record

Insert Tbunread

Select 1, ' Tom '

Union

Select 2, ' Jack '

--3 turn on the transaction and update it

BEGIN Tran

Update Tbunread

Set Name= ' Jack_upd '

where id=2

---4 query transaction count (due to no rollback or commit TRANSACTION)

SELECT @ @TRANCOUNT

The results of the transaction query are as follows:

--5 open another connection, set the transaction isolation level to (READ UNCOMMITTED)

Set Transaction isolation LEVEL READ UNCOMMITTED

--6 query data, the data that is queried is the modified data.

SELECT * FROM Tbunread where id=2

Such as:

(ii) Read submitted

Read Committed is the default transaction isolation level for SQL SERVER. When a transaction is reading data, SQL SERVER places a shared lock to prevent other transactions from modifying the data, and when the data reads are complete, the shared lock is automatically freed, and other transactions can be modified by the data. Because a shared lock blocks the blocking statement execution at the same time, the data row that the transaction is modifying cannot be read until the transaction completes the data modification. Therefore, this isolation level prevents dirty reads.

In SQL SERVER 2005 or later versions, if the set Read_committed_snapshot is on, read-committed transactions are fully read with data row versioning under isolation. The read operation does not acquire a shared lock (S Lock) on the data being read, so the transaction that is modifying the data is not blocked. At the same time, the cost of locking resources is minimized by reducing the number of locks acquired. Read-committed isolation and snapshot isolation using row versioning are designed to provide statement-level or transactional-level read consistency for replica data.

Example one: Set Read_committed_snapshot to Off

--1. Creating a Test table

CREATE TABLE Tbunread

(ID INT,

Name nvarchar (20)

)

--2 New Record

Insert Tbunread

Select 1, ' Tom '

Union

Select 2, ' Jack '

--3 turn on the transaction and update it

BEGIN Tran

Update Tbunread

Set Name= ' Jack_upd '

where id=2

---4 query transaction count (due to no rollback or commit TRANSACTION)

SELECT @ @TRANCOUNT

--5 open another connection, set the transaction isolation level to (Read Committed)

Set Transaction Isolation LEVEL Read Committed

--6 query data, cannot query data because the current transaction is not committed

SELECT * FROM Tbunread where id=2

6 results of query data such as:

Example two: Setting Read_committed_snapshot to On

Use master

Go

---Create a test database

Create DATABASE Read_committed_snapshot_test

Go

---activating data row versioning

ALTER DATABASE read_committed_snapshot_test set READ_COMMITTED_SNAPSHOT on

Go

Use Read_committed_snapshot_test

Go

--1. Creating a Test table

CREATE TABLE Tbreadlevel

(ID INT,

Name nvarchar (20)

)

--2 New Record

Insert Tbreadlevel

Select 1, ' Test '

Go

Select Id,name as "Pre-modified data" from Tbreadlevel

Such as:

Go

--3 turn on the transaction and update it

BEGIN Tran

Update Tbreadlevel

Set Name= ' Jack_upd '

where id=1

---4 query transaction count (due to no rollback or commit TRANSACTION)

SELECT @ @TRANCOUNT

--5 open another connection, set the transaction isolation level to (Read Committed)

--Query the data, the data is the last submitted data

SELECT * FROM Tbreadlevel where id=1

5 of the query results are as follows:

(iii) REPEATABLE reading

REPEATABLE READ TRANSACTION ISOLATION level during a transaction, all shared locks are retained until the end of the transaction, rather than at the end of the read, which is quite different from the committed read behavior, although the same record is not affected by other transactions during the transaction, but may be caused by too long locking of the data that other people cannot process the data. Affects the concurrency rate, which is more likely to increase the probability of a deadlock occurring.

In summary, if the data is read by using the REPEATABLE read isolation level, the data is read out and other transactions can only read or add to the data in this range, but cannot be modified until the read transaction is complete. Therefore, use of this isolation level requires careful care and is set according to the actual situation.

Example:

--1. Creating a Test table

CREATE TABLE Tbunread

(ID INT,

Name nvarchar (20)

)

--2 New Record

Insert Tbunread

Select 1, ' Tom '

Union

Select 2, ' Jack '

--3 Setting the Transaction isolation level to (repeatable read)

Set Transaction isolation level repeatable READ

--4 turn on the transaction and update it

BEGIN Tran

--5 Querying data

SELECT * FROM Tbunread where id=2

---6 Number of transactions queried (no rollback or COMMIT TRANSACTION)

SELECT @ @TRANCOUNT

The results of 5 and 6 are as follows:

---7 open another connection, query data and modify data

---transaction is not completed, but the previous data can be queried

SELECT * FROM Tbunread where id=2

Go

---8, modify the data, cannot be modified because the transaction is not completed

Update Tbunread

Set Name= ' Jack_upd '

where id=2

Go

--7, 8 executes as follows, can query data, but cannot update data, such as.

(iv) Snapshots

The snapshot isolation level is the new isolation level after SQL SERVER 2005, which, when turned on, allows the read operation to be unaffected by the change in the transaction while the data read by any statement in the transaction is activated, commits are completed, and the data row version conforms to the transactional consistency. So you can only check the data that was committed before the transaction was activated, that is, you could query the set of data rows that have completed the commit, but you cannot see the data row that the activated transaction is modifying. When reading data using the snapshot isolation level, the data is not required to be locked, and if the record being read is being modified by a transaction, it will also read the data that was committed before the record. Therefore, when a record is modified by a transaction, the tempdb database of SQL Server stores the most recently committed row of data for use when the transaction at the snapshot isolation level reads the data. Set Allow_snapshot_isolation to ON, and the transaction sets the snapshot isolation level.

Use master

Go

---Creating a test database (snapshot)

Create DATABASE Snapshot_test

Go

---activating data row versioning

ALTER DATABASE snapshot_test set allow_snapshot_isolation on

Go

Use Snapshot_test

Go

--1. Creating a Test table

CREATE TABLE Tbreadlevel

(ID INT,

Name nvarchar (20)

)

--2 New Record

Insert Tbreadlevel

Select 1, ' Test '

Union

Select 2, ' Snapshot test '

Go

Select Id,name as "pre-modification data"

From Tbreadlevel

Go

--3 turn on the transaction and update it

BEGIN Tran

Update Tbreadlevel

Set name= ' Jack_upd_ snapshot '

where id=1

---4 Number of transactions queried (no rollback or COMMIT TRANSACTION)

SELECT @ @TRANCOUNT

--2, 4 of the execution results, such as.

--5 open another connection, set the transaction isolation level to (snapshot)

Set Transaction Isolation Level SNAPSHOT

--6 query data, query data is the last committed data

SELECT * FROM Tbreadlevel where id=1

(v) Serializable

Serializable is the highest level in the transaction isolation level, which is the most stringent isolation level because it locks the index key for the entire range and makes the transaction completely isolated from other transactions. Until the current transaction completes, the other transaction cannot insert a new data row, and its index key value exists in the index key range read by the current firm. This isolation level is the same as select with the Holdlock effect.

Example:

--1. Creating a Test table

CREATE TABLE Tbunread

(ID INT,

Name nvarchar (20)

)

--2 New Record

Insert Tbunread

Select 1, ' Tom '

Union

Select 2, ' Jack '

--3 Setting the Transaction isolation level to (serializable)

Set Transaction Isolation Level SERIALIZABLE

--5 turn on the transaction and update it

BEGIN Tran

SELECT * FROM Tbunread where id=2

---6 Number of transactions queried (no rollback or COMMIT TRANSACTION)

SELECT @ @TRANCOUNT

5, 6 implementation results such as.

---7, open another connection, query the data, you can query to the previous data

SELECT * FROM Tbunread where id=2

---8, modify data, cannot modify data

Update Tbunread

Set Name= ' Jack_upd '

where id=2

--new data, unable to insert data

Insert Tbunread

Select 3, ' may '

lock mechanism for SQL Server (iii)--Overview (Locks and transaction isolation levels)