Basic tutorial on InnoDB storage engine locks in Mysql _mysql

MyISAM and memory using table-level locks (table-level locking)

BdB with page locks (page-leve locking) or table-level locks, default to page locks

InnoDB supports row-level locks (row-level locking) and table-level locks, which default to row-level locks

Various lock features

Table-level Lock: Low overhead, lock fast, no deadlock, high locking granularity, highest probability of conflict, minimum concurrency

Row-level locks: large overhead, slow lock, deadlock, minimum locking granularity, lowest probability of lock collision and highest concurrency

Page Lock: Overhead and lock time between table lock and row lock, deadlock, lock granularity between table lock and row lock, concurrency is generally

Lock of InnoDB storage engine

The InnoDB storage engine implements the following two types of locks

1, shared Lock (S lock), allows transactions to read a row of data

2, exclusive lock (X Lock), allow transactions to update or delete a row of data

The compatibility of shared and exclusive locks is shown in the following illustration

Non-locked read of consistency

Consistent, non-locked row reads (consistent nonlocking read) refers to the InnoDB storage engine reads data from rows in the current execution time database by using multiple versioning (multi versioning). If the read row is executing the delete, update operation, this is the read operation that will not wait for the row to be locked for release, instead, InnoDB will read a snapshot of the row.

It is called an unlocked read because there is no need for the release of X locks on rows waiting to be accessed. Snapshot data refers to the data from the previous version of the row, which is implemented through the undo segment. However, at different transaction isolation levels, the reading is different, and not all reads are consistent read under each transaction isolation level.

For example:

For the transaction isolation level of the Read committed, he always reads the latest version of the row and, if the row is locked, reads the latest snapshot of the row version.

For repeatable read (the default isolation level for the InnoDB storage engine), the row data at the beginning of the transaction is always read.

The mechanism of non-locking reads greatly improves the concurrency of data reading, which is the default read mode under the InnoDB storage engine default settings, but in some cases, reading can be locked, for example:

1. Explicitly add locks to read, such as using select---for UPDATE; Select---lock in share mode

2, the foreign key inserts and updates, because the foreign key inserts and the update, for the data isolation requirement is high, before inserts needs to scan the parent table record existence, therefore, the foreign key inserts deletes, the INNODB will use adds S lock the way to realize.

The algorithm of InnoDB lock

1. Record Lock: Locks on individual Row Records

2, Gap Lock: clearance locks, 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

The record lock always locks the index records, and if the InnoDB Storage engine table is set up without setting any index, the InnoDB storage engine locks with an implicit primary key, Next-key lock at repeatable read Isolation level The algorithm is the default row record locking algorithm.

Problems with Locks

1. Lost update

How to avoid losing updates: Make a transaction a serial operation rather than a concurrent operation, that is, to start---each transaction with an exclusive lock on the read record.

2. Dirty Reading

Dirty reading is a transaction that can read uncommitted data in another transaction, which violates the isolation of the database.

The condition that dirty reads occur is that the isolation level of the transaction is required for READ UNCOMMITTED.

3. Non-repeatable reading

The difference between non-repeatable and dirty reads is that dirty reads are read to uncommitted data, but cannot be read in duplicate.

In general, non-repeatable reads are acceptable, and in the InnoDB storage engine, the Next-key lock algorithm is used to avoid the problem of non repeatable reads.

It is noteworthy that by default the InnoDB storage engine does not roll back the error exception thrown by the timeout.

Deadlock-related issues

1, the condition of the deadlock occurred

Mutex: A resource can only be used by one process at a time; the request and the retention condition: a process that is blocked by requesting resources, retains the resources that have been obtained, and is not deprived of the resources that the process has obtained, and cannot be forcibly deprived until the end of use is exhausted; loop wait condition: Several processes form a end-to-end loop waiting for resource relationships.

2, deadlock detection (based on online experience)

There are two scenarios in which InnoDB detects deadlocks, one that satisfies the loop wait condition, and another strategy: the lock structure exceeds the maximum number set in the MySQL configuration or the lock's traversal depth exceeds the maximum depth set, and InnoDB is also judged to be a deadlock (this is a performance-enhancing consideration, Avoid transactions that consume too many resources at once.

