In-depth analysis of the transaction isolation level and lock mechanism in the MSSQL database, in-depth analysis of mssql

In-depth analysis of the transaction isolation level and lock mechanism in the MSSQL database, in-depth analysis of mssql

Lock Mechanism
The difference between NOLOCK and READPAST.

1. Start a transaction to insert data.

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

2. Execute a query statement.

SELECT * FROM Customer WITH (NOLOCK)

"A" and "a" are displayed in the result ". When the transaction in 1 is rolled back, a will become dirty data. (Note: transactions in 1 are not committed ). NOLOCK indicates that no shared lock is added to the data table to prevent other transactions from modifying the data table.

SELECT * FROM Customer

This statement will remain deadlocked until the exclusive lock is lifted or the lock times out. (Note: SET LOCK_TIMEOUT 1800 to SET lock timeout)

SELECT * FROM Customer WITH (READPAST)

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

3. Execute an insert statement.

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

At this time, even if the transaction in step 1 is rolled back, the data of 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 apply any lock. You can add, delete, query, and modify it without locking it.

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 = 'A' WHERE ID = 1-do not lock

ROWLOCK

1. Execute a query statement with a row lock.

Set transaction isolation level repeatable read -- (required) TRAN in TRAN tttSELECT * FROM Customer WITH (ROWLOCK) where id = 17 WAITFOR delay '00: 00: 20' commit tran ttt

Note: Data is locked when you delete or update the data being queried. Other unqueried rows and addition are not affected.

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 for SELECT * FROM Customer-do not wait for UPDATE Customer SET Title = 'A' WHERE ID = 17-waiting for UPDATE Customer SET Title = 'A' where id <> 17-not waiting

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 or Delete tables.

Update Customer set Title = 'a'-Wait 10 seconds.

SELECT * FROM Customer-no need to wait

2. Execute TABLOCKX

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

Note: Other transactions cannot read, update, or delete tables.

Update Customer set Title = 'a'-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 or Delete tables.

Update Customer set Title = 'a'-Wait 10 seconds.

SELECT * FROM Customer-no need to wait

UDPLOCK

1. Run the command in connection.

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

2. Execute in other connections.

Update Customer set Title = 'A' where ID = 1-Wait 10 seconds

SELECT * FROM Customer-No

Insert into Customer select 'A', 'B'-No

Note: Only update data is locked for the UDPLOCK lock.

Note: using these options will cause the system to ignore the Transaction Isolation Level (SET Transaction Isolation Level) originally SET in the SET statement ).

Transaction isolation level

Dirty READ: READ UNCOMMITTED

Dirty read means that when a transaction is accessing data and modifying the data has not been committed to the database, another transaction also accesses the data, then the data is used. Because the data has not been committed, the data read by another transaction is dirty data, and the operations performed based on the dirty data may be incorrect.

1. Run the command in connection.

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

2. Execute in B connection.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT * FROM Customer

At this time, uncommitted data will be displayed as '123', causing dirty data when transaction A rolls back. Equivalent to (NOLOCK)

Read committed: READ COMMITTED

1. Run the command in connection.

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

2. Execute in B connection.

SET TRANSACTION ISOLATION LEVEL READ COMMITTEDSELECT * FROM Customer

At this time, uncommitted data will not be displayed as '1234568'. data can be read in transaction B only after transaction A is committed. Avoid dirty reading.

REPEATABLE READ

A non-repeated read refers to reading the same data multiple times in a transaction. When the transaction is not completed, another transaction also accesses the same data. Therefore, the data read twice in the first transaction may be different because of the modification of the second transaction. In this way, the data read twice in a transaction is different, so it is called non-repeated read.

For example:

1. Execute the following statement in 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 B connection, and wait 30 seconds for the first transaction.

UPDATE Customer SET Title = 'd where id = 17

At this time, the connection will be locked and cannot be executed until the end of connection. In addition, the data read twice in connection A is the same and is not affected by connection B.

Note: For Read Committed and Read UnCommitted, the B connection is not locked. After the connection is executed, the two query statements have different results, that is, the Title of the second query is changed to d.

Serialized read: SERIALIZABLE

1. Run the command in connection.

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

2. Execute in connection B, and within 20 seconds after execution of.

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

Before the transaction of A connection is committed, B connection cannot insert data into the table, which avoids phantom reading.

Note: phantom read refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all the data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still unmodified data rows in the table, just like an illusion.

Shared lock

The S lock allows concurrent transactions to read (SELECT) resources under the closed concurrency control (see the type of concurrency control. When a resource has a shared lock (S lock), no other transactions can modify the data. Once the read operation is complete, the shared lock (S lock) on the resource is immediately released, unless the transaction isolation level is set to repeated read or higher, or use the lock prompt to keep the shared lock (S lock) during the transaction duration ).

