MySQL lock

Time:2022-8-7

Thresh

lock classification

The granularity of operations can be divided into table-level locks, row-level locks, and page-level locks.

Table-level lock: locks the entire table for each operation. The lock granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest. It is used in storage engines such as MyISAM, InnoDB, and BDB.
Row-level lock: Locks a row of data for each operation. The lock granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest. Applied in the InnoDB storage engine.
Page-level lock: Locks a group of adjacent records each time, the locking granularity is between the table lock and the row lock, the overhead and locking time are between the table lock and the row lock, and the degree of concurrency is normal. Applied in the BDB storage engine.

The types of slave operations can be divided into read locks and write locks.

Read lock (S lock): Shared lock, for the same data, multiple read operations can be performed at the same time without affecting each other.  
Write lock (X lock): Exclusive lock, it will block other write locks and read locks before the current write operation is completed.

IS locks, IX locks: intentional read locks and intentional write locks belong to table-level locks. S and X are mainly for row-level locks. Before adding S or X locks to table records, IS or IX locks are added to the table.

S lock: Transaction A adds an S lock to the record, which can read the record but cannot be modified. Other transactions can add an S lock to the record, but cannot add an X lock, need to add an X lock, and need to wait for the record S lock All released.
X lock: Transaction A adds X lock to the record, which can read and modify the record, and other transactions cannot read and modify the record.

From the performance of the operation, it can be divided into optimistic locking and pessimistic locking

Optimistic locking: The general implementation method is to compare the recorded data versions, and the conflict detection will be performed when the data update is submitted. If a conflict is found, an error message will be prompted.
Pessimistic lock: When modifying a piece of data, in order to avoid being modified by others at the same time, the control method is to lock the data before modifying it, and then modify it. Shared locks and exclusive locks are different implementations of pessimistic locks, but both belong to the category of pessimistic locks.

row lock principle

In the InnoDB engine, we can use row locks and table locks, where row locks are further divided into shared locks and exclusive locks. InnoDB row locks are implemented by locking records on index data pages. There are three main implementation algorithms: Record Lock, Gap Lock, and Next-key Lock.

RecordLock lock: A lock that locks a single row record. (record lock, both RC and RR isolation levels are supported)
GapLock lock: Gap lock, locks the gap between index records and ensures that the gap between index records remains unchanged. (range lock, RR isolation level support)
Next-key Lock lock: The combination of record lock and gap lock locks the data at the same time, and locks the front and back ranges of the data. (record lock + range lock, RR isolation level support)

At the RR isolation level, InnoDB first uses Next-Key Lock for record locking behavior. However, when the SQL operation contains a unique index, Innodb will optimize the Next-Key Lock and downgrade it to RecordLock, which only locks the index itself, not the index itself. scope.

1) select ... from statement: The InnoDB engine uses the MVCC mechanism to implement non-blocking reading, so for ordinary select statements, InnoDB does not lock
2) select ... from lock in share mode statement: A shared lock is added, and InnoDB will use the Next-Key Lock lock for processing. If the scan finds a unique index, it can be downgraded to a RecordLock lock.
3) select ... from for update statement: an exclusive lock is added, and InnoDB will use the Next-Key Lock lock for processing. If the scan finds a unique index, it can be downgraded to a RecordLock lock.
4) update ... where statement: InnoDB will use Next-Key Lock for processing. If the scan finds a unique index, it can be downgraded to RecordLock.
5) delete ... where statement: InnoDB will use Next-Key Lock for processing. If the scan finds a unique index, it can be downgraded to RecordLock.
6) insert statement: InnoDB will set an exclusive RecordLock lock on the row to be inserted.

pessimistic lock

Pessimistic Locking refers to locking the data in the process of data processing, which is generally implemented by the locking mechanism of the database. Broadly speaking, the aforementioned row locks, table locks, read locks, write locks, shared locks, exclusive locks, etc., all belong to the category of pessimistic locks

table level lock
Table-level locks lock the entire table for each operation, with the lowest degree of concurrency.
Manually increase the table lock

lock table table name read|write, table name 2 read|write;

View the locks added to the table

show open tables;

remove table lock

unlock tables;

Table-level read lock: The current table is appended with a read lock, and the current connection and other connections can be read; however, the current connection addition, deletion, and modification operations will report an error, and other connection additions, deletions, and modifications will be blocked.
Table-level write lock: The current table appends a write lock. The current connection can add, delete, modify, and query the table. All operations on the table (including queries) are blocked by other connections.
Summary: Table-level read locks block write operations, but not read operations. Write locks block both read and write operations.

Shared locks (row-level locks – read locks)
Shared locks are also called read locks, or S locks for short. A shared lock means that multiple transactions can share a lock on the same data, and can access the data, but can only read and cannot modify it. The way to use a shared lock is in select … lock in share mode, which only applies to query statements.
Summary: The transaction uses a shared lock (read lock), which can only be read and cannot be modified, and the modification operation is blocked.

Exclusive lock (row-level lock – write lock)
Exclusive locks are also called write locks, or X locks for short. An exclusive lock cannot coexist with other locks. For example, if a transaction acquires an exclusive lock on a data row, other transactions cannot perform other operations on the row record or acquire the row lock.
The way to use an exclusive lock is to add for update at the end of SQL. By default, the innodb engine will add for update to the update and delete statements.
The implementation of row-level locks actually relies on its corresponding index, so if the query does not use the index, the entire table records will be locked.

