Ali P8 boss will take you to learn Java lock in 3 minutes

Ali P8 boss will take you to learn Java lock in 3 minutes

This article is transferred from: Le byte article
Main explanation: Java lock
For more Java related knowledge, you can follow the public account "Le Byte" Send: 999

1 Introduction

Large concurrent operations of the database should consider the performance issues of deadlocks and locks. I see that most of the language on the Internet is unclear (especially update locks), so here is a concise explanation, for the convenience of the following description, here T1 represents a database execution request, T2 represents another request, it can also be understood that T1 is a thread, T2 is another One thread. T3, T4 and so on.

2 Types of locks

  1. Shared lock.

    example 1: ---------------------------------------- T1: select * from table (please imagine it needs to be executed for 1 hour, please imagine the following SQL statements) T2: update table set column1='hello' process: T1 operation (plus shared lock) T2 operation If T1 has not been executed yet T2 etc... else Lock is released T2 execution endif The reason why T2 has to wait is because T2 tries to add an exclusive lock to the table before executing the update. The database stipulates that shared locks and exclusive locks cannot coexist on the same resource at the same time. So T2 must wait for T1 After the execution, the shared lock is released, the exclusive lock can be added, and then the update statement can be executed. Example 2: ---------------------------------------- T1: select * from table T2: select * from table Here T2 does not need to wait for T1 to finish executing, but can be executed immediately. analysis: T1 is running, the table is locked, such as lockA T2 runs, and then adds a shared lock to the table, such as lockB. Two locks can exist on the same resource at the same time (for example, on the same table). This is called a total Shared locks are compatible with shared locks. This means that the shared lock does not prevent other sessions from reading resources at the same time, but it prevents Stop other session update Example 3: ---------------------------------------- T1: select * from table T2: select * from table T3: update table set column1='hello' This time, T2 can run without waiting for T1 to run, but T3 can run after both T1 and T2 are run. Because T3 must wait for the shared locks of T1 and T2 to be released before it can add exclusive locks and then execute update operating. Example 4: (The occurrence of deadlock) ---------------------------------------- T1: begin tran select * from table (holdlock) (holdlock means to add a shared lock, until the end of the transaction is not released) update table set column1='hello' T2: begin tran select * from table(holdlock) update table set column1='world' Assuming that T1 and T2 reach select at the same time, T1 adds a shared lock to the table, and T2 also adds a shared lock. When When T1's select is executed and ready to execute update, according to the lock mechanism, T1's shared lock needs to be upgraded Level to the exclusive lock to perform the next update. Before upgrading the exclusive lock, you must wait for the Other shared locks are released, but because shared locks such as holdlock are released only after the transaction ends, Therefore, because T2's shared lock is not released, T1 and so on (wait for T2 to release the shared lock, so you can upgrade to a row by yourself) He lock), similarly, T2 and so on are caused because the shared lock of T1 is not released. The deadlock occurred. Example 5: ---------------------------------------- T1: begin tran update table set column1='hello' where id=10 T2: begin tran update table set column1='world' where id=20 Although this kind of statement is the most common, many people think it has a chance to cause deadlock, but it actually depends on the situation. In addition, if id is an index on the primary key, then T1 will find the record (with id=10). Record), and then add an exclusive lock to the record, T2, similarly, locate the record through the index at once, Then add an exclusive lock to the record with id=20, so that T1 and T2 are updated separately, without affecting each other. Nor T2 Need to wait. But if id is a normal column, there is no index. Then when T1 adds an exclusive lock to the row id=10, In order to find id=20, T2 needs to scan the entire table, then it will add a shared lock or update to the table in advance Lock or exclusive lock (depending on the database execution strategy and method, such as the first execution and the second execution The database execution strategy will be different). But because T1 has already added an exclusive lock to a record, resulting in T2's full table scan cannot go on, causing T2 to wait. How to solve the deadlock? One way is as follows: Example 6: ---------------------------------------- T1: begin tran select * from table(xlock) (xlock means to directly add an exclusive lock to the table) update table set column1='hello' T2: begin tran select * from table(xlock) update table set column1='world' In this way, when T1's select is executed, an exclusive lock is directly added to the table. When T2 is executing select, it needs to wait for the T1 transaction to be executed completely. The occurrence of deadlock is ruled out. But when the third user came over and wanted to execute a query statement, he had to wait because of the existence of the exclusive lock, and the fourth and fifth users would also wait because of this. In big concurrency In this case, it is too friendly to let everyone wait, so the update lock is introduced here. Copy code
  2. Update lock

    To solve the deadlock, an update lock is introduced. Example 7: ---------------------------------------- T1: begin tran select * from table(updlock) (add update lock) update table set column1='hello' T2: begin tran select * from table(updlock) update table set column1='world' The update lock means: "I only want to read now, you others can also read, but I may do update operations in the future, I have acquired from a shared lock (for reading) to an exclusive lock (Used to update)". Only one update lock for a thing can obtain this qualification. T1 executes select and adds update lock. T2 was running and was about to add an update lock, but found that there was already an update lock there, so I had to wait. When user3, user4... need to query the data in the table table later, it will not be blocked because the select of T1 is executed, and it can still be queried. Compared with example 6, this improves Efficiency. Example 8: ---------------------------------------- T1: select * from table(updlock) (plus update lock) T2: select * from table(updlock) (wait until T1 releases the update lock, because there cannot be two update locks on the same resource at the same time) T3: select * from table (add a shared lock, but you can read it without waiting for the updlock to be released) This example is to illustrate: the shared lock and update lock can be on the same resource at the same time. This is called shared lock and update lock is compatible. Example 9: ---------------------------------------- T1: begin select * from table(updlock) (plus update lock) update table set column1='hello' (Important: when T1 is doing update, there is no need to wait for T2 to release anything, but directly upgrade the update lock to an exclusive lock, and then execute the update) T2: begin select * from table (The update lock added by T1 does not affect the reading of T2) update table set column1='world' (The update of T2 needs to be executed after the update of T1 is completed) We use this example to deepen our understanding of update locks, The first case: T1 arrives first, and T2 arrives immediately; in this case, T1 first adds an update lock to the table, and T2 adds a shared lock to the table. Assuming that the select of T2 is executed first, it is ready to execute the update. Found that there is an update lock, T2, etc. T1 executes only then executes select, prepares to execute update, upgrades the update lock to an exclusive lock, then executes update, the execution is complete, the transaction At the end, release the lock, T2's turn to execute update. The second case: T2 arrives first, and T1 arrives immediately; in this case, T2 first adds a shared lock to the table, and after T1 reaches, T1 adds an update lock to the table. Assuming that T2 select ends first, prepare update, find that there is an update lock, then wait, the following steps are the same as the first case. This example shows that exclusive locks and update locks are incompatible, and they cannot be added to the same sub-resource at the same time. Copy code
  3. Exclusive locks (Exclusive Locks)

    This is simple, that is, other transactions can neither read nor change the resources locked by the exclusive lock. Example 10 T1: update table set column1='hello' where id<1000 T2: update table set column1='world' where id>1000 Suppose that T1 arrives first, and T2 arrives later. In this process, T1 will apply an exclusive lock to the record with id<1000, but it will not block the update of T2. Example 11 (Assuming that ids are self-increasing and continuous) T1: update table set column1='hello' where id<1000 T2: update table set column1='world' where id>900 As in Example 10, T1 arrives first, and T2 arrives immediately. The exclusive lock added by T1 will block the update of T2. Copy code
  4. Intent Locks

    Intentional lock means to set a sign at the door of a house (for example, representing a watch), indicating that someone in the house (for example, representing certain records) is locked. Another person wants to know the house There is no need to enter the house to check whether anyone is locked, just look at the sign at the door. When an exclusive lock is added to a row in a table, the table can no longer be locked by the table. How does the database program know that the table cannot be locked? One way is to judge the Whether each record of the table has an exclusive lock, another way is to directly check whether the table itself has an intentional lock at the table level, without judging one by one. Obviously the latter is highly efficient. Example 12: ---------------------------------------- T1: begin tran select * from table (xlock) where id=10 - means to impose an exclusive lock on the row id=10 T2: begin tran select * from table (tablock)-means to add table-level lock Suppose that T1 is executed first, and T2 is executed later. When T2 is executed, a table lock is to be added. In order to determine whether the table lock can be added, the database system must determine whether each row of the table table has an exclusive lock. If one of the rows is found to have an exclusive lock, no more table locks are allowed. It's just that the efficiency of judging item by item like this is too low. In fact, the database system does not work like this. When the select of T1 is executed, the system adds an exclusive lock to the row of table id=10, and it also quietly locks the entire table at the same time With the addition of an intentional exclusive lock (IX), when T2 executes a table lock, it only needs to see that the table has an intentional exclusive lock and wait directly without checking resources one by one. Example 13: ---------------------------------------- T1: begin tran update table set column1='hello' where id=1 T2: begin tran update table set column1='world' where id=1 This example has the same actual effect as the above example. T1 is executed. The system simultaneously locks the expert exclusive lock, adds the intention exclusive lock to the page, and adds the intention exclusive lock to the table at the same time. Copy code
  5. Schema Locks

    Example 14: ---------------------------------------- alter table .... (Add schema locks, called Schema modification (Sch-M) locks DDL statement will add Sch-M lock The lock does not allow any other session to connect to the table. I can't even connect to this table, let alone what SQL statement I want to execute on the table. Example 15: ---------------------------------------- Use jdbc to send a new SQL statement to the database, the database must first compile it, and during compilation, it will also lock, which is called: Schema stability (Sch-S) locks select * from tableA In the process of compiling this statement, other sessions can do any operation on tableA (update, delete, add exclusive lock, etc.), but cannot do DDL (such as alter table) operations. Copy code
  6. Bulk Update Locks is mainly used when importing data in batches (for example, using the bcp command similar to imp/exp in Oracle). It's not difficult to understand, programmers often don't need to care, so I won't go into details.

3 When to lock?

You can manually specify how to lock, when to lock, and what lock to add, but most of them are automatically determined by the database system. That s why we don t know how to lock Write SQL happily. Example 15: ---------------------------------------- T1: begin tran update table set column1='hello' where id=1 T2: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED-transaction isolation level is to allow dirty read go select * from table where id=1 Here, the select of T2 can find out the result. If the transaction isolation level is not set to dirty read, T2 will wait for the T1 transaction to finish executing before reading the result. How to automatically lock the database? 1) T1 execution, the database automatically adds exclusive lock 2) T2 execution, the database found that the transaction isolation level allows dirty reads, so no shared locks are added. Without a shared lock, it will not conflict with an existing exclusive lock, so dirty reads can be made. Example 16: ---------------------------------------- T1: begin tran update table set column1='hello' where id=1 T2: select * from table where id=1-to specify the isolation level, use the system default isolation level, which does not allow dirty read If the transaction level is not set to dirty read, then: 1) T1 execution, the database automatically adds exclusive lock 2) T2 execution, the database found that the transaction isolation level does not allow dirty reads, it is ready to add a shared lock for this select process, but found that it can not be added, because there is an exclusive lock, so Wait and wait. Until T1 is executed and the exclusive lock is released, T2 adds the shared lock, and then starts to read... Copy code

