Thresh

Classification of lock

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, and the probability of lock conflict is high and the concurrency is low. Application in MyISAM, InnoDB, BDB and other storage engines. Row-level locking: Locks one row of data per operation. The lock granularity is minimum, the probability of lock conflict is lowest, and the concurrency is highest. Application in InnoDB storage engine. Page-level locking: locking a group of adjacent records at a 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. Application in BDB storage engine.Copy the code

Operation types can be classified into read lock and write lock.

Read lock (S lock) : Shared lock. Multiple read operations on the same data can be performed simultaneously without affecting each other. Write lock (X lock) : An exclusive lock that blocks other write locks and read locks until the current write operation is complete.Copy the code

IS lock, IX lock: intention read lock, intention write lock, belongs to the table level lock, S and X mainly for row level lock. An IS or IX lock IS added to a table before an S or X lock IS added to a table record.

S lock: Transaction A adds S lock to the record and can read the record but cannot modify it. Other transactions can add S lock to the record but cannot add X lock. X lock needs to be added and all S locks need to be released. X lock: Transaction A adds an X lock to A record and can read or modify the record. Other transactions cannot read or modify the record.

The performance of the operation can be divided into optimistic locking and pessimistic locking

Optimistic lock: The general implementation is to compare the versions of recorded data, and the conflict detection will be performed only when the data update is submitted. If the conflict is found, an error message will be displayed. Pessimistic lock: Control of data modification before modification in order to avoid modification by others. Shared lock and exclusive lock are different implementations of pessimistic lock, but both belong to the category of pessimistic lock.Copy the code

Row lock principle

In The InnoDB engine, we can use row locks and table locks, which are divided into shared locks and exclusive locks. InnoDB row Lock is achieved by locking records on index data pages. There are three main implementation algorithms: Record Lock, Gap Lock and next-key Lock.

RecordLock: Lock that locks a single row record. (record lock, supported by RC and RR isolation levels.) GapLock: a GapLock locks index record gaps to ensure that they remain the same. (Range Lock, RR isolation level support) next-key Lock: record Lock and gap Lock combination, simultaneously locks data, and locks the data before and after range. (Record lock + range lock, RR isolation level support)Copy the code

At the RR isolation level, InnoDB uses next-key Lock for record locking first, but when SQL operations have unique indexes, InnoDB optimizes next-key Lock and downgrades it to RecordLock, which only locks the index itself rather than the range.

1) select... From statements: InnoDB engine uses MVCC mechanism to implement non-blocking read, so InnoDB does not lock normal SELECT statements. From Lock in Share mode statement: If a shared lock is appended, InnoDB uses a next-key lock. If a scan finds a unique index, it can degrade to a RecordLock. 3) select... From for UPDATE: An exclusive Lock is appended. InnoDB uses a next-key Lock. If a scan finds a unique index, it can be downgraded to a RecordLock. 4) the update... Where statement: InnoDB uses a next-key Lock for processing. If a scan finds a unique index, it can degrade to a RecordLock. 5) the delete... Where statement: InnoDB uses a next-key Lock for processing. If a scan finds a unique index, it can degrade to a RecordLock. 6) Insert statement: InnoDB sets an exclusive RecordLock on the row to be inserted.Copy the code

Pessimistic locking

Pessimistic Locking refers to the process of data processing, when the data is locked, the Locking mechanism of the database is generally used to implement it. In a broad sense, the previously mentioned 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 lock locks the entire table in each operation and has the lowest concurrency. Manually add table locks

The lock table table name read | write, table name 2 read | write;Copy the code

View the locks added to the table

show open tables;
Copy the code

Delete table locks

unlock tables;
Copy the code

Table read lock: a read lock is appended to the current table, and the current connection and other connections can be read. However, the current connection add, delete, or modify operation will report an error, and other connection add, delete, or modify operations will be blocked. Table-level write lock: The write lock is added to the current table. The current connection can add, delete, modify, or query the table. All operations (including query) on the table are blocked for other connections. Summary: Table-level read locks block write operations, but not read operations. Write locks block both read and write operations.

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

