If you feel you have gained something, please click a “like” to share the useful knowledge with more people
Why introduce database locking
A situation in which multiple threads in a database concurrently execute transactions to access the same data. Uncontrolled concurrent operations can read and store incorrect data, breaking database consistency (dirty reads, unrepeatable reads, phantom reads, and so on).
In order to solve this problem, locking is a very important technology to achieve database concurrency control is a good scheme. Simply put, when a transaction that executes an SQL statement wants to manipulate a table record, it requests the database to lock the recordset that you access. No other transaction can update the data until the transaction releases the lock.
The lock type
Locking mechanism
Optimistic locking
Read more and write less. At the application level.
Check if the version number or timestamp matches when submitting the update. In the data read scenario, the corresponding data version is read, and the corresponding data version is upgraded in the data write operation. The system reads data, obtains the original version of the data, and then performs the write operation. The obtained version is compared with the current version of the data in the database. If the version is the same, the system performs the write operation (Update statement The database system locks the data by default. Otherwise, query the results again, and retry the write operation until it is complete.
Pessimistic locking
Write concurrency high scenario. The database itself.
Table locks, row locks, etc. After locking, other transactions cannot be queried or updated. Such as the select… from table for update;
Locking granularity
When we use Mysql, we usually use InnoDB storage engine. There are two essential differences between InnoDB and MyISAM:
- InnoDB supports row locking
- InnoDB supports transactions
Different storage engines support different lock granularity:
- InnoDB row and table locks are supported!
- MyISAM only supports table locks!
InnoDB also has two types of Intention Locks for internal use, both of which are table Locks:
- Intended shared lock (IS) : a transaction that intends to assign a shared lock to a row must acquire an IS lock on that table before assigning a shared lock to a row.
- Intentional exclusive lock (IX) : a transaction that intends to lock a row exclusively must acquire an IX lock on the table before it can lock a row exclusively.
Row locks
High overhead, slow lock; Deadlocks occur; The lock granularity is small, the probability of lock conflict is low, and the concurrency is high.
InnoDB uses row lock by default and implements two standard row locks — shared lock and exclusive lock.
Note:
1. In addition to explicit locking, locking and unlocking are performed without manual intervention in other cases.
2. All of InnoDB’s row locking algorithms are based on indexes and lock indexes or index intervals;
InnoDB locks rows by locking index entries, unlike Oracle, which locks rows in data blocks. InnoDB’s row-locking implementation means that InnoDB uses row-locking only when data is retrieved by index criteria. Otherwise, InnoDB uses table locking!
The disadvantages of row-level locking are that it is slow and memory consuming because a large number of lock resources are requested, and it may cause a large number of lock conflicts, affecting concurrency performance.
Innodb row lock implementation – lock index, not records
InnoDB storage engine row locking is implemented by locking indexes, not records. This is the key to understanding many database locking problems.
Due to InnoDB’s special indexing mechanism, InnoDB locks primary key indexes when database operations use primary key indexes. When InnoDB uses a non-primary key index, InnoDB locks the non-primary key index first and then the primary key index.
As shown in the figure below, when InnoDB locks non-primary key index B, it also locks its corresponding primary key index, so if InnoDB locks non-primary key index B with b value 2 and 3, then its associated primary key index A with a value 6 and 5 also needs to be locked.
Database locking mechanism
For example, an index has the values 10,11,13,20. InnoDB can use Record Lock on 10, 11,13,20 as needed, and Gap Lock on (-∞,10), (10,11), (11,13), (13,20), (20, +∞). Next-key Locking is similar to the combination of the above two kinds of locks. It can lock the interval for (-∞,10], (10,11], (11,13], (13,20], (20, +∞). It can be seen that it not only locks a range, but also locks the record itself (open on the left and close on the right).
- InnoDB automatically locks the modification operation, but not the query operation.
- Gap locking is not required when a statement uses a unique index to search for a unique row. If the following statement has a unique index for the ID column, only the row with id value 10 will be locked.
- InnoDB uses indexes to lock rows, not records. Therefore, when two different records of an operation have the same index, the wait also occurs because the row lock is locked.
- Due to InnoDB’s indexing mechanism, database operations use the primary key index, InnoDB locks the primary key index; When InnoDB uses a non-primary key index, InnoDB locks the non-primary key index first and then the primary key index.
- When the index of the query is a unique index (no two rows have exactly the same Key value), InnoDB storage engine degrades next-key Lock to Record Lock, which locks only the index itself, not the range.
- InnoDB has special treatment for secondary indexes. It not only locks the range of secondary indexes, but also applies a Gap Lock to the value of the next key.
- InnoDB uses a next-key Lock mechanism to avoid Phantom problems.
Equivalent query, and go index
For primary keys or unique indexes, if all the WHERE conditions hit exactly (= or in) in the SELECT query, the scenario itself will not be phantom, so only row locks will be added.
Range query, and go index
When we retrieve data using a range condition rather than an equality condition and request a shared or exclusive lock, InnoDB locks the index entries of existing data records that meet the condition. For records whose Key values are within the condition range but do not exist, called gaps, InnoDB also locks this GAP. This locking mechanism is called next-key locking.
For example, if there are only 101 records in the emp table, the empid values are 1,2… SQL > alter table SQL > alter table SQL
Select * from emp where empid > 100 for update;
InnoDB locks not only qualified records with an EMPID value of 101, but also “gaps” where empID is greater than 101 (these records do not exist).
Apparently, InnoDB’s locking mechanism blocks concurrent insertions of key values within the qualifying range when using range criteria to retrieve and lock records, often resulting in severe lock waits. Therefore, in practical application development, especially for applications with a large number of concurrent inserts, we should try to optimize business logic, try to use equality conditions to access updated data, and avoid using scope conditions.
In addition to using gap locks when using range conditions, InnoDB also uses gap locks when using equality conditions to request a non-existent record!
No index walk
Even if index fields are used in conditions, it is up to MySQL to decide whether to use indexes to retrieve data by determining the cost of different execution plans. If MySQL decides that a full table scan is more efficient, such as for some small tables, it will not use indexes. In this case InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don’t forget to check the EXECUTION plan of your SQL to see if indexes are actually being used.
compatibility
Shared lock (read lock)
A read lock, also known as a shared lock, allows one transaction to read a row, preventing other transactions from acquiring an exclusive lock on the same data set.
After transaction A adds A read lock to A certain data, transaction B can also obtain the read lock and read data if it initiates A read operation on the data. If transaction B initiates a write operation on the data, the exclusive lock of the data cannot be obtained. Therefore, the write operation cannot be performed. The write lock can be obtained only after all the read locks are released.
Exclusive lock (write lock)
A write lock, also known as an exclusive lock, allows a transaction to acquire the update data of an exclusive lock, preventing other transactions from acquiring shared and exclusive locks of the same data set.
A deadlock
Deadlock, is to produce a loop waiting chain, I wait for your resources, you wait for my resources, we all wait for each other, no one release their own occupied resources, resulting in infinite waiting.
Cause one: Lock sequence
For example, a common deadlock situation occurs in an operation scenario like the one shown below.
The statement in session 1 uses the index on B, because it is a non-primary key index, so the lock is added on index B first and then on index A. Session 2, on the other hand, locks index A and then index B. In this case, deadlocks can occur.
Cause two: Lock sequence
Such as:
//Session A START TRANSACTION; UPDATE account SET p_money=p_money-100 WHERE p_name="tim"; UPDATE account SET p_money=p_money+100 WHERE p_name="bill"; COMMIT; //Thread B START TRANSACTION; UPDATE account SET p_money=p_money+100 WHERE p_name="bill"; UPDATE account SET p_money=p_money-100 WHERE p_name="tim"; COMMIT; Copy the codeCopy the codeUPDATE account SET p_money=p_money-100 WHERE p_name= “Tim”; Lock row data where p_name= “Tim”; P_name = “bill”;
UPDATE account SET p_money=p_money+100 WHERE p_name= “bill”;
Lock data where p_name= “bill” and attempt to obtain data where p_name= “Tim”;
At this point, the two threads enter a deadlock, neither can obtain their desired resources, enter the wireless wait, until the timeout!
How to avoid and deal with
- Innodb_lock_wait_timeout Wait lock timeout rollback transaction:
The intuitive approach is that while two transactions are waiting for each other, when one waits beyond a certain threshold set, one transaction is rolled back and the other transaction continues. This method is simple and effective. In InnoDB, the parameter Innodb_lock_wait_timeout is used to set the timeout.
- A wait-for graph algorithm for active deadlock detection:
Innodb also provides a wait-for graph algorithm to proactively detect deadlocks. The wait-for graph algorithm is triggered whenever a lock request is not immediately satisfied and enters a wait. innodb_deadlock_detect
- Apply layer locking suggestions
The conclusion of this question depends on which locks are held by transaction A after the two UPDATE statements are executed and when they are released. You can verify that transaction B’s UPDATE statement is actually blocked until transaction A commits.
Knowing this answer, you must know that the row locks on the two records held by transaction A were released at commit time.
That is, in InnoDB transactions, row locks are added when they are needed, but are not released immediately when they are no longer needed, but at the end of the transaction.
Knowing this setting, how does it help us use transactions? That is, if you need to lock more than one row in your transaction, put the locks that are most likely to cause lock conflicts and affect concurrency as far back as possible.
Suppose you are responsible for implementing an online transaction for movie tickets. Customer A wants to buy tickets at theater B. To simplify things, this business needs to involve the following operations:
Deduct the movie ticket price from customer A’s account balance;
Add this movie ticket to theater B’s account balance;
Log a transaction.
That is, to complete the transaction, we need to update two records and insert one record. Of course, to keep the transaction atomic, we put these three operations in one transaction. So, how would you arrange the order of these three statements in a transaction?
If another customer C wants to buy a ticket at cinema B at the same time, then the part of the two transactions that conflict is statement 2. Because they need to update the balance of the same theater account, they need to modify the same line of data.
As set above, no matter how you order the statements, all operations require row locks to be released at transaction commit time. So, if you put statement 2 at the end, say 3, 1, 2, then the cinema account balance line will be locked for the least amount of time. This minimizes lock waits between transactions and improves concurrency.
How to lock
- Implicit lock
Innodb implicitly locks DDL statements.
- Explicit locking
select… from table for update;
select… from table lock in mode;