Summary: The transaction uses an exclusive lock (write lock), the current transaction can read and modify it, other transactions cannot modify it, nor can it acquire a record lock (select… for update). If the query does not use an index, the entire table record will be locked.

optimistic locking

Optimistic locking is relative to pessimistic locking. It is not a function provided by the database and needs to be implemented by developers themselves. When operating the database, the idea is very optimistic, thinking that this operation will not lead to conflict, so no special treatment is done during database operation, that is, no locking, but to determine whether there is a conflict when the transaction is committed .

The key point of optimistic locking implementation: conflict detection

Both pessimistic locks and optimistic locks can solve transaction write and write concurrency. In applications, you can choose to distinguish them according to the concurrent processing capability. For example, you can choose optimistic locks for high concurrency requirements; pessimistic locks can be selected for low concurrency requirements.

Optimistic lock implementation principle
Use the version field (version)
First add a version field to the data table, and add 1 to the version number of that record for each operation. The version is used to check whether the read record has changed, and the function is to prevent the record from being modified by other transactions during business processing.

Use Timestamp
Similar to using the version field, you also need to add a field to the data table, and the field type uses timestamp
timestamp. It is also to check the timestamp of the data in the current database and the timestamp obtained before the update when the update is submitted.
For comparison, if they are consistent, submit the update, otherwise it is a version conflict and cancel the operation.

update products set quantity=quantity-1,version=version+1 where id=1 and version=#{version};

In addition to manually implementing optimistic locking, many database access frameworks also encapsulate the implementation of optimistic locking, such as
hibernate framework. The MyBatis framework can be extended using the OptimisticLocker plugin.

Deadlocks and Solutions

table lock deadlock
cause:

User A accesses table A (locks table A), and then accesses table B; another user B accesses table B (locks table B), and then attempts to access table A; at this time, user A is locked because user B For table B, it must wait for user B to release table B before it can continue. Similarly, user B must wait for user A to release table A before it can continue, which leads to a deadlock.

User A–"A table (table lock)–"B table (table lock)
User B–"B table (table lock)–"A table (table lock)

solution:
This kind of deadlock is relatively common and is caused by bugs in the program. There is no other way except to adjust the logic of the program. Carefully analyze the logic of the program. When operating on multiple tables in the database, try to process them in the same order, and try to avoid locking two resources at the same time. For example, when operating two tables, A and B are always processed in the order of A first and then B. , when two resources must be locked at the same time, ensure that the resources should be locked in the same order at all times.

row-level lock deadlock
Cause 1:
If a query without index conditions is executed in a transaction, a full table scan is triggered, and the row-level lock is upgraded to a full-table record lock (equivalent to a table-level lock), and it is easy to cause death after multiple such transactions are executed. Locks and blocking, eventually the application system will become slower and slower, blocking or deadlock will occur.

Solution 1:
Do not use too complex queries related to multiple tables in the SQL statement; use the explain "execution plan" to analyze the SQL statement, and for the SQL statement with full table scan and full table lock, establish corresponding indexes for optimization.

==========================================================================

Cause 2:
Two transactions want to get the lock held by each other and wait for each other, so a deadlock occurs.

Solution 2:
In the same transaction, try to lock all the resources needed at once
Sort resources by id and process them in order

Convert shared lock to exclusive lock
cause:
Transaction A queries a record, and then updates the record; at this time, transaction B also updates the record. At this time, the exclusive lock of transaction B, because transaction A has a shared lock, must wait for A to release the shared lock before it can be acquired, and can only be queued wait. When transaction A performs the update operation again, a deadlock occurs here, because transaction A needs an exclusive lock for the update operation. However, the lock request cannot be granted because transaction B already has an exclusive lock request and is waiting for transaction A to release its shared lock.

Transaction A: select * from dept where deptno=1 lock in share mode; //shared lock, 1
        update dept set dname='java' where deptno=1;//exclusive lock, 3
Transaction B: update dept set dname='Java' where deptno=1;//Because 1 has a shared lock, an exclusive lock cannot be obtained, and needs to wait, 2

solution:
For controls such as buttons, the click will be invalid immediately, and users will not be allowed to click repeatedly, so as to avoid causing multiple operations on the same record at the same time;

Use optimistic locking for control. The optimistic locking mechanism avoids database locking overhead in long transactions and greatly improves large concurrency
lower system performance. It should be noted that since the optimistic locking mechanism is implemented in our system, the user from the external system
User update operations are not under the control of our system, so dirty data may be updated to the database;

Deadlock troubleshooting
MySQL provides several lock-related parameters and commands, which can assist us in optimizing lock operations and reducing deadlock occurrence. View deadlock logs

View recent deadlock log information through the show engine innodb status\G command.

Usage: 1. View recent deadlock log information; 2. Use explain to view the SQL execution plan

View Lock Status Variables

Check the status variables through the show status like 'innodb_row_lock%' command to analyze the contention of row locks in the system
    Innodb_row_lock_current_waits: the number of currently waiting locks
    Innodb_row_lock_time: The total length of time since the system was started to lock
    Innodb_row_lock_time_avg: Average time to wait for a lock each time
    Innodb_row_lock_time_max: The time to wait for the longest lock since the system was started
    Innodb_row_lock_waits: The total number of times the system has waited since the system was started

If the number of waiting times is high and the waiting time is long each time, it is necessary to analyze why there are so many waits in the system, and then proceed to
hand customization optimization