MySQL lock problem

MySQL's lock mechanism is relatively simple, and its most notable feature is that different storage engines support different locking mechanisms. For example, the MyISAM and memory storage engines

A table-level lock is used, the BDB storage engine uses a page lock, but also supports table-level locks; The InnoDB storage engine supports both row-level and table-level locks, but the default

Row-level locks are used in the case.

MySQL features of these 3 types of locks can be broadly summarized as follows:

(1) Table-level lock: Low overhead, lock fast, no deadlock, locking granularity is high, the probability of lock conflict is highest, the concurrency is the lowest.

(2) Row-level lock: High overhead, locking slow, deadlock, locking granularity is the least, the probability of lock conflict is the lowest, the concurrency is the highest.

(3) page Lock: Overhead and lock time are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.

Table-level locks are more suitable for applications that are primarily query-based and have only a small number of updates to the index criteria, such as Web applications, and row-level locks, only from the lock's point of view

It is more suitable for applications that have a large number of concurrent queries, such as some online transaction processing systems, that update a small amount of different data concurrently by index conditions.

One,MyISAM table lock

1. Querying table-level lock contention

Show status like ' table% ';

If the value of the table_locks_waited is higher, then there is a serious table-level lock contention situation.

2. mysql table-level lock Lock mode

There are two modes of table-level lock for MySQL: Table shared read lock and table exclusive write lock.

When a session is given a read lock on a table, the session can only access the locked table, and can only read operations;

The table reads, but the write operation is blocked and waits for the lock to be released. When a session adds a write lock to a table, the session can only

Access the lock of this table, you can read and write operations, the other session of the table read and write operations will be blocked, need to wait for the release of the lock.

There is a serial between the read and write operations of the MyISAM table and the write operation.

3. How to add a table lock

Read Lock: Lock table Tbl_name read;

Add write Lock: Lock table Tbl_name write;

Release lock: Unlock tables;

MyISAM will automatically add read locks to all tables involved before executing the query statement, and will automatically write locks to the table involved before performing the update operation.

This process does not require user intervention, so the user generally does not need to explicitly lock the MyISAM table with the lock Table command. Give the MyISAM table an explicit

Locking is generally done to simulate transactional operations in a certain degree, and to achieve consistent reads of multiple tables at a certain point in time.

Note that when you use lock tables, you not only need to lock all the tables that are used at a time, but also how many times the same table appears in the SQL statement.

It's going to be locked down many times with the same alias in the SQL statement, or else it will go wrong!

4. Concurrent Insertion

The MyISAM storage engine has a system variable Concurrent_insert that is specifically designed to control the behavior of its concurrent insertions, with values of 0, 1, or 2, respectively.

(1) When Concurrent_insert is set to 0 o'clock, concurrent insertions are not allowed.

(2) When Concurrent_insert is set to 1 o'clock, if there is no hole in the MyISAM table (that is, the row in the middle of the table is not deleted), MyISAM allows the

While one process reads a table, another process inserts records from the end of the table. This is also the default setting for MySQL.

(3) When Concurrent_insert is set to 2 o'clock, the record is allowed to be inserted concurrently at the end of the table, regardless of whether there is an empty hole in the MyISAM table.

Simply add the "local" option to the Add Table Lock command, which is: Lock table Tbl_name Local read, in case the MyISAM table concurrency Insert condition is met,

Other users can insert records concurrently at the end of the table, but the update operation is blocked, and the locked user cannot access records that were inserted concurrently by other users.

5. MyISAM Lock Dispatch

When both the write process and the read process request a write lock and read lock for the same MyISAM table, the write process takes precedence over the lock. Not only that, even if the read request first

The lock waits for the queue, the write request is followed, and the write lock is inserted before the read lock request! This is because MySQL considers writing requests to be generally more important than reading requests. And that's exactly what

The MyISAM table is not well suited for reasons that have a large number of update operations and query operations applied because a large number of update operations can cause query operations to be difficult to obtain read locks, thus

May block forever.

Adjust the scheduling behavior of the MyISAM by some settings:

(1) by specifying the startup parameter low-priority-updates, the MyISAM engine will default to the read request to give priority to the right.

(2) Reduce the priority of update requests issued by this connection by executing the command set Low_priority_updates=1.

(3) Reduce the priority of the statement by specifying the Low_priority property of the Insert, UPDATE, DELETE statement.

(4) Set a suitable value for the system parameter Max_write_lock_count, when the read lock of a table reaches this value, MySQL will temporarily write the request to the excellent

The first level is lowered, giving the reading process a chance to get a lock.

Second,InnoDB lock Problem

1. Querying for InnoDB Row lock contention