The deadlock caused by the cyclic wait condition can only be four forms: two rows of records cross-request mutexes, the same table there are primary key index lock conflicts, primary key index lock and nonclustered index lock conflict, lock escalation caused by lock wait queue blocking.

3. Deadlock avoidance (based on online experience)

1. If you use the Insert...select statement to back up the table with a large amount of data, operate at a separate point in time, avoid competing with other SQL statements, or use the SELECT INTO OutFile plus the load data infile instead of Insert...select, This is not only fast, but it doesn't require locking.
2. A transaction that locks a recordset, its operation result set should be as brief as possible so that it does not occupy too many resources at once and conflicts with records processed by other transactions.
3. Update or delete the table data, the SQL statement where conditions are primary key or index, to avoid the intersection of two cases, resulting in deadlock. For a more complex where clause, it is used separately in the UPDATE statement after it is obtained by SQL.
4. SQL statements do not have too many nested tables, can split the split, avoid occupying resources at the same time waiting for resources, resulting in conflicts with other transactions.
5. To run the script on the fixed-point, avoid running multiple scripts to read and write to the same table at the same point of time, paying special attention to the statements that lock and manipulate the data to a large amount.
6. Increase the judgment of deadlocks in the application, and if the transaction ends unexpectedly, rerun the transaction, reducing the effect on the function.

4, deadlock resolution

1 First execute show processlist find the deadlock thread number. Then kill PID

2 show InnoDB status check engine state to see which statements produce deadlocks

3) View the Innodb_locks, Innodb_trx, innodb_lock_waits tables under the INFORMATION_SCHEMA architecture

Ps:mysql dead Lock

Now that we're talking about deadlocks, that's a special one.
What is a deadlock?

Deadlocks are the result of improper allocation and use of resources. Is the phenomenon of two processes competing for a resource and waiting for each other. Specifically, there are four necessary requirements for deadlocks:
(1) Mutually exclusive conditions: each resource can only be used by one process
(2) Request and retention conditions: When a process is blocked by requesting resources, the acquired resources are persisted
(3) Non-deprivation of conditions: the resources that the process has acquired cannot be forcibly deprived until the end of use.
(4) Cyclic waiting condition: the process of forming a kind of end-to-end circular waiting resource relationship between several processes.
Obviously, a deadlock takes two or more two processes, in other words, deadlocks occur in concurrent programs. In MySQL, due to the current only InnoDB engine using transactions (InnoDB support locks), there will be InnoDB and deadlock of the unprecedented base love.
Dead-Lock detection

1, by using show InnoDB status check engine state, you can see which statements produce deadlock
2, MySQL provides a information_schema, by viewing innodb_locks, Innodb_trx, innodb_lock_waits these tables detect deadlocks.
The deadlock caused by the cyclic wait condition can only be four forms: two rows of records cross-request mutexes, the same table there are primary key index lock conflicts, primary key index lock and nonclustered index lock conflict, lock escalation caused by lock wait queue blocking.

Deadlock avoidance

1. If you use the Insert...select statement to back up the table with a large amount of data, at a single point in time, avoid competing with other SQL statements, or use the SELECT INTO OutFile plus the load data infile instead of INSERT ... Select, which is not only fast, but does not require locking of the
2. A transaction that locks a recordset should be as brief as possible so that it does not consume too many resources at once and conflicts with records processed by other transactions.
3. Update or delete table data, the SQL statement where conditions are primary keys or are indexed, to avoid two cases of intersection, resulting in deadlock. For a more complex where clause, it is used separately in the UPDATE statement after it is obtained by SQL.
4. SQL statements do not have too many nested tables to split, to avoid occupying resources while waiting for resources, resulting in conflicts with other transactions.
5. To run a script on a fixed-point, avoid running multiple scripts to read and write to the same table at the same point of time, paying special attention to statements that lock and manipulate data volumes.
6. Increase the judgment of deadlocks in the application and, if the transaction ends unexpectedly, rerun the transaction, reducing the impact on the functionality.