Update lock

Update locks (U locks) can prevent common deadlocks. In a repeatable or serializable transaction, this transaction reads data [getting a shared lock (S lock) for resources (pages or rows)], then modify the data [this operation requires the lock to be converted to the exclusive lock (X lock)]. If two transactions obtain the Shared Mode Lock on the resource and attempt to update the data at the same time, a transaction attempts to convert the lock to an exclusive lock (X lock ). The conversion from the sharing mode to the exclusive lock must wait for a while, because the exclusive lock of one transaction is not compatible with the Sharing Mode Lock of other transactions; a lock wait occurs. The second transaction attempts to obtain the exclusive lock (X lock) for updates. Because both transactions need to be converted to exclusive locks (X locks), and each transaction waits for the other transaction to release the share mode lock, a deadlock occurs.

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

Exclusive lock

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

Data modification statements (such as INSERT, UPDATE, and DELETE) combine the modification and read operations. Before executing the required modification operation, the statement first performs the read operation to obtain data. Therefore, data modification statements usually request a shared lock and an exclusive lock. For example, the UPDATE statement may modify rows in another table based on the join with one table. In this case, in addition to the exclusive lock on the row to be updated, the UPDATE statement also applies the shared lock on the row to be read from the join table.

What is the SQL locking mechanism?

Hello! Locks are a very important concept in databases. They are mainly used to ensure database integrity and consistency in multi-user environments.
We know that data inconsistency occurs when multiple users can manipulate data in the same database at the same time. That is, if the transaction is not locked and multiple users access a database at the same time, the problem may occur when their transactions use the same data at the same time. These problems include loss of updates, dirty reads, non-repeated reads, and Phantom reads. Database locking is used to solve the above problems.
Of course, locking is good, but it is necessary to avoid deadlocks.
In the database system, a deadlock occurs when multiple users (processes) Lock a resource respectively and attempt to request to lock the resources that the other user has locked. This produces a lock request ring, as a result, multiple users (processes) are waiting for the other party to release the locked resources. This deadlock is the most typical form of deadlock. For example, there are two transactions A and B at the same time, and transaction A has two operations: locking the table part and requesting access table supplier; transaction B also has two operations: locking the table supplier and requesting the Access Table part. As A result, A deadlock occurs between transaction A and transaction B. The second case of deadlock is that when in a database, several long-running transactions execute parallel operations, when the query analyzer processes a very complex query, such as a connection query, the processing sequence cannot be controlled, and deadlock may occur.
In the application, you can use the following methods to avoid deadlocks: (1) Reasonably arrange the table access sequence. (2) Avoid user intervention in transactions as much as possible, minimize the number of tasks processed by a transaction, and keep the transaction short and in a batch. (3) Time-Domain discretization for data access. The time-domain discretization for data access refers to various control measures used in the Client/Server structure to control the time period for accessing objects in the database or database. The following methods are used to reasonably arrange the execution time of background transactions and manage background transactions using workflows. When a workflow manages a task, it restricts the number of threads of the same type of tasks (usually one) to prevent excessive resource occupation. On the other hand, it rationally schedules the time series and times of running different tasks, avoid executing multiple background tasks at the same time. In addition, avoid running background tasks at the front-end transaction peak hours. (4) data storage space discretization method. The data storage space discretization method is used to distribute data in a logical table to several discrete spaces, so as to improve the table access performance. The following methods are used: first, a large table is divided into several small tables by row or column. Second, it is divided by different user groups. (5) use the isolation level as low as possible. Isolation level refers to the degree of multi-user transaction isolation to ensure the integrity and consistency of database data. SQL92 defines four isolation levels: uncommitted read, committed read, Repeatable read, and serializable. If a high isolation level is selected, such as serializability, although the system can guarantee data integrity and consistency to a greater extent for better isolation, however, the chance of deadlocks due to conflicts between transactions is greatly increased, which greatly affects the system performance. (6) use the BIND connection to allow two or more transaction connections to share transactions and locks, and any transaction connection must apply for a lock as if another transaction wants to apply for a lock, therefore, you can allow these transactions to share data without locking conflicts.
In short, it is very important for a qualified DBA to understand the locking mechanism of SQL Server and master the database locking method.
 
SQL server issues transaction isolation level,

You have two processes (for example, two in the same tool)

The setting is not automatically submitted.

One program operates on the same table, and the other program reads data

You can see the difference.

If you search for this, there should be a ready-made example.