Show status like ' innodb_row_lock% ';

If the value of Innodb_row_lock_waits and Innodb_row_lock_time_avg is higher, it means that the lock contention is more serious and can be

InnoDB monitors to further observe the table, data row, and so on, and analyze the reason for lock contention.

Turn on the monitor:

CREATE TABLE Innodb_monitor (a INT) Engine=innodb;

Show InnoDB status\g;

To stop the Monitor:

DROP TABLE Innodb_monitor;

When the monitor is turned on, the monitored content is logged to the log every 15 seconds by default, and if open for a long time causes. err files to become very large,

So after you confirm the cause of the problem, remember to delete the monitoring table to turn off the monitor, or start the server by using the "--console" option to turn off write

Log files.

2. InnoDB Lock and Lock method

There are two types of row locks for InnoDB: Shared Lock (S) and exclusive lock (X). In order to allow row and table locks to coexist, to achieve a multi-granularity lock mechanism, INNODB also has

Two types of intent locks are used internally: intent shared and intent exclusive, both of which are table locks. Transactions must obtain the corresponding table before locking the data row

The corresponding intent lock.

The intent lock is innodb automatically and does not require user intervention. For update, DELETE, and INSERT statements, InnoDB automatically adds the data set to the

Exclusive Lock (X), InnoDB does not add any locks for normal SELECT statements, and transactions can be displayed to the recordset with shared or exclusive locks through the following statement.

Set autocommit=0;

Shared Lock (S): SELECT * FROM table_name WHERE ... LOCK in SHARE MODE.

Exclusive Lock (X): SELECT * FROM table_name WHERE ... For UPDATE.

Release lock: Unlock tables; (implicitly commits the transaction)

When a transaction obtains a shared lock on a table, other transactions can query the table's records or share locks on the record. When a transaction is made to a table

Update operation, if there is another transaction also in the table with a shared lock, you need to wait for the release of the lock, if another transaction also performed updates to the table

Causes a deadlock, another transaction exits, and the current transaction completes the update operation. When a transaction obtains an exclusive lock on a table, the other transaction can only

The table records are queried, no shared locks can be added, and records cannot be updated, and there is a wait.

3. InnoDB line Lock Implementation method

InnoDB a row lock is implemented by locking an index entry on an index, InnoDB this type of row lock implementation is characterized by:

(1) InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise InnoDB will use table locks.

(2) Since the MySQL row lock is for the index plus lock, not for the record plus lock, so although is to access the record, but if you are using

The same index key, there will be a lock conflict.

(3) When a table has multiple indexes, different transactions can use different indexes to lock different rows, in addition, whether using primary key index, unique index