4 Lock granularity

The lock granularity refers to the effective scope of the lock, that is, row lock, page lock, or entire table lock. The lock granularity can also be managed automatically by the database, or managed by manually specifying hints.

Example 17: ---------------------------------------- T1: select * from table (paglock) T2: update table set column1='hello' where id>10 When T1 is executed, the first page is locked, after reading the first page, the lock is released, and then the second page is locked, and so on. Assuming that the first 10 rows of records happen to be one page (of course, it is generally impossible There are only 10 rows of records on a page), then when T1 executes the query to the first page, it will not block the update of T2. Example 18: ---------------------------------------- T1: select * from table (rowlock) T2: update table set column1='hello' where id=10 When T1 is executed, a shared lock is added to each row, read, and then released, and then the next row is locked; when T2 is executed, it will try to lock the row with id=10, as long as the row is not locked by T1. T2 can perform the update operation smoothly. Example 19: ---------------------------------------- T1: select * from table (tablock) T2: update table set column1='hello' where id = 10 T1 executes, adding a shared lock to the entire table. T1 must be completely queried before T2 can allow locks and start updating. The above three examples are manually specifying the lock granularity. You can also set the transaction isolation level to let the database automatically set the lock granularity. Different transaction isolation levels, the database will be different Locking strategy (such as what type of lock to add, and what granular lock to add). Please check the online manual for details. Copy code

