MySQL InnoDB Lock

MySQLLock Mechanism of common storage engines

MyISAMAndMemoryTable-Level Lock (Table-level locking)

BdbUse the page lock (Page-Leve locking) Or table-Level Lock. The default value is page lock.

InnoDBSupports row-level locks (Row-level locking) And table-level locks. The default value is Row-level locks.

Various lock features

Table-Level Lock: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of conflict, the lowest concurrency

Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, minimum probability of lock conflict, and highest concurrency

Page lock: the overhead and lock time are between the table lock and the row lock. There will be deadlocks. The lock granularity is between the table lock and the row lock, and the concurrency is normal.

InnoDBStorage engine lock

InnoDBThe storage engine implements the following two locks:

1. Shared lock (S lock), Allows the transaction to read a row of data.

2. exclusive lock (X lock)Allows a transaction to update or delete a row of data.

Shows compatibility between shared locks and exclusive locks.

 

Consistent non-locked read

Consistent non-locked row read (Consistent nonlocking read) RefersInnoDBThe storage engine uses multi-Version Control (Multi Versioning) To read the data of the row in the database at the current execution time. If the read row is being executedDelete,UpdateThis is because the read operation will not wait for the release of the row lock. On the contrary,InnoDBReads a snapshot of the row.

The reason is that it is not a locked read because it does not need to wait for access on the rowXLock release. Snapshot data refers to modifying the data of previous versions.Undo. However, at different transaction isolation levels, the reading method is different. Not all transaction isolation levels read consistent reading.

For example:

ForRead committedAt the transaction isolation level, it always reads the latest version of the row. If the row is locked, it reads the latest snapshot of the row version.

ForRepeatable read (InnoDBDefault storage engine isolation level),Always reads the row data at the beginning of the transaction.

The non-locked read mechanism greatly improves the concurrency of Data Reading.InnoDBBy default, the storage engine uses the default read mode, but in some cases, the read can be locked, for example:

1. explicitly lock the read, suchSelect --- for update; Select --- lock in share mode

2. During the insertion and update of Foreign keys, the data isolation requirements are high for the insertion and update of Foreign keys. Before the insertion, You need to scan the records in the parent table for existence, therefore, when the foreign key is inserted or deleted,InnoDBAddSLock.

InnoDBLockedAlgorithm

1. Record lock: Lock on a single row record

2. Gap lock: Gap lock, locking a range, but does not contain the record itself

3. Next-key lock: Gap lock + record lock, Lock a range, and lock the record itself

Record lockIndex records are always locked. IfInnoDBWhen a storage engine table is created, no index is set.InnoDBThe storage engine uses an implicit primary key for locking.Repeatable readAt the isolation level,Next-key lockThe algorithm is the default row record lock algorithm.

Lock Problems

1. Update loss

How to avoid missing updates:Converts a transaction into a serial operation, rather than a concurrent operation, that is, starting each transaction.---Apply exclusive lock to read records.

2. Dirty reading

Dirty reading means that a transaction can read uncommitted data from another transaction, which violates the database isolation.

The condition for dirty reads is that the transaction isolation level isRead uncommitted.

3. Repeatable reading

The difference between non-repeated reads and dirty reads is:Dirty reads read uncommitted data, but cannot read committed data repeatedly..

In general, non-repeated reads are acceptable.InnoDBStorage engine,UseNext-key lockAlgorithm to avoid repeated reading.

It is worth noting that, by defaultInnoDBThe storage engine does not roll back errors caused by timeout.

Deadlock Problems

1. Conditions for deadlock

Mutual Exclusion condition: A resource can only be used by one process at a time. Request and persistence conditions: when a process is blocked due to a resource request, the acquired resources are not allowed.:Resources obtained by the process cannot be forcibly deprived before they are used. Cyclic waiting Conditions:Several processes form a cyclic resource wait relationship that connects the beginning and the end.

2. Deadlock Detection (based on online experience)

InnoDBThere are two ways to detect deadlocks: one is to meet the loop wait condition, and the other is to apply the lock structure beyondMySQLWhen the maximum number set in the configuration or the traversal depth of the lock exceeds the configured maximum depth,InnoDBIt will also be considered as a deadlock (this is to improve the performance, to avoid occupying too many resources at a time for a transaction ).

There are only four types of deadlocks due to cyclic wait conditions: the two tables apply for mutex locks for two rows of records. If the same table has a primary key index lock conflict, the primary key index lock conflicts with the non-clustered index lock, and the lock wait queue is blocked due to lock upgrade.

3. avoid deadlocks (based on online experience)

1. If you useInsert... SelectStatement to back up a table with a large amount of data.SQLStatement to compete for resources or useSelect into OUTFILEAddLoad data infileReplaceInsert... SelectThis is not only fast, but does not require locking.
2.For a transaction that locks the record set, the operation result set should be as short as possible to avoid occupying too much resources at a time and conflict with the records processed by other transactions.
3.Update or delete table data,SQLStatementWhereThe condition is both a primary key or an index, avoiding crossover between the two conditions, resulting in a deadlock. ForWhereClause is more complex.SQLAnd then use it in the update statement.
4. SQLDo not use too many nested tables in the statement. You can split them to avoid occupying resources and waiting for resources, resulting in conflicts with other transactions.
5.When you run scripts at a specified point, avoid running multiple scripts to read and write the same table at the same time point. Pay special attention to statements that lock and operate on a large amount of data.
6.ApplicationProgramAdd the deadlock judgment. If the transaction ends unexpectedly, re-run the transaction to reduce the impact on the function.

4. deadlock resolution

1) ExecuteShow processlistLocate the deadlock thread number.ThenKillPID

2) show InnoDB statusCheck engine status,We can see which statements generate deadlocks.

3)ViewInformation_schemaArchitectureInnodb_locks,Innodb_trx,Innodb_lock_waitsAnd other tables