MySQL's business, lock

Lockfirst, the concept

A lock is a mechanism by which a computer coordinates multiple processes or threads accessing a resource. In the database, the data is a resource shared by many users in addition to the contention of traditional computing resources (CPU, RAM, IO). How to guarantee the consistency and validity of data concurrent access is a problem that all databases must solve, and lock conflict is also an important factor to affect the database concurrent access. From this point of view, locks are especially important and more complex to the database. In this paper, MyISAM and InnoDB two engine for the problem of the lock;

Second, MySQL lock overview

Compared to other databases, MySQL's locking mechanism is relatively simple, the most notable feature is that different engines have different locks. For example, MyISAM and InnoDB use table lock, row lock, but InnoDB also support table lock, by default, row lock;

third, MySQL table lock and row lock comparison

Name of the lock	Overhead	Lock speed	Dead lock	Particle size	Concurrency performance
Table lock	Small	Fast	does not appear	Big	Minimum
Row lock	Big	Slow	Appear	Small	Highest

From the lock point of view, the table lock is suitable for the main query, only a small number of applications to update the data by index criteria, row lock is suitable for a large number of index conditions to update a small amount of data concurrently, while the application of concurrent queries;

MyISAM table LockFirst, query table-level lock contention situation

Table_locks_waited and table_locks_immediate can be used to analyze the contention of the system table lock;

If the value of the table_locks_waited is higher, then there is a high table lock contention.

Second, the lock mode of the table lock

Table-level locks are available in two ways: Read lock and Write lock, and the compatibility of lock mode is as follows:

request lock mode compatible with current lock mode	read lock	write lock
read lock	is	no
write lock	no	no

As can be seen from the table above, the read operation of the MyISAM table does not block other users from reading requests to the same table, but it blocks the table's write requests, and for MyISAM writes, it blocks other users from reading and writing to the same table. Between the read and write operations of the MyISAM, the write operation and the write operation are serial. That is, when a thread obtains a write lock on a table, only the thread holding the lock can perform the update operation, and the other thread reads and writes until the lock is released.

Third, how to add the table lock

MyISAM will automatically add read locks to all tables involved before executing the query statement (SELECT), and will automatically write locks to the table involved before performing the update operation (update, DELETE, INSERT, etc.), which does not require user intervention, so Users generally do not need to explicitly lock the MyISAM table directly with the Lock Table command.

To the MyISAM table plus display lock, is generally to a certain extent to simulate transaction operations, to achieve a point in time a large number of tables consistent read. For example, we now have a stock table and a list of inbound, we want to determine whether the total number of warehousing and inventory table is consistent, this time we do not lock the two tables, it may produce incorrect results, such as in the first query inventory, the storage list has changed. At this time, data errors are caused.

Make a summary of the above:

1, the above example in lock tables added the "local" option, its role is to meet the MyISAM table concurrent insertion conditions, allowing other users to insert records at the end of the table;

2, when using lock tables to give the table explicit table lock, you must also get all the locks involved in the table, and MySQL does not support lock escalation. That is, after the lock tables is executed, only those tables that are explicitly locked can be accessed, the unlocked tables cannot be accessed, and if a read lock is added, only the query operation can be performed and the update operation cannot be performed. In fact, in the case of automatic lock-up, the MyISAM always get all the locks required by the SQL statement at once. This is why the MyISAM table does not appear to be deadlocked.

Iv. Concurrent Insertion

The MyISAM storage engine has a system variable Concurrent_insert that is specifically designed to control the behavior of concurrent insertions, with values of 0,1,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 one process to read the table while 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.

Five, the MyISAM lock dispatch

　　The read and write locks of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial. For example, there is now a process to read the lock of a table, this is a process to request the same table of the write lock, this time the MyISAM table will do? The result is a write lock to get first, not only that, if the read lock into the queue first, write the lock into the queue, this time is also write lock spot. This is because MySQL thinks writing is more important than reading. This is why the MyISAM table is not very suitable for a number of update operations and query operations. This can be very bad because of the large number of update operations where the read operation is difficult to obtain a lock, which may block forever. But we can set the scheduling behavior of MyISAM.

