Database lock mechanism

1 Preface

The database large concurrency operation takes into account the deadlock and lock performance issues. See online most vague (especially update lock), so here is a concise explanation, for the following description of the convenient, here with T1 for a database execution request, T2 represents another request, can also be understood as T1 a thread, T2 for another thread. T3,t4 and so on. The following is an example of SQL Server (2005).

2 kinds of lock

1. Shared lock.

   Example 1:----------------------------------------T1:select * FROM table (imagine that it takes 1 hours to execute, so imagine the SQL statements that follow) T2:update table S Et column1= ' hello ' procedure: T1 run (plus shared lock) T2 run if T1 not finished T2 etc.. else Lock is released T2 the reason for the endifT2 is to wait, because T2 attempted to add a row to table tables before performing the update He locks, and the database stipulates that shared and exclusive locks cannot coexist concurrently on the same resource. So T2 must wait for the T1 to execute, release the shared lock, and then add an exclusive lock before the UPDATE statement can start executing. Example 2:----------------------------------------t1:select * from Tablet2:select * FROM table here T2 does not have to wait for T1 to finish, but can execute immediately. Analysis: T1 run, the table is locked, such as called LockAT2 Run, and then add a shared lock on the table, such as called LOCKB. Two locks can exist simultaneously on the same resource (as compared to a table). This is known as shared lock compatibility with shared locks. This means that the shared lock does not prevent other sessions from reading resources at the same time, but blocks other session update Example 3:----------------------------------------T1:select * from Tablet2:se Lect * from tablet3:update table set column1= ' Hello ' this time, T2 do not have to wait for T1 to run, T3 to wait for T1 and T2 to run after running. Because the T3 must wait for the shared lock of T1 and T2 to be released for exclusive locks and then perform the update operation. Example 4: (the occurrence of deadlocks)----------------------------------------t1:begin transelect * from table (holdlock) (holdlock means a shared lock, Until the end of the thing is released) Update table set column1= ' Hello ' t2:begin transelect * from table (HOLDLOCK) Update table set CoLumn1= ' World ' assumes that T1 and T2 simultaneously achieve select,t1 to the table plus shared lock, T2 also to the shared lock, when the T1 select executes, ready to perform the update, according to the lock mechanism, The T1 shared lock needs to be upgraded to an exclusive lock to perform the next update. Before an exclusive lock is upgraded, the other shared locks on the table must be released, but because shared locks such as Holdlock are released only after the transaction ends, because the T2 shared lock is not released and causes T1, and so on ( such as T2 release shared lock, oneself good upgrade to exclusive lock, similarly, also because T1 share lock does not release and lead to T2 and so on. A deadlock has been created. Example 5:----------------------------------------t1:begin tranupdate table set column1= ' Hello ' where id=10t2:begin TranUpdate table set column1= ' World ' where id=20 this kind of statement is most common, many people think it has the opportunity to create a deadlock, but actually depends on the situation, if the ID is the primary key above the index, then T1 will find the record (id= 10 of the record), and then to the record plus an exclusive lock, T2, the same, all of a sudden through the index to the record, and then the id=20 record plus exclusive locks, so T1 and T2 each update each other, and not affect each other. T2 also need not wait. But if the ID is a normal column, there is no index. Then when T1 to id=10 this line of exclusive lock, T2 in order to find id=20, need to scan the full table, then the table will be pre-shared lock or update lock or exclusive lock (dependent on the database execution policies and methods, such as the first execution and the second execution of the database execution policy will be different). However, because T1 has added an exclusive lock for a record, causing T2 's full table scan to continue, it causes T2 to wait. How does a deadlock work? One approach is as follows: Example 6:----------------------------------------t1:begin transelect * from table (Xlock) (xlock means to add exclusive locks directly to the table) Update table set column1= ' Hello ' t2:begin transelect * from table (xlock) Update table set column1= ' world ' so that when the T1 select executes, By adding an exclusive lock to the table, T2 needs to wait for the T1 to execute when the select is executed. A deadlock has been ruled out. But when the third user comes to thinkWhen executing a query statement, you also have to wait for an exclusive lock to exist, and the fourth and fifth user will wait for it. In the case of large concurrency, it is too friendly to let everyone wait for the performance, so the update lock is introduced here.

2. Updating locks (update lock)

   To resolve deadlocks, an update lock is introduced. Example 7:----------------------------------------t1:begin transelect * from table (updlock) (plus update lock) Update table set column1= ' Hello ' t2:begin transelect * from table (updlock) Updated table set column1= ' World ' update lock means: "I just want to read it now, you can read it, but I'll probably do the update in the future, I have acquired the qualification from a shared lock (used to read) to an exclusive lock (to update). A thing can only have one update lock to qualify for this. T1 executes SELECT, plus update lock. T2 run, ready to add update lock, but found that there is already an update lock there, had to wait. When there are User3, user4 ... When querying data in table tables, it is not possible to query because the T1 select is blocked on execution, which improves efficiency compared to example 6. Example 8:----------------------------------------T1:select * from table (updlock) (plus update lock) T2:select * FROM table (updlock ) (wait until T1 releases the update lock, because the same time cannot have two update locks on the same resource) T3:select * from table (plus shared lock, but not waiting for Updlock to be released, you can read) This example is a description: Shared and update locks can be on the same resource simultaneously. This is known as shared locks and update locks are compatible. Example 9:----------------------------------------T1:beginselect * from table (updlock) (plus update lock) Update table set column1= ' Hello ' (Focus: Here T1 do update, do not need to wait for T2 release what, but directly to upgrade the update lock to exclusive lock, and then perform the update) T2:beginselect * FROM table (T1 Plus update lock does not affect T2 read) update Table set column1= ' World ' (T2 update needs to wait for the T1 update to finish before it can be executed) we use this example to deepen the understanding of the update lock, the first case: T1, T2 immediately arrived; In this case, T1 first to the table plus update the lock, T2 to the table plus shared lock, assuming that T2 's select executes first, ready to perform the update, found that there is an update lock exists, T2 and so on. T1 execution then executes the Select, prepares to perform the update, updates the lock to an exclusive lock, then executes the update, completes the transaction, releases the lock, and the T2 turns to perform the update. The second situation: T2 first, T1 immediately up; In this case, T2 to the table plus shared lock, T1 up, T1 on the table plus update lock, assuming that the T2 select first end, ready to update, found that there is an update lock, then wait, the next step is the same as the first case. This example shows that an exclusive lock is incompatible with an update lock, and they cannot be added to the same child resource at the same time.

4. Exclusive lock (exclusive lock, Exclusive Locks)

    This simple, that is, other transactions can not read, but also can not change the exclusive lock lock resources. Example 10t1:update table set column1= ' Hello ' where id<1000t2:update table set column1= ' World ' where id>1000 assumes T1, T2 Subsequently, this process T1 an exclusive lock on the id<1000 record. But the T2 update will not be blocked. Example 11 (assuming IDs are self-growing and continuous) t1:update table set column1= ' Hello ' where id<1000t2:update table set column1= ' World ' where I d>900 as an example 10,T1, T2 immediately, T1 plus the exclusive lock will block the T2 update. 

6. intent Lock (Intent Locks)

    An intent lock is a sign at the door of a house (for example, a table) that indicates that someone in the room (for example, some record) is locked. Another person wants to know if someone in the house is locked, do not go into one of the house to check, look directly at the door logo on the line. When a row in a table is added to an exclusive lock, the table can no longer be added to the table lock. How does the database program know that the table cannot be added to the table lock? One way is to determine whether each record of the table has an exclusive lock, and the other way is to check whether the table itself is intentionally locked at the table level, and does not need to be judged. Obviously the latter is highly efficient. Example:----------------------------------------t1:begin Tran SELECT * FROM table (Xlock) where id=10-meaning id=10 this Row imposes an exclusive lock T2:begin Tran SELECT * FROM table (TABLOCK)-meaning to add a table-level lock assuming T1 first execution, T2 after execution, T2 execution, to add a table lock, in order to determine whether the table lock can be added, the database system To determine each row of table tables is an exclusive lock, if it is found that one row has an exclusive lock, it is not allowed to add a table lock. It's just that the efficiency is too low. In fact, the database system does not work this way. When T1 's select executes, the system adds an exclusive lock to the table's id=10 line, and also silently adds an intent exclusive lock (IX) to the entire table, and when T2 executes the table lock, only to see that the table has an intent exclusive lock exists, it waits directly, without needing to check the resources. Example:----------------------------------------t1:begin Tran Update table set column1= ' Hello ' where id=1t2:begi N Tran Update table set column1= ' World ' where id=1 This example is the same as the example above, T1 execution, the system on the table at the same time on the expert exclusive lock, the page plus intent exclusive lock, the table plus intent exclusive lock. 

8. Program Lock (Schema Locks)

   Example:----------------------------------------ALTER TABLE .... (Add schema locks, called schema Modification (SCH-M) LOCKSDDL statement will add SCH-M lock this lock does not allow any other session to connect to the table. I can't even get to the table, not to mention what SQL statements to execute on the table. Example:----------------------------------------use JDBC to send a new SQL statement to the database, the database must be compiled first, during compilation, also lock, called: Schema stability ( SCH-S) Locksselect * FROM TableA compiling this statement, other sessions can do anything to the table TableA (Update,delete, add exclusive locks, etc.), but cannot do DDL (such as ALTER TABLE) operations.

10. The Bulk Update Locks is primarily used in bulk-conducting data (for example, with a bcp command similar to IMP/EXP in Oracle). Not difficult to understand, programmers often do not need to care, do not repeat.

3 when to lock?

How to lock, when to lock, plus what locks, you can manually forcibly specified by hint, but most of the database system is automatically determined. That's why we can write SQL happily without a lock. Example:----------------------------------------T1:    begin TRAN       Update table set column1= ' Hello ' where id=1t2:    SET TRANSACTION Isolation LEVEL READ UNCOMMITTED  --things isolated to allow dirty read       go       select * FROM table where id=1 here, T2 s Elect can find out the results. If the thing isolation level is not set to dirty read, T2 will wait until the T1 thing executes to read the result. How does the database automatically lock? 1) T1 execution, database Auto plus exclusive lock 2) T2 execution, the database discovers that the object isolation level allows dirty reads, without sharing locks. Without a shared lock, there is no conflict with an existing exclusive lock, so it can be dirty read. Example:----------------------------------------T1:    begin TRAN       Update table set column1= ' Hello ' where id=1t2:    select * FROM table where id=1--for the specified isolation level, the system default isolation level is used, which does not allow dirty reads if the thing level is not set to dirty read: 1) T1 execution, the database automatically plus exclusive lock 2) T2 execution, the database found that the isolation level of things do not allow dirty read, it is ready to add a shared lock for the select process, but found not add, because there has been an exclusive lock, so   wait ah. Until T1 executes, the exclusive lock is released, the T2 is added with the shared lock, and then begins to read ....

4 size of the lock

The granularity of the lock is the scope of the lock, that is, a row lock, or a page lock, or an entire table lock. The granularity of locks can be managed either automatically by the database or by manually specifying hint.

Example:----------------------------------------T1:    select * FROM table (paglock) T2:    Update table Set column1= ' Hello ' where id>10t1 executes, locks the first page first, releases the lock after reading the first page, locks the second page, and so on. Assuming that the first 10 rows of records happen to be one page (of course, it is generally impossible to have only 10 rows of records on a page), T1 does not block updates to the T2 when it executes to the first page of the query. Example:----------------------------------------T1:    select * FROM table (rowlock) T2:    Update table Set column1= ' Hello ' where Id=10t1 executes, the shared lock is added to each row, read, then released, and then locked on the next line; When the T2 executes, it attempts to lock the id=10 line, as long as the row is not T1 plus a row lock, T2 can perform the update operation smoothly. Example:----------------------------------------T1:    select * FROM table (TABLOCK) T2:    Update table Set column1= ' Hello ' where id = 10t1 execution, plus shared lock on entire table. T1 must complete the query, T2 can only allow lock, and start updating. The above 3 cases are manually specified the granularity of the lock, you can also set the level of things isolation, let the database automatically sets the granularity of the lock. Different levels of things isolation, the database will have different locking policies (such as what type of lock, plus what granularity of the lock). Please check the online manual for details.

5 Priority of lock and thing isolation level

Manually specified lock First, example:----------------------------------------T1:    GO       SET TRANSACTION Isolation Level SERIALIZABLE       GO       BEGIN TRANSACTION       SELECT * from table (NOLOCK)       GOT2:    Update table set column1= ' Hello ' where id= 10T1 is the highest level of things isolation, serial locks, the database system should automatically add a table-level lock to the next SELECT statement, but because the NOLOCK is manually specified, the SELECT statement does not have any locks, so T2 will not have any blocking.

6 Other important hint of the database and their differences

1) holdlock sharing lock on the table, and the thing is not complete, the shared lock is not released.   2) tablock to the table plus shared lock, as long as the statement does not complete, the shared lock is not released.    In contrast to Holdlock, see the following example: Example----------------------------------------t1:begin Tran select * FROM table (TABLOCK) T2: BEGIN TRAN Update table set column1= ' Hello ' where id = T1 Executes the SELECT, the shared lock is released, and the T2 can perform the update. This is called Tablock.   Let's look at Holdlock example----------------------------------------t1:begin Tran select * FROM table (holdlock) T2: BEGIN TRAN Update table set column1= ' Hello ' where id = ten T1 executes the Select, the shared lock will still not be released, will still be held, and T2 must wait for it to update.  T1 finally executed a commit or rollback stating that the thing was over, and that T2 had the right to execute. 3) Tablockx to table plus exclusive lock example:----------------------------------------t1:select * from table (Tablockx) (forcibly plus exclusive lock) its   It will not be able to read and update the table, unless T1 is executed, the exclusive lock is automatically released. Example:----------------------------------------t1:begin Tran SELECT * FROM table (TABLOCKX) This time, select only executes  Not yet, the exclusive lock must be released only after the whole thing has been done (after a commit or rollback is executed).   4) Xlock plus exclusive lock What difference does it have with Tablockx? It can be used in this way, example:----------------------------------------SELECT * FROM table (Xlock Paglock) adds an exclusive lock to the page and Tablelocx cannot do so. Xlock can also be used in this way: the SELECT * FROM table (Xlock tablock) effect is identical to the SELECT * from table (TABLOCKX)