Exclusive lock (row level lock – write lock) Exclusive lock is also known as write lock, short for X lock. An exclusive lock is one that cannot coexist with other locks. If a transaction acquires an exclusive lock on a row, other transactions cannot perform other operations on that row or acquire the lock on that row. The way to use exclusive locking is to add for update to the end of the SQL statement. Innodb uses for update by default in delete statements. Row-level locking is implemented by relying on the corresponding index, so if you operate on a query that does not have an index, the entire table record will be locked.

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

Optimistic locking

Optimistic locking, as opposed to pessimistic locking, is not a feature provided by the database and needs to be implemented by the developer himself. In the database operation, the idea is very optimistic, thinking that this operation will not cause conflicts, so in the database operation does not do any special processing, that is, do not lock, but in the transaction commit to determine whether there is a conflict.

The key point of optimistic lock implementation is conflict detection

Pessimistic lock and optimistic lock can both solve the concurrency of transaction writing. In applications, they can be distinguished according to the concurrency processing ability. For example, optimistic lock is selected for high concurrency rate. Pessimistic locks can be selected for low concurrency requirements.

Optimistic lock implementation principle: Use version field to add a version field to the data table. For each operation, the version number of that record is increased by 1. Version is used to check whether the read record is changed to prevent the record from being modified by other transactions during service processing.

Using a Timestamp is similar to using a version field. You also need to add a field to the table. The field type uses a Timestamp. Also, check the timestamp of the data in the current database and compare it with the timestamp obtained before the update. If the timestamp is consistent, submit the update; otherwise, it is version conflict and cancel the operation.

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

In addition to implementing optimistic locking manually, many database access frameworks also encapsulate optimistic locking implementations, such as

Hibernate framework. The MyBatis framework can be extended using the OptimisticLocker plugin.

Deadlocks and solutions

Table deadlock causes:

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; User A must wait for user B to release table B because user B has locked table B, and user B must wait for user A to release table A before continuing.Copy the code

User A– table A (table lock) — Table B (table lock) — Table A (table lock)

Solution: This kind of deadlock is common and is caused by a BUG in the program. There is no other way to adjust the program logic. Close analysis of the program logic for multi-table operation of the database, processed in the same order as far as possible, try to avoid locking two resources at the same time, such as the operation of the A and B two tables, always press B after A first order processing, must lock two resources at the same time, to ensure that at any time should be to lock in the same order.

Row-level lock lock Reason 1: if the transaction is carried out a no index in the conditions of the query, trigger a full table scan, row-level locks up into a full table record locking (equivalent to the table level lock), after multiple such transaction execution, it is easy to produce a deadlock and blocking, application system will be more and more slow, finally happening or deadlock.

Solution 1: Do not use too complex queries associated with multiple tables in SQL statements; Use explain “Execution plan” to analyze SQL statements. For SQL statements with full table scan and full table lock, build corresponding indexes for optimization.

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

Cause 2: Two transactions wait for each other to obtain the lock held by the other, resulting in a deadlock.

Solution 2: In the same transaction, try to lock all the resources required at one time. Sort the resources by ID and process them in that order

Transaction A queries A record and updates the record. At this time, transaction B also updates the record, and the exclusive lock of transaction B can only be obtained after transaction A releases the shared lock. A deadlock occurs when transaction A performs the update operation again because transaction A requires an exclusive lock to do 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.

Select * from dept where deptno=1 lock in share mode; Update dept set dname=' Java 'where deptno=1; B: update dept set dname='Java' where deptno=1; // Can't get exclusive lock because 1 has shared lock, wait for 2Copy the code

Solution: for buttons and other controls, click immediately invalid, do not let the user click repeatedly, to avoid causing the same record multiple operations;

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

MySQL provides several lock-related parameters and commands to help optimize lock operations and reduce deadlocks

Run the show engine innodb status\G command to view recent deadlock logs.

Usage: 1. View recent deadlock logs. 2, Use Explain to view the SQL execution plan

View lock status variables

Innodb_row_lock_current_waits: Show status like'innodb_row_lock% ' Number of pending locks Innodb_row_lock_time: total length of lock time since system startup Innodb_row_lock_time_avg: average length of lock time Innodb_row_lock_time_max: Innodb_row_lock_waits: Total number of waits since system startupCopy the code

If the waiting times are high and each waiting time is long, we need to analyze why there are so many waits in the system, and then customize the optimization by hand