Deeply analyze transaction isolation level and lock mechanism in MSSQL database _mssql

Lock mechanism
The difference between Nolock and Readpast.

1. Open a transaction to perform the Insert data operation.

BEGIN TRAN t

INSERT into Customer

SELECT ' A ', ' a '

2. Execute a query statement.

SELECT * from Customer with (NOLOCK)

The results show "a" and "a". When a transaction is rolled back in 1, then a will become dirty data. (Note: The transaction in 1 is not committed). Nolock indicates that no shared locks have been added to the datasheet to prevent other transactions from modifying the data table data.

SELECT * from Customer

This statement will remain deadlocked until the exclusive lock is lifted or the lock times out. (Note: Set lock timeout set lock_timeout 1800)

SELECT * from Customer with (READPAST)

This statement displays the state before a is uncommitted, but does not lock the entire table. This hint indicates that a locked row or data page is ignored when the database engine returns results.

3. Executes an INSERT statement.

BEGIN TRAN t

INSERT into Customer

SELECT ' B ', ' B '

COMMIT TRAN t

This time, even if the transaction of Step 1 is rolled back, the data of a is lost and B continues to insert into the database.

NOLOCK

1. Execute the following statement.

BEGIN TRAN TTT

SELECT * from Customer with (NOLOCK)

WAITFOR delay ' 00:00:20 '

COMMIT TRAN TTT

Note: Nolock without any lock, you can add or delete changes without locking.

INSERT into Customer SELECT ' a ', ' B ' – Do not lock

DELETE Customer where id=1– do not lock

SELECT * from customer– do not lock

UPDATE Customer SET title= ' AA ' WHERE id=1– not locked

Rowlock

1. Execute a query statement with row locks.

SET TRANSACTION Isolation Level repeatable READ-(must)

BEGIN TRAN ttt

SELECT * from Customer with (Rowlock) whe RE id=17

WAITFOR delay ' 00:00:20 '

COMMIT TRAN TTT

Note: When you delete and update the data that you are querying, the data is locked. The query data has no effect on other rows and additions that are not queried.

INSERT into Customer SELECT ' a ', ' B ' – Do not wait for

delete customer where id=17– wait for

Delete customer where id<>17– do not wait 
   select * FROM customer– does not wait for

update customer set title= ' AA ' where id=17– waits for

update customer set title= ' AA ' where id<>17– not waiting

Holdlock,tablock and Tablockx

1. Executive Holdlock

BEGIN TRAN TTT

SELECT * from Customer with (HOLDLOCK)

WAITFOR delay ' 00:00:10 '

COMMIT TRAN TTT

Note: Other transactions can read the table, but cannot update the deletion

Update Customer set title= ' AA '-wait 10 seconds.

SELECT * from customer-does not need to wait

2. Executive Tablockx

BEGIN TRAN TTT

SELECT * from Customer with (TABLOCKX)

WAITFOR delay ' 00:00:10 '

COMMIT TRAN TTT

Note: Other transactions cannot read tables, update and delete

Update Customer set title= ' AA '-wait 10 seconds.

SELECT * from customer-to wait 10 seconds.

3. Executive Tablock

BEGIN TRAN TTT

SELECT * from Customer with (TABLOCK)

WAITFOR delay ' 00:00:10 '

COMMIT TRAN TTT

Note: Other transactions can read the table, but cannot update the deletion

Update Customer set title= ' AA '-wait 10 seconds.

SELECT * from customer-does not need to wait

Udplock

1. Execute in a connection.

BEGIN TRAN TTT

SELECT * from Customer with (UPDLOCK)

WAITFOR delay ' 00:00:10 '

COMMIT TRAN TTT

2. Perform in other connections.

Update Customer set title= ' AA ' where id=1-to wait 10 seconds

SELECT * from customer– not wait

Insert INTO Customer select ' A ', ' B '-Don't Wait

Note: For Udplock locks, only the update data is locked.

Note: Using these options will cause the system to ignore the transaction isolation level previously set in the SET statement (set Transaction isolation Levels).

Transaction ISOLATION LEVEL

Dirty reads: READ UNCOMMITTED

Dirty reading means that when a transaction is accessing the data and the data has been modified, and the modification has not yet been submitted to the database, then another transaction accesses the data and then uses the data. Because this data is not yet submitted data, then another transaction read the data is dirty data, the operation based on dirty data may be incorrect.

1. Execute in a connection.

BEGIN TRAN t

INSERT into Customer

SELECT ' 123 ', ' 123 '

WAITFOR delay ' 00:00:20 '

COMMIT TRAN t

2. Execute in B connection.

