SQL Server lock queuing mechanism, SQL Server queuing mechanism

SQL Server lock queuing mechanism, SQL Server queuing mechanism

1. Create a new table and insert 1010000 data:

create table test(id int identity(1,1) ,name varchar(600)) goinsert into testvalues(replicate('a',600));go 1010000create index idx_test_id on test(id)

2. Open A new session (A) and run the following statement. Because the statement is not submitted, the session with the same data modified by other medicines will be blocked:

begin tranupdate test set name = replicate('f',500)where id = 100000

3. Open another session (B) and run the following statement. The data with the id of 100000 needs to be modified, so it is blocked by the previous session.

Note that when the query prompt repeatableread is used in the statement, the S lock obtained by the select statement on the record will be released only when commit or rollback is used.

begin transelect *from test with(repeatableread)where id >= 9000 and id <= 100000select  sysdatetime()

4. Open another session (C) and run the following statement. The session A is blocked because the data with the id of 100000 needs to be modified.

begin tran  update test set name = replicate('k',589)where id = 100000select  sysdatetime()

5. In session A, commit the transaction through commit. At this time, session B will return the result immediately, but Session C continues to be blocked. At this time, Session C is blocked by session B.

Next, execute commit in session B, so Session C returns immediately.

From the above phenomenon, we can see that the queue mechanism in the database lock, that is, when many sessions need to access the same resource, there will be a queue mechanism, at first, A added an exclusive lock to the resource to obtain the resource but did not submit it. Therefore, the lock on the resource was not released.

Session B also needs to access the same resource. Because the session needs to read data, a shared lock is required. However, the shared lock is incompatible with the exclusive lock on the previous resource, as a result, session B is blocked, and session B enters a waiting queue.

Session C also needs to access the same resource. To modify data, an exclusive lock is required. However, session A is not compatible with the exclusive lock, as a result, Session C is also blocked, so Session C will also enter the waiting queue, after session B.

Reasons for blocking Session C:

It can be seen that when Session C changes the IU lock on Page 1: 21339 to the IX lock, the Session C is in the convert State without a grant, and the session C is to be modified, it is because the data with the id of 100000 needs to be modified.

Previously, the IU lock was applied to 1: 21339 and the u Lock was applied to the RID. to modify the data on this page, you must change the IU lock to the IX lock, if the operation succeeds, change the U lock to the X lock.

From the above analysis, we can see that the lock in SQL Server adopts the queue mechanism, that is, first-in-first-out.

Sqlserver lock Table Mechanism

This issue should be analyzed in detail:
First, there are two basic transaction isolation modes: read committed, repeatable read, and serializable)
, A non-blocking (read uncommitted, snapshot ).

The default value is read committed. In this case, when updating a table, if you do not use the hint prompt, you can add the IX lock to the table first. The level is not high and it is basically compatible with other locks, however, for repeatable read, the serializable transaction isolation level will first add the IX lock to the table and then convert it to the X lock. The X lock is incompatible with most of the locks and is prone to table blocking.

The second isolation level does not have the above problems, but introduces other problems.

The above is a case.
Another type is lock upgrade. One lock is 96 B memory. If there is too much memory, sqlserver will be upgraded to a table lock. Generally, a row-Level Lock of more than 5000 will be upgraded to a table X lock.
Therefore, appropriate file groups and table partitions are necessary.

The second reason is that the transactions cannot be released for a long time due to mutual resource reference, which leads to real deadlocks. However, after SQL2005, the probability of such a situation is very low.

Leave a question for yourself.

Two SQL statements, two connections, and execution at the same time.

Update A set A. NAME = xxx where A. id = 55

Update A set A. NAME = xxx where A. id = 56. If 56 does not exist, what will happen?
 
Why is the deadlock mechanism introduced in SQL server?

What the first two brothers answered was not a deadlock, but a normal lock.

The deadlock is formed in this way. Assume there are two things: A and B.
Transaction A needs to update two tables during execution, for example, T1 and T2. At this time, transaction A has executed T1 and is applying to use T2.
Things B also needs to update the two tables, but things B executes T2. applying to use T1,
Because T1 has been locked by transaction A, transaction B must wait for transaction A to release the lock. However, transaction A is applying for T2. T2. transaction B is locked first, the T2 lock can be obtained only after the lock is released for transaction B. The deadlock occurs now. If there is no deadlock mechanism, the two locks will continue to wait.
SQL server checks deadlocks on a regular basis. If deadlocks are detected, the two items will be weighed and one of the items with a low execution cost will be sacrificed so that the other can continue to be executed.

To avoid deadlocks, pay attention to them, such
1. Keep things as short as possible.
2. The update sequence of things should be as consistent as possible. In the preceding example, if the update sequence of A and B is T1, T2, or T2, no deadlock will occur for T1.
3. You can modify the lock granularity. For example, you can change the page lock to a row lock.