This is the fourth day of my participation in the August More text Challenge. For details, see: August More Text Challenge

define

A lock is a mechanism by which a computer coordinates concurrent access to a resource by multiple processes or threads. The database locking mechanism is simply a rule designed by the database to ensure the consistency of data and make all kinds of shared resources become orderly when being concurrently accessed.

The classification of the lock

Locks can be classified into various categories according to their types

Classification of types of data operations

  1. Read lock (shared lock) : Multiple read operations on the same data can be performed simultaneously without affecting each other.
  2. Write lock (exclusive lock) : Blocks other write locks and read locks until the current write operation is complete.

Granularity classification of data operations

As much as possible in order to improve the concurrency of database, data range as small as possible for each of the lock, in theory, every time to lock the current scheme of data operation will get maximum concurrency, but management lock is very expensive things (relating to the acquisition, check and release, etc.), so the database system need to be in high concurrency response and performance of the system balance, This gives rise to the concept of “lock granularity”.

  1. Table level lock: low overhead, fast locking; Deadlocks do not occur; The lock granularity is large, the probability of lock conflict is high, and the concurrency is the lowest. (MyISAM and Memory storage engines use table-level locking)
  2. Row-level locking: high overhead and slow locking; Deadlocks occur; Minimum locking granularity; The probability of lock conflict is the lowest and the concurrency is the highest. (InnoDB supports row-level locking as well as table-level locking, default row-level locking)
  3. Page locking: the cost and locking time are between table locking and row locking. Deadlocks occur; Lock granularity is between table lock and row lock, and concurrency is mediocre.
  4. Applicable: From a lock perspective, table locks are suitable for applications where the data is mostly queried and only updated by index criteria. Row locking is suitable for applications that have a large number of concurrent updates of a small number of different data by index criteria and concurrent queries.

MyISAM table locks

  1. Table locking has two modes
Table shared read lock: it does not block read requests from other users to the same table, but blocks write requests to the same table. Table exclusive write lock: Blocks read or write requests from other users to the same table.Copy the code
  1. MyISAM tables are sequential between reads and writes, and between writes.
  2. By default, write locks have a higher priority than read locks: when a lock is released, it is given first to waiting lock requests in the write lock queue and then to waiting lock requests in the read lock queue.

InnoDB row locks

  1. InnoDB implements two types of row locks.
Shared lock (S) : Allows one transaction to read a row, preventing other transactions from acquiring exclusive locks on the same dataset. Exclusive lock (X) : Allows transactions that acquire exclusive locks to update data, preventing other transactions from acquiring shared and exclusive locks on the same dataset.Copy the code
  1. InnoDB also has two types of intentional locks (both table locks) for internal use in order to allow both row and table locks to coexist and implement a multi-grained locking mechanism.
Intended shared lock (IS) : If a transaction intends to add a row-shared lock to a row, it must first acquire the IS lock for the table. Intended exclusive lock (IX) : A transaction that intends to assign an exclusive lock to a row must first acquire the table's IX lock.Copy the code
  1. Index invalidation causes row lock variable table lock.

Optimistic lock and pessimistic lock

Optimistic locking: optimistically, it is believed that concurrent update conflicts will not occur. No locking is applied during data access and processing. Only when data is updated, conflicts are judged according to the version number or timestamp.

Pessimistic lock: Pessimistic view of concurrent update conflicts, data access and processing before the exclusive lock, the data is locked throughout the process, the lock is released after the transaction commit or rollback. Pessimistic lock is implemented by the database itself, to use, we directly call the related statements of the database.

InnoDB’s three row locks

Record Locks Locks on a single row of records. The index entry is locked to lock the rows that meet the criteria. Other transactions cannot modify or delete locked items.

Example: add a record lock on a record with ID =1 to prevent other transactions from inserting, updating, or deleting the row

SELECT * FROM table WHERE id = 1 FOR UPDATE;
Copy the code