SET TRANSACTION Isolation Level READ UNCOMMITTED

SELECT * from Customer

This time, the uncommitted data will be ' 123 ' displayed, causing dirty data when a transaction rolls back. Equivalent to (NOLOCK)

Submit READ: Read Committed

1. Execute in a connection.

BEGIN TRAN t

INSERT into Customer

SELECT ' 123 ', ' 123 '

WAITFOR delay ' 00:00:20 '

COMMIT TRAN t

2. Execute in B connection.

SET TRANSACTION Isolation Level READ committed

SELECT * from Customer

At this time, the uncommitted data will be ' 123 ' not displayed, and the data can be read in B after the a transaction is committed. The dirty reads are avoided.

Non-repeatable reads: REPEATABLE READ

Non-repeatable reading refers to reading the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction and the modification of the second transaction, the data read two times for the first transaction may be different. This makes it happen that the data read two times within a transaction is not the same, so it is called a non repeatable read.

For example:

1. Execute the following statement in a connection.

SET TRANSACTION Isolation level repeatable READ

BEGIN TRAN ttt

SELECT * from Customer WHERE id=17 WAITFOR dela

Y ' 00:00:30 '

SELECT * from Customer WHERE id=17

COMMIT TRAN TTT

2. Execute the following statement in a B connection and wait within 30 seconds of the first thing.

UPDATE Customer SET title= ' d ' WHERE id=17

This time, the connection will be locked and cannot be performed until the a connection is finished. The same data is read two times in a connection and is not interfered by B connection.

Note, for Read committed and read UNCOMMITTED case, B connection will not be locked, wait until a connection is executed, two query statement results are different, that is, the second query title changed to D.

Serialized READ: SERIALIZABLE

1. Execute in a connection.

Set TRANSACTION Isolation level SERIALIZABLE

BEGIN TRAN t

UPDATE Customer SET title= ' $ ' WAITFOR '

00: 00:20 '

COMMIT TRAN t

2. Executed in a B connection, and within 20 seconds after a is executed.

BEGIN TRAN tt

INSERT into Customer

SELECT ' 2 ', ' 2 '

COMMIT TRAN tt

Before a connected transaction commits, a B connection cannot insert data into the table, which avoids the illusion of reading.

Note: Hallucination is a phenomenon that occurs when a transaction is not executed independently, for example, the first transaction modifies the data in a table that involves all the rows of data in the table. At the same time, the second transaction modifies the data in this table, which is inserting a row of new data into the table. So, it's going to happen later. Users of the first transaction find that there are no modified rows of data in the table, as if there were hallucinations.

Shared locks

Shared locks (S locks) allow concurrent transactions to read (select) resources under closed concurrency control (see Types of concurrency control). When a shared lock (S Lock) exists on a resource, no other transaction can modify the data. Once the read operation completes, the shared (s) lock on the resource is released immediately, unless the transaction isolation level is set to repeatable read or higher, or a shared lock (S lock) is reserved with a lock hint during the transaction duration.

Update lock

Update locks (U locks) can prevent common deadlocks. In a repeatable read or serializable transaction, this transaction reads data [Gets the shared lock (S) of a resource (page or row)] and modifies the data [this operation requires that the lock be converted to an exclusive (X) lock]. If two transactions obtain a shared schema lock on the resource and then attempt to update the data at the same time, a transaction attempts to convert the lock to an exclusive (X) lock. The conversion of shared mode to exclusive lock must wait for some time because the exclusive lock of one transaction is incompatible with the shared mode lock of other transactions; The second transaction attempted to acquire an exclusive (X) lock for an update. A deadlock occurs because two transactions are converted to exclusive (X) locks, and each transaction waits for another transaction to free a shared-mode lock.

To avoid this potential deadlock problem, use an update lock (U lock). Only one transaction at a time can obtain an update lock (U Lock) for the resource. If a transaction modifies a resource, the update lock (U Lock) is converted to an exclusive (X) lock.

Exclusive lock

Exclusive locks (X locks) prevent concurrent transactions from accessing resources. When an exclusive (X) lock is used, no other transaction can modify the data, and the read operation is only performed when the NOLOCK hint is used or the Read isolation level is not committed.

Data modification statements, such as INSERT, UPDATE, and DELETE, combine modification and read operations. Statement performs a read operation to get the data before performing the desired modification operation. Therefore, data modification statements typically request shared and exclusive locks. For example, an UPDATE statement might modify a row in another table based on a join to a table. In this case, in addition to requesting an exclusive lock on the update row, the UPDATE statement requests a shared lock on the row read in the junction table.