7 timeout wait for lock

Example 26

The set LOCK_TIMEOUT 4000 is used to set the lock wait time, in milliseconds, 4000 means waiting for 4 seconds to see the lock timeout setting for the current session with the SELECT @ @LOCK_TIMEOUT. -1 means to wait forever. T1:begin Tran    udpate table set column1= ' Hello ' where id = 10t2:set lock_timeout 4000    select * FROM table Wehre I D = 10

When T2 executes, it waits for the T1 to release an exclusive lock for 4 seconds, and if T1 has not released an exclusive lock, T2 throws an exception: Lock request time out period exceeded.

8 attached: Compatible relationship table for various locks

| Requested Mode                     | is  | S   | U   | IX  | SIX | X  | | Intent Shared (IS)                 | Yes | Yes | Yes | Yes | Yes | No | | Shared (S)                         | Yes | Yes | Yes | No  | No  | No | | Update (U)                         | Yes | Yes | No  | No  | No  | No | | Intent Exclusive (IX)              | Yes | No  | No  | Yes | No  | No | | Shared with Intent Exclusive (SIX) | Yes | No  | No  | No  | No  | No | | Exclusive (X)                      | No  | No  | No  | No  | No  | No |

9 How to improve concurrency efficiency

1. Pessimistic Lock: Use the lock mechanism of the database itself to implement. Through the understanding of database lock, it is possible to use transaction isolation level in accordance with the specific business situation and to reduce the concurrency wait by manually specifying the locking method such as reducing the granularity of the lock.
2. Optimistic Lock: Use the program to handle concurrency. Principles are relatively good understanding, the basic one can understand. There are about 3 ways of doing this
   1. Adds a version number to the record.
   2. Timestamp the record.
   3. Pre-read and contrast the data that will be updated.

Whether it is the lock mechanism of the database system itself or the lock mechanism on the business data level of optimistic locking, it is essentially the reading, writing and judging of the state bits.

Database lock mechanism