Tutorial _ MySQL

This article mainly introduces the basic concepts of InnoDB storage engine locks in MySQL. it is the basic knowledge in MySQL getting started, for more information, see MyISAM and MEMORY using table-level locking)

BDB uses page-leve locking or table-level locks. the default value is page locks.

InnoDB supports row-level locking and table-level locking. the default value is row-level locking.

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.

InnoDB storage engine lock

The InnoDB storage engine implements the following two locks:

1. shared Lock (S Lock), allowing transactions to read a row of data

2. exclusive Lock (X Lock), which allows the transaction to update or delete a row of data

Shows compatibility between shared locks and exclusive locks.

Consistent non-locked read

Consistent non-lock row read refers to the InnoDB storage engine reading data of the row in the database at the current execution time through multi versioning. If the read row is performing the DELETE or UPDATE operation, the read operation will not wait for the row to be locked to be released. On the contrary, InnoDB will read a snapshot of the row.

The reason is that it is not a locked read because the X lock on the row that does not need to be accessed is released. Snapshot data refers to the data of the previous version, which is implemented through the undo segment. However, at different transaction isolation levels, the reading method is different. not all transaction isolation levels read consistent reading.

For example:

For the transaction isolation level of read committed, it always reads the latest version of the row. if the row is locked, it reads the latest snapshot of the row version.

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

The non-locked read mechanism greatly improves the concurrency of data reading. this is the default read method in the default settings of the Innodb storage engine, but in some cases, you can lock the read. for example:

1. explicitly lock the read, for example, using select --- 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, InnoDB will apply the S lock to insert and delete foreign keys.

InnoDB lock algorithm

1. Record Lock: the Lock on a single row Record

2. Gap Lock: a Gap Lock that locks 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 Lock always locks index records. If no index is set when an InnoDB storage engine table is created, the InnodB storage engine uses an implicit primary key to Lock the table, at the Repeatable Read isolation level, the Next-key Lock algorithm is the default row record Lock algorithm.

Lock problems

1. update loss

How to avoid loss of updates: Let the transaction become a serial operation, rather than a concurrent operation, that is, to start each transaction-add an exclusive lock to the Read record.

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 is Read uncommitted.

3. repeatable reading

The difference between repeatable reading and dirty reading is that dirty reading reads read uncommitted data, rather than reading committed data.

In general, unrepeatable reading is acceptable. in The InnoDB storage engine, the Next-Key Lock algorithm is used to avoid the problem of unrepeatable reading.

It is worth noting that by default, the InnoDB storage engine will not roll back the error exceptions 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 obtained resources are not allowed: resources obtained by a process cannot be forcibly deprived before they are used. cyclic waiting conditions: a cyclic waiting resource relationship is formed between several processes.

2. Deadlock Detection (based on online experience)

Innodb detects deadlocks in two situations: one is to meet the loop wait condition, and the other is: when the lock structure exceeds the maximum number set in the mysql configuration or the traversal depth of the lock exceeds the maximum depth set, innodb will also judge as a deadlock (this is a performance improvement consideration, avoid a transaction occupying too many resources at a time ).

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 insert... The select statement backs up the table with a large amount of data. you can operate the table at a separate time point to avoid competing for resources with other SQL statements, or use select into outfile with load data infile instead of insert... Select, this is not only fast, but also 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 conflicting with the records processed by other transactions.
3. update or delete table data. The where condition of the SQL statement is either a primary key or an index, avoiding crossover between the two conditions and causing a deadlock. When the where clause is complex, you can obtain it through SQL and then use it in the update statement.
4. do not use too many nested tables for SQL statements. you can split the tables 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 that 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. add a deadlock judgment in the application. if the transaction ends unexpectedly, re-run the transaction to reduce the impact on the function.

4. deadlock resolution

1) first execute show processlist to find the deadlock thread number, and then Kill the pid

2) check the engine status in Show innodb status to see which statements generate deadlocks.

3) view innodb_locks, innodb_trx, innodb_lock_waits, and other tables under the information_schema architecture.

PS: Mysql deadlock

Now that we talk about deadlocks, let's talk about them here.
What is a deadlock?

Deadlocks are caused by improper resource allocation and use. Two processes are waiting for a certain resource. Specifically, a deadlock must meet four necessary conditions:
(1) mutex condition: each resource can only be used by one process.
(2) request and retention conditions: when a process is blocked by requesting resources, it will not release the obtained resources.
(3) non-deprivation condition: the resources obtained by the process cannot be forcibly deprived before they are used.
(4) cyclic waiting condition: a cyclic waiting resource relationship is formed between several processes that are connected at the beginning and end.
Obviously, two or more processes are required for a deadlock. In other words, the deadlock occurs in a concurrent program. In Mysql, because only the InnoDB engine currently uses transactions (InnoDB supports locks), InnoDB and deadlocks can be achieved.
Deadlock Detection

1. check the engine status by using Show innodb status. you can see which statements generate deadlock.
2. MySQL provides an ionion_schema to check deadlocks by viewing innodb_locks, innodb_trx, and innodb_lock_waits tables.
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.

Deadlock Avoidance

1. If you use insert... The select statement backs up the table with a large amount of data. you can operate the table at a separate time point to avoid competing for resources with other SQL statements, or use select into outfile with load data infile instead of insert... Select, this is not only fast, but also 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 conflicting with the records processed by other transactions.
   3. update or delete table data. The where condition of the SQL statement is either a primary key or an index, avoiding crossover between the two conditions and causing a deadlock. When the where clause is complex, you can obtain it through SQL and then use it in the update statement.
   4. do not use too many nested tables for SQL statements. you can split the tables 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 that 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. add a deadlock judgment in the application. if the transaction ends unexpectedly, re-run the transaction to reduce the impact on the function.
   

The above is the basic tutorial _ MySQL content of the InnoDB storage engine lock in MySQL. For more information, see PHP Chinese website (www.php1.cn )!