Gap Locks When InnoDB requests shared or exclusive Locks for retrieving data using range criteria rather than equality criteria, InnoDB Locks the index entries of existing data records that meet the criteria. Add gap locks for records whose key values are in the range of conditions but do not exist.

  • Gap locks are based on non-unique indexes. Gap locks are used to lock an interval, not just each piece of data in the interval.
  • The purpose of the Gap lock is to prevent phantom reads from occurring between two current reads of the same transaction.
All rows in (1, 10) will be locked, all rows with ids 2, 3, 4, 5, 6, 7, 8, 9 will be blocked, but rows 1 and 10 will not be locked.
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
Copy the code

Next-key Locks

  • A combination of a record lock and a gap lock whose blocking range includes both an index record and an index range. The purpose of the critical lock is also to avoid phantom reads. If the transaction isolation level is lowered to RC, the critical lock will fail.
  • Can be understood as a special clearance lock, through the key lock can solve the problem of magic reading. There is an adjacent key lock on the non-unique index column of each data row. When a transaction holds the adjacent key lock on the data, it will lock an open and close range of data. InnoDB implements row-level lock based on index, and the adjacent key lock is only related to non-unique index columns. There is no adjacent key lock on unique index columns (including primary key columns).

A deadlock

Deadlock generation

  1. A deadlock is when two or more transactions occupy each other on the same resource and request to lock the occupied resource, resulting in a vicious cycle.
  2. Deadlocks can occur when a transaction attempts to lock resources in a different order. Deadlocks can also occur when multiple transactions lock the same resource simultaneously.
  3. The behavior and order of locks depend on the storage engine. Executing statements in the same order, some storage engines produce deadlocks and some do not.
  4. Deadlocks occur for two reasons: real data conflicts; The implementation of storage engine;

Detection of deadlock

The database system implements various mechanisms of deadlock detection and deadlock timeout. InnoDB storage engine can detect the cyclic dependency of deadlock and return an error immediately.

Deadlock recovery

After a deadlock occurs, the deadlock can only be broken by partially or completely rolling back one of the transactions. InnoDB currently handles deadlocks by rolling back transactions that hold the least row-level exclusive lock.

Deadlock detection for external locks

InnoDB usually detects a deadlock automatically and causes one transaction to release the lock and roll back, while the other acquires the lock and continues the transaction. InnoDB does not automatically detect deadlocks in cases involving external locks or table locks. This is solved by setting the lock wait timeout parameter innodb_lock_WAIT_TIMEOUT.

Deadlocks affect performance

Deadlocks affect performance rather than cause serious errors because InnoDB automatically detects and handles deadlocks. On highly concurrent systems, deadlock detection can slow things down when many threads are waiting for the same lock. Sometimes when a deadlock occurs, it may be more effective to disable deadlock detection (innodb_deadlock_detect configuration option) and rely on the Innodb_lock_WAIT_TIMEOUT setting to roll back the transaction.

MyISAM avoids deadlocks

In the case of automatic locking, MyISAM always obtains all the locks required by the SQL statement at once, so there are no deadlocks in MyISAM tables.

InnoDB avoids deadlocks

  1. Use the select… The for update statement gets the necessary locks;
To avoid deadlocks when performing multiple concurrent writes on a single InnoDB table, you can start the transaction by using select... For update gets the necessary locks.Copy the code
  1. Apply for a lock of sufficient level directly;
In a transaction, if you want to update records, you should directly request a lock of sufficient level, that is, an exclusive lock. Instead of applying for shared locks and then exclusive locks at update time. By the time an exclusive lock is applied, another transaction may have acquired a shared lock for the same record, resulting in a lock conflict or deadlock.Copy the code
  1. Specifies the order in which multiple tables are accessed
If a transaction needs to modify or lock multiple tables, lock statements should be used in the same order in each transaction. In the application, if different programs concurrently access multiple tables, should try to agree to the same order to access the tables, which can greatly reduce the chance of deadlock.Copy the code
  1. Change the transaction isolation level