or normal index, InnoDB uses row locks to lock the data. (Although different indexes are used, if the record has been locked by another session

The words also need to wait. ）

(4) Even if indexed fields are used in the condition, it is up to MySQL to determine the cost of different execution plans by using an index to retrieve the data.

If MySQL thinks that a full table scan is more efficient, such as a small table, it will not use an index, in which case the INNODB will use a table lock instead of

Row locks.

4. Gap Lock

When retrieving data using the range criteria, InnoDB also locks the record for the key value within the condition but does not exist, and the lock

It is called "clearance lock". The purpose of the INNODB is to prevent Phantom reading on the one hand, and to satisfy the need for recovery and replication on the other. But

This locking mechanism blocks concurrent insertions that meet key values within the range, causing severe lock waits, so you should try to avoid using scope conditions to retrieve data.

In addition to using a gap lock when locking with range conditions, InnoDB also uses a gap lock if an equal condition is requested to lock a nonexistent record!

5. The impact of the need for recovery and replication on the INNODB lock mechanism

MySQL implements the MySQL database by Binlog Record SQL statements that successfully update data such as INSERT, UPDATE, Delete, and so on.

Recovery and master-slave replication. The MySQL recovery mechanism (replication is actually in slave MySQL constantly doing binlog based recovery) has the following features:

(1) MySQL recovery is SQL statement level, that is, re-execute the SQL statement in Binlog.

(2) The MySQL Binlog is recorded in the order in which the transactions are submitted, and the recovery is carried out in this order.

So MySQL's recovery and replication requirements for the lock mechanism are that other concurrent transactions cannot be inserted to satisfy their locking criteria until a transaction is committed

of any record, that is, no phantom reads are allowed.

In addition, for general SELECT statements, MySQL uses multiple versions of data to achieve consistency without requiring any locks, however, for "insert

Into Target_tab select * from Source_tab where ... "and" CREATE TABLE New_tab ... From Source_tab where ... "this

SQL statement, the user does not do any update to Source_tab, but MySQL has done a special processing of this SQL statement, adding a shared lock to Source_tab.

This is because, without locking, if there is another transaction that has been updated and committed before the SQL statement is executed, the Source_tab

Binlog, the location of the update operation is preceded by the SQL statement, which uses this binlog for database recovery, and the result of the recovery is related to the actual

Logically inconsistent, replication results in a master-slave database inconsistency. Because actually applying the data inserted in Target_tab or New_tab is another transaction

The data before the Source_tab update, and the Binlog record is the first to update and then execute the Select...insert ... Statement. If the select of the above statement is a fan

The InnoDB also adds a gap lock to the source table. So this SQL statement blocks concurrent updates to the original table and should be avoided as much as possible.

6. InnoDB Use of table lock and precautions

For InnoDB tables, row-level locks should be used in most cases, but table-level locks can also be considered in individual special transactions, mainly

There are two scenarios:

(1) The transaction needs to update most or all of the data, the table is relatively large, if the use of the default row lock, not only this transaction execution inefficient, and may create

To other transactions for long-time lock waits and lock collisions, in which case you might consider using table locks to increase the execution speed of the transaction.

(2) transactions involving more than one table, more complex, it is likely to cause deadlocks, resulting in a large number of transaction rollback. This can also be considered in the case of a one-time locking transaction involving

Tables to avoid deadlocks and reduce the cost of the database due to transaction rollback.

In addition, the following two points need to be noted for using table locks in InnoDB:

(1) Using lock tables Although you can add a table-level lock to InnoDB, the table lock is not managed by the InnoDB storage engine layer, but by its previous layer ──mysql

Server is responsible, only if autocommit=0, Innodb_table_locks=1 (the default setting), the InnoDB layer to know the MySQL plus table lock, MySQL

The server is also aware of InnoDB row locks, in which case the InnoDB can automatically identify deadlocks that involve table-level locks; otherwise, InnoDB will not be automatically detected

and handle this deadlock.

(2) When using the lock TABLES to InnoDB table lock, you should be aware that the autocommit is set to 0, or MySQL will not add locks to the table; Before the end of the transaction,

Do not release the table lock with unlock TABLES because unlock TABLES commits the transaction implicitly; commit or rollback does not release with lock TABLES

The table lock must be released with unlock TABLES.

7. About Deadlocks

The MyISAM table lock is deadlock free, because MyISAM always gets all the locks needed at once, either all satisfied, or waits, so it does not

A deadlock occurs. In InnoDB, however, in addition to a single SQL-composed transaction, the lock is progressively obtained, which determines that deadlocks are possible in InnoDB.

After a deadlock occurs, InnoDB is typically automatically detected, and a transaction is freed and rolled back, another transaction gets locked, and the transaction continues to complete. But in

In cases involving an external lock or a table lock, InnoDB does not automatically detect the deadlock, which requires the lock wait timeout parameter to be set

Innodb_lock_wait_timeout to solve.

In general, deadlocks are a problem with application design by adjusting the business process, database object design, transaction size, and the SQL that accesses the database

Statements, most deadlocks can be avoided. The following is an example of several common ways to avoid deadlocks.

(1) In the application, if the different programs will concurrently access multiple tables, should try to agree to access the table in the same order, which can greatly reduce the resulting deadlock

The opportunity.

(2) In the process of batch processing of data, if the data is sorted beforehand, to ensure that each thread in a fixed order to process the records, can also greatly

Reduce the possibility of deadlocks.

(3) In the transaction, if you want to update the record, you should request a sufficient level of lock, that is, an exclusive lock, instead of requesting a shared lock, update and then request an exclusive lock,

Because when a user requests an exclusive lock, other transactions may have acquired a shared lock of the same record, resulting in a lock conflict or even deadlock.

(4) Under Repeatable-read isolation level, if two threads simultaneously record the same condition with select ... For update plus exclusive lock, without

If the condition is logged, two threads will be locked successfully. The program found that the record does not yet exist and tries to insert a new record if two threads are so

Do, there will be deadlocks. In this case, you can avoid the problem by changing the isolation level to read COMMITTED.

(5) When the isolation level is read Committed, if two threads first Execute select ... For UPDATE, determine if there is a qualifying record,

If not, insert the record. At this point, only one line Cheng Nen is inserted successfully, another thread will have a lock wait, and when the 1th thread commits, the 2nd line routines

There was an error in the primary key, but even though the thread went wrong, it got an exclusive lock! If there is a 3rd thread to apply for an exclusive lock, there will be a deadlock.

In this case, you can do the insert operation directly before capturing the primary key weight exception, or when you encounter a primary key error, always perform a rollback release to get

The exclusive lock.

MySQL lock problem