Shulou(Shulou.com)05/31 Report--

This article mainly explains "Introduction to transaction isolation level and locking mechanism in MSSQL database". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian to study and learn "Introduction to transaction isolation level and locking mechanism in MSSQL database" together.

The difference between NOLOCK and READPAST locking mechanisms.

1. Start a transaction to insert data.

BEGIN TRAN tINSERT INTO CustomerSELECT 'a','a'

2. Executes a query statement.

SELECT * FROM Customer WITH (NOLOCK)

The results show "a" and "a." When the transaction in 1 rolls back, then a becomes dirty data. (Note: transactions in 1 are not committed). NOLOCK indicates that no shared lock has been added to the data table to prevent other transactions from modifying the data table.

SELECT * FROM Customer

This statement deadlocks until the exclusive lock is released or the lock times out. (Note: SET LOCK_TIMEOUT 1800)

SELECT * FROM Customer WITH (READPAST)

This statement displays the state of a before it was committed, but does not lock the entire table. This prompt instructs the database engine to ignore locked rows or data pages when returning results.

3. Executes an insert statement.

BEGIN TRAN tINSERT INTO CustomerSELECT 'b','b'COMMIT TRAN t

At this point, even if the transaction in step 1 rolls back, the data in a will be lost and b will continue to be inserted into the database.

NOLOCK

1. Execute the following statement.

BEGIN TRAN tttSELECT * FROM Customer WITH (NOLOCK)WAITFOR delay '00:00:20'COMMIT TRAN ttt

Note: NOLOCK does not add any locks, and can be added, deleted, checked and modified without locking.

INSERT INTO Customer SELECT 'a',' b'-Unlocked Deleting Customer where ID=1 -Unlocked SELECT * FROM Customer -Unlocked UPDATE Customer SET Title='aa' WHERE ID=1 -Unlocked

ROWLOCK

1. Executes a query statement with a row lock.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- (Required)BEGIN TRAN tttSELECT * FROM CUSTOMER WITH (ROWLOCK) WHERE ID=17WAITFOR delay '00:00:20'COMMIT TRAN ttt

Note: Data is locked when deleting and updating the data being queried. For other unqueried rows and additions, query data has no effect.

INSERT INTO Customer SELECT 'a',' b'-do not wait for Deleted Customer where ID=17 -do not wait for Deleted Customer where ID17 -do not wait for SELECT * FROM Customer -do not wait for UPDATE Customer SET Title='aa' WHERE ID=17-do not wait for UPDATE Customer SET Title='aa' WHERE ID17-do not wait

HOLDLOCK, TABLOCK and TABLOCKX

1. Execute HOLDLOCK

BEGIN TRAN tttSELECT * FROM Customer WITH (HOLDLOCK)WAITFOR delay '00:00:10'COMMIT TRAN ttt

Note: Other transactions can read tables, but cannot update deletes

update Customer set Title='aa' -Wait 10 seconds.

SELECT * FROM CUSTOMER -No waiting

2. EXECUTION TABLOCKX

BEGIN TRAN tttSELECT * 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 -Wait 10 seconds.

3. Execute TABLOCK

BEGIN TRAN tttSELECT * FROM Customer WITH (TABLOCK)WAITFOR delay '00:00:10'COMMIT TRAN ttt

Note: Other transactions can read tables, but cannot update deletes.

update Customer set Title='aa' -Wait 10 seconds.

SELECT * FROM CUSTOMER -No waiting

UDPLOCK

1. Executed in connection A.

BEGIN TRAN tttSELECT * FROM Customer WITH (UPDLOCK)WAITFOR delay '00:00:10'COMMIT TRAN ttt

2. Executed in other connections.

update Customer set Title='aa' where ID=1-Wait 10 seconds

SELECT * FROM CUSTOMER -No waiting

insert into Customer select 'a',' b'-No wait

Note: For UDPLOCK locks, only update data is locked.

Note: Using these options causes the system to ignore the SET Transaction Isolation Level originally set in the SET statement.

transaction isolation level

Dirty Read: READ UNCOMMITTED

A dirty read is when a transaction accesses data and makes changes to the data that have not yet been committed to the database, and another transaction accesses the data and uses the data. Because this data is uncommitted, the data read by another transaction is dirty, and the operation performed on the dirty data may be incorrect.