1, by specifying the startup parameters Low-priority-updates, so that the MyISAM engine default to read requests to priority rights. 2. By executing the command set Low_priority_updates=1, the priority for update requests made by this connection is reduced. 3. Reduce the priority of the statement by specifying the Low_priority property of the Insert, UPDATE, DELETE statement. 4, to the system parameters Max_write_lock_countSetting an appropriate value, when the read lock of a table reaches this value, MySQL temporarily lowers the priority of the write request, giving the read process a chance to lock. 5, some long-running query operations, will also cause the write process to block. Vi. when to use a table lock

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 the transaction is inefficient, and may cause other transactions long time lock wait and lock conflict, in this case, you can consider using table locks to improve the execution speed of the transaction.

2, transactions involving multiple tables, more complex, it is likely to cause deadlocks, resulting in a large number of transaction rollback. It is also possible to consider the tables involved in a one-time locking transaction, thus avoiding deadlocks and reducing the cost of the database due to transaction rollback.

InnoDB Lock

　　The difference between InnoDB and MyISAM is that InnoDB supports transactional and row-level locks. There are many different places for row-level locks and table locks, and the introduction of transactions introduces some new problems.

first, the business1, the characteristics of the transaction

　　atomicity : changes to the data, either all succeed, or all fail.

Consistency: The data must be in a consistent state when the transaction begins and when it is completed.

　　isolation : Ensures that transactions are performed in a "standalone" environment that is not affected by external concurrency operations. That is, the middle part of the transaction processing process is not visible.

Persistence: After the transaction is complete, his modifications to the data are permanent, i.e. the system fails and can be persisted.

2. Problems caused by concurrent transactions

　　Update lost: Last update covers updates made by other firms. The condition for this problem is that the current user is not allowed to operate without committing the transaction.

Dirty reads: A transaction is making changes to a record, and the transaction is likely to read dirty data before it completes the commit and another transaction reads the data from that line.

Non-REPEATABLE READ: A transaction reads the previously read data at some time after reading some data, but finds that the data it has read has changed, or some records have been deleted!

Magic: A transaction re-reads the previously retrieved data in the same query condition, but finds that other transactions have inserted new data that satisfies its query criteria.

3, the isolation level of the transaction

　A, update loss should be completely avoidable, but to prevent the loss of updates, can not be solved by database transaction control alone, the application needs to lock up the updated data to solve;

B, "Dirty reading", "non-repeatable reading" and "phantom reading" are all database read consistency problems, must be provided by the database to provide a certain transaction isolation mechanism to solve

Isolation level	Read Data consistency	Dirty Read	Non-REPEATABLE READ	Phantom reading
Uncommitted read (READ UNCOMMITTED)	Lowest level, only guaranteed not to read physically corrupted data	Is	Is	Is
Submitted degrees (Read committed)	Statement-level	Whether	Is	Is
REPEATABLE READ (Repeatable Read)	Transaction-level	Whether	Whether	Is
Serializable (Serializable)	Highest level, transaction level	Whether	Whether	Whether

4. InnoDB lock mode and lock method

　　

Lock type	Readable (other transactions)	Writable (Other transactions)
Shared Lock (S)	Is	Whether
Exclusive Lock (x)	Whether	Whether

For both row and table locks, InnoDB provides two intent locks (i.e., table locks)

　　Intent shared Lock (IS): The transaction intends to add a row of shared locks to the data row, and the transaction must obtain the IS lock of the table before sharing it with a data row.

Intent exclusive Lock (ix): The transaction intends to add an exclusive lock to the data row, and the transaction must obtain an IX lock on the table before adding an exclusive lock to the data row.

The compatibility between the four types of locks is as follows:

Lock mode	S	X	Is	Ix
S	Compatible	Conflict	Compatible	Conflict
X	Conflict	Conflict	Conflict	Conflict
Is	Compatible	Conflict	Compatible	Compatible
Ix	Conflict	Conflict	Compatible	Compatible