5 Priority of lock and transaction isolation level

Manually specified locks take precedence, Example 20: ---------------------------------------- T1: GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION SELECT * FROM table (NOLOCK) GO T2: update table set column1='hello' where id=10 T1 is the highest transaction isolation level, serial lock, the database system should automatically add table-level locks to the subsequent select statement, but because NOLOCK is manually specified, the select The statement will not add any locks, so T2 will not have any blocking. Copy code

6 Other important Hints of the database and their differences

1) holdlock adds a shared lock to the table, and the transaction is not completed, the shared lock is not released. 2) Tablock adds a shared lock to the table. As long as the statement is not completed, the shared lock will not be released. The difference with holdlock, see the following example: Example 21 ---------------------------------------- T1: begin tran select * from table (tablock) T2: begin tran update table set column1='hello' where id = 10 After T1 executes select, the shared lock will be released, and then T2 can execute update. This is called tablock. Let s look at holdlock. Example 22 ---------------------------------------- T1: begin tran select * from table (holdlock) T2: begin tran update table set column1='hello' where id = 10 After T1 executes select, the shared lock will still not be released and will still be held. Therefore, T2 must wait and cannot be updated. When T1 finally executes commit or Rollback shows that this thing is over, T2 has the right to execute. 3) TABLOCKX adds exclusive lock to the table Example 23: ---------------------------------------- T1: select * from table(tablockx) (forcing exclusive lock) Other sessions cannot read and update this table, unless T1 is executed, the exclusive lock will be automatically released. Example 24: ---------------------------------------- T1: begin tran select * from table(tablockx) This time, select alone is not enough, the entire thing must be completed (after the commit or rollback is executed) before the exclusive lock is released. 4) xlock plus exclusive lock How is it different from tabockx? It can be used like this, Example 25: ---------------------------------------- select * from table(xlock paglock) add exclusive lock to page And TABLELOCX cannot be used in this way. xlock can also be used like this: select * from table(xlock tablock) is equivalent to select * from table(tablockx) Copy code