1. Executed in connection A.

BEGIN TRAN tINSERT INTO CustomerSELECT '123','123'WAITFOR delay '00:00:20'COMMIT TRAN t

2. Executed in B connection.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT * FROM Customer

At this point, the uncommitted data will show up as '123', resulting in dirty data when transaction A rolls back. Equivalent to (NOLOCK)

Submitted Read: READ COMMITTED

1. Executed in connection A.

BEGIN TRAN tINSERT INTO CustomerSELECT '123','123'WAITFOR delay '00:00:20'COMMIT TRAN t

2. Executed in B connection.

SET TRANSACTION ISOLATION LEVEL READ COMMITTEDSELECT * FROM Customer

At this time, uncommitted data will not be displayed '123', and data can only be read from B after transaction A commits. Avoid dirty reading.

REPEATABLE READ

Non-repeatable reading refers to reading the same data multiple times within a transaction. Before the transaction ends, another transaction accesses the same data. Then, between the two reads in the first transaction, the data read in the first transaction may not be the same due to the modification of the second transaction. This happens because the data read twice within a transaction is different, and is therefore called non-repeatable.

For example:

1. Execute the following statement in the A connection.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRAN tttSELECT * FROM Customer WHERE ID=17WAITFOR delay '00:00:30'SELECT * FROM Customer WHERE ID=17COMMIT TRAN ttt

2. Execute the following statement in the B connection within thirty seconds of the first thing.

UPDATE Customer SET Title='d' WHERE ID=17

At this point, the connection will be locked and cannot be executed until the A connection ends. Moreover, the data read twice in connection A is the same and is not disturbed by connection B.

Note: For Read Committed and Read UnCommitted cases, the B connection will not be locked. After the A connection is executed, the results of the two query statements are different, that is, the Title of the second query becomes d.

Serialization Read: SERIALIZABLE

1. Executed in connection A.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRAN tUPDATE Customer SET Title='111'WAITFOR delay '00:00:20'COMMIT TRAN t

2. Executed in connection B and within 20 seconds of execution of A.

BEGIN TRAN ttINSERT INTO CustomerSELECT '2','2'COMMIT TRAN tt

Connection B cannot insert data into the table until connection A's transaction commits, which avoids phantom reads.

Note: Phantom read refers to a phenomenon that occurs when transactions are not executed independently, such as the first transaction modifies the data in a table, and this modification involves all data rows in the table. At the same time, the second transaction modifies the data in this table by inserting a new row into the table. Then, later on, the user operating on the first transaction discovers that there are still unmodified rows in the table, as if an illusion had occurred.

shared lock

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. Shared locks (S-locks) on resources are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or shared locks (S-locks) are retained for the duration of the transaction with a lock hint.

update lock

Update locks (U locks) prevent common deadlocks. In a repeatable or serializable transaction, the transaction reads the data [acquires a shared lock (S lock) on a resource (page or row)] and then modifies the data [this operation requires the lock to be converted to an exclusive lock (X lock)]. If two transactions acquire a shared-mode lock on a resource and then attempt to update the data simultaneously, one transaction attempts to convert the lock to an exclusive lock (X lock). The shared-mode to exclusive lock transition must wait a while because the exclusive lock of one transaction is incompatible with the shared-mode lock of another transaction; lock waiting occurs. The second transaction attempts to acquire an exclusive lock (X lock) for an update. Deadlocks occur because both transactions are converted to exclusive locks (X-locks) and each transaction waits for the other transaction to release the shared-mode lock.

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

exclusive lock

Exclusive locks (X-locks) prevent concurrent transactions from accessing resources. When exclusive locks (X-locks) are used, no other transaction can modify the data; reads occur only when the NOLOCK hint is used or when 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 data before performing the desired modification operation. Therefore, data modification statements typically request shared locks and exclusive locks. For example, an UPDATE statement might modify rows in one table based on a join to another table. In this case, in addition to requesting an exclusive lock on the updated row, the UPDATE statement requests a shared lock on the row read in the join table.

Thank you for reading, the above is the "MSSQL database transaction isolation level and lock mechanism introduction" content, after the study of this article, I believe you have a deeper understanding of the MSSQL database transaction isolation level and lock mechanism introduction, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.