If the lock mode of a transaction request is compatible with the current lock, INNODB grants the requested lock to the transaction and, conversely, if the two are incompatible, the transaction waits for the lock to be released.

The intent Lock is innodb automatically and does not require user intervention. For update, Delete, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and InnoDB does not add any locks for the normal SELECT statement;5, the implementation of InnoDB line lock

　　The InnoDB implementation of a row lock is achieved by locking the index entry on the index, or by using a row-level lock for InnoDB only if the data is retrieved through an index, otherwise InnoDB will use a table lock.

Note the point:

A, the InnoDB does use a table lock instead of a row lock when querying without an index condition.

B, because the MySQL row lock is for the index plus lock, not for the record plus the lock, so although it is access to the record, but if you use the same index key, there will be a lock conflict.

C. When a table has multiple indexes, different transactions can use different indexes to lock different rows, and InnoDB uses row locks to lock the data, whether using a primary key index, a unique index, or a normal index.

D, even if the index field is used in the condition, but whether the index is used to retrieve the data is determined by MySQL by judging the cost of different execution plans, if MySQL thinks the full table scan is more efficient, such as for some very small tables, it will not use the index, in which case InnoDB will use table lock, Instead of a row lock. Therefore, when parsing a lock conflict, don't forget to check the SQL execution plan to verify that the index is actually used.

6. Clearance Lock

　　When we retrieve data with a range condition rather than an equal condition, and request a shared or exclusive lock, InnoDB locks the index entry for the qualifying existing data record, and for a record in which the key value is within the condition but does not exist, called "gap", InnoDB also locks the "gap". This locking mechanism is called a gap lock (Next-key lock).

Purpose

　　InnoDB the purpose of using gap locks, on the one hand is to prevent Phantom reading to meet the requirements of the relevant isolation level, if you do not use a gap lock, if the other transaction inserted more than the scope of any record, then this transaction if the above statement is executed again, a phantom reading will occur, on the other hand, to meet its recovery and replication needs.

　Obviously, when using scope conditions to retrieve and lock records, the INNODB locking mechanism blocks concurrent insertions that match the key values within the range, which often results in severe lock waits. Therefore, in the actual application development, especially the concurrent inserting more and more applications, we should try to optimize the business logic, try to use equal conditions to access the updated data, to avoid the use of scope conditions.

dead Lock

　　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. However, when an external lock is involved or a table lock is involved, INNODB does not fully detect the deadlock, which needs to be resolved by setting the lock wait timeout parameter innodb_lock_wait_timeout. It is necessary to note that this parameter is not only to solve the deadlock problem, if the concurrent access is high, if a large number of transactions due to the inability to obtain the required locks immediately suspended, it can consume a lot of computer resources, causing serious performance problems, or even drag across the database. We can prevent this from happening by setting the appropriate lock wait timeout threshold.

In general, deadlocks are an application design problem, and most deadlocks can be avoided by adjusting the business process, database object design, transaction size, and SQL statements that access the database. The following is an example of several common ways to avoid deadlocks.

　1, in the application, if the different programs will access multiple tables concurrently, should try to agree to access the table in the same order, which can greatly reduce the chance of deadlock generation.

2, in the process of batch processing of data, if the data sorted in advance, 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 apply for 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 the user requests an exclusive lock, other transactions may have acquired the same record of the shared lock, 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 an exclusive lock, two threads will be locked successfully if the condition record is not met. The program found that the record does not yet exist and tries to insert a new record, and if two threads do so, a deadlock occurs. 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 are executed first select ... For UPDATE, determine if there is a record that matches the criteria, and if not, insert the record. At this point, only one line Cheng Nen Insert succeeds, the other thread will have a lock wait, when the 1th thread commits, the 2nd thread will be wrong because of the primary key, but although this thread is wrong, it will get an exclusive lock! If there is a 3rd thread to apply for an exclusive lock, there will be a deadlock.

MySQL's business, lock