7 Lock timeout waiting

Example 26

SET LOCK_TIMEOUT 4000 is used to set the lock waiting time, the unit is milliseconds, 4000 means waiting You can use select @@LOCK_TIMEOUT to view the lock timeout setting of the current session in 4 seconds. -1 means Wait forever. T1: begin tran udpate table set column1='hello' where id = 10 T2: set lock_timeout 4000 select * from table wehre id = 10 Copy code

When T2 is executed, it will wait for T1 to release the exclusive lock. After 4 seconds, if T1 has not released the exclusive lock, T2 will throw an exception: Lock request time out period exceeded.

8 Attachment: Compatibility table of 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 | Copy code

9 How to improve concurrency efficiency

  1. Pessimistic lock: use the lock mechanism of the database itself to achieve. Through the above understanding of database locks, you can use transaction isolation level and reasonable manual designation of locks according to specific business conditions, such as reducing the granularity of locks, etc. to reduce concurrent waiting.
  2. Optimistic locking: Use programs to handle concurrency. The principles are relatively easy to understand, and they are basically understood at a glance. There are about 3 ways
    1. Add the version number to the record.
    2. Time stamp the record.
    3. The data to be updated is read in advance and compared afterwards.

Whether it is the lock mechanism of the database system itself or the lock mechanism at the business data level such as optimistic lock, it is essentially the reading, writing, and judgment of status bits.

Thank you for your approval and support, the editor will continue to forward high-quality articles of "LeByte"