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