mysql-innodb-Lock

Before InnoDB lock, why start transaction

InnoDB the release of a lock after a transaction commit/rollback, once the transaction commits/rolls back, the lock in the transaction is automatically freed, InnoDB automatically commits by default Autocommit=1

Search criteria use indexes and lock differences that do not use indexes:

A specific row is locked when the retrieval condition has an index.

Full table scan is performed without using the search condition, thus locking all rows (including non-existent records)

Read Lock:

Read locks are shared, or are not blocked from each other. Multiple users can simultaneously read the same resource at the same time without interfering with each other.

Write Lock:

Write locks are exclusive, meaning that a write lock blocks other write and read locks. In addition, the write lock has a higher priority than the read lock, so a write lock request may be inserted in front of the read lock queue, but the read lock will not be able to be inserted in front of the write lock

Table Lock:

　　InnoDB also has two table locks: Intent Shared lock (IS), intent exclusive Lock (IX)

Row Lock:

　　InnoDB implements two types of frontal row level locks, shared locks and exclusive locks

Optimistic Lock:

Optimistic locking, also known as optimistic concurrency control, which assumes that multi-user concurrent transactions do not interact with each other during processing, and that each transaction can handle the portion of the data that is affected without a lock being generated. Before data updates are committed, each transaction checks that the data has been read by the transaction and that no other transaction has modified it. If other transactions are updated, the transaction that is currently being committed is rolled back.

Pessimistic Lock:

Pessimistic lock, also called pessimistic concurrency control, when transaction A has a lock applied to a row of data, and when the transaction has freed the lock, the other transaction is able to perform the operation with that lock, where the lock imposed by transaction A is called a pessimistic lock. Lock and exclusive lock (row, Gap, Next-key Lock) are pessimistic locks

Pessimistic lock and optimistic lock implementation mode:

Pessimistic lock implementation relies on the lock mechanism provided by the database, such as the SELECT * from news where the id=12 for update, and optimistic locking depends on the record data version to implement, that is, by adding a version number field in the table as a key factor for the successful submission.

Shared Lock (S):

A shared lock is also called a read lock, a transaction acquires a shared lock on a row of data, another transaction acquires a shared lock on that row, but cannot obtain an exclusive lock, that is, when a transaction reads a row of data, other transactions are readable, but the data row cannot be increased

Set shared Lock: SELECT .... LOCK in SHARE MODE;

Exclusive Lock (X):

The lock is also called a write lock, a transaction acquires an exclusive lock on a data row, other transactions can no longer acquire another lock on the row (exclusive or shared), that is, when a transaction reads a row of data, other transactions cannot make the data row

Set exclusive lock: SELECT .... For UPDATE

　　Note the point:

• For SELECT statements, InnoDB does not add any locks, that is, multiple concurrent select operations can be performed without any lock conflicts, because there is no lock at all.
• For insert,update,delete operations, InnoDB automatically adds exclusive locks to the data involved, and only query select requires us to set the exclusive lock manually.

Intent shared Lock (IS):

Notifies the database what locks need to be applied next and locks the table. If a shared lock is required for record A, then InnoDB will find the table first, and then add the shared lock to record a after the table is added with intent to share the lock. That is, a data row must be locked before a shared lock is acquired.

Intent exclusive Lock (IX):

Notifies the database what locks need to be applied next and locks the table. If you need to add an exclusive lock to record A, then InnoDB will find this table first, after the table is intent on an exclusive lock, and then a shared lock on record a. In other words, a data row must first obtain the IX lock of the table before it is added to the lock.

Shared and intent shared locks, the difference between an exclusive lock and an intent exclusive lock:

• Shared and exclusive locks, the system will automatically add shared or exclusive locks under certain conditions, or you can manually add shared or exclusive locks.
• Both intent-sharing and intent-exclusive locks are automatically added and automatically released by the system, and the entire process requires no manual intervention.
• Shared and exclusive locks are row records of locks, and intent-shared and intent-exclusive locks are locked by tables.

How locks are implemented:

In MySQL, a row-level lock is not a direct lock record, but a lock index. Indexes are primary key index and non-primary key index Two, if a SQL statement operation primary Key index, MySQL will lock the primary key index, if a statement operation non-primary key index, MySQL will first lock the non-primary key index, and then lock the relevant primary key index .

InnoDB a row lock is implemented by locking an index entry, and if there is no index, InnoDB locks the record through a hidden clustered index. That is: If the data is not retrieved by index criteria, then InnoDB will lock all the data in the table, with the actual effect being the same as the table lock

Row locks are divided into three cases:

Record Lock: Locks a record by locking the index entry.

Gap Lock: Locks a range of records, not including the record itself, between the ' gap ' between index items, the gap before the first record, or the gap after the last record

Next-key Lock: Locks a range of records and includes the record itself (the combination of the two above)

Note:The InnoDB default level is the Repeatable-read (repeat read) level. Ansi/ios SQL standard defines 4 transaction isolation levels: uncommitted read (READ UNCOMMITTED), Read Committed, repeat read (repeatable read), serial read (SERIALIZABLE)

The difference between GAP lock and Next-key Lock:

　　Next-key Lock is a combination of a row lock and a gap lock, so that when InnoDB scans the index record, the selected index record is preceded by a row lock (record Lock), and a gap lock is added to the gap on both sides of the index record (Gap Lock). If a gap is locked by the transaction T1, other transactions cannot be inserted into the record at this interval.

Row locks prevent other transactions from being modified or deleted, gap locks prevent other transactions from being added, and the Next-key locks formed by the combination of row and gap locks together solve the problem of the RR interface's Phantom reading when writing data.

When to use table locks in InnoDB:

InnoDB uses row-level locks in most cases, because transaction and row locks are often the reason we choose InnoDB, but in some cases we also consider using table-level locks

• When the transaction needs to update most of the data, the table is also larger, if you use the default row lock, not only inefficient, but also easy to cause other transactions long wait and lock conflict.
• Transactions are more complex and may cause deadlocks to cause rollback.

Under InnoDB, the following two points should be noted for using table locks. (1) using lock Talbes Although it is possible to add a table-level lock to InnoDB, it must be stated thattable locks are not managed by the InnoDB storage engine layer，instead, it is the last layer of MySQL server, only if autocommit=0, innodb_table_lock=1 (the default setting), the InnoDB layer to know the MySQL plus table lock, MySQL server can sense InnoDB plus row lock, in this case,    InnoDB can automatically identify deadlocks that involve table-level locks, otherwise innodb will not be able to automatically detect and process such deadlocks. (2) in the use of Locak tables to InnoDB lock should be noted that the autocommit is set to 0, or MySQL will not add locks to the table; Before the transaction ends, do not use Unlocak tables to release the table lock, because unlock tables implicitly commits the transaction Commit or rollback cannot release a table-level lock added with Locak tables, you must use unlock tables to release the table lock, the correct way to see the following: For example: If you need to write the table T1 and read from the table T

SET Autocommit=0READ, ...; [dosomething with tables T1and here] ; COMMIT ; UNLOCK TABLES;

Deadlock:

We have said that there is no deadlock in MyISAM, because MyISAM always get all the locks they need, either all of them, or all waiting. In the InnoDB, the lock is gradually obtained, resulting in the possibility of deadlock.

After a deadlock occurs, InnoDB can generally detect and cause one transaction to release the lock fallback, and the other to acquire the lock to complete the transaction. However, when an external lock is involved, or a lock is involved, the InnoDB does not automatically 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 used to solve the deadlock problem, if the concurrent access is high, if a large number of transactions due to the inability to get the required locks immediately suspended, can consume a lot of computer resources, causing serious performance problems, or even drag down the database. We can prevent this from happening by setting the appropriate lock wait timeout threshold.

There are several ways to avoid deadlocks, here are three common types of:

1. If multiple tables are accessed concurrently by different programs, it is possible to significantly reduce deadlock opportunities by agreeing to access the tables in the same order. If the order of two session accesses two tables is different, the chance of deadlock is very high! However, deadlocks can be avoided if accessed in the same order.
2. In the same transaction, it is possible to lock all resources needed at once, reducing the probability of deadlock generation.
3. For business segments that are very prone to deadlocks, you can try using upgrade lock granularity to reduce deadlock generation by table-level locking.
4. When the program processes data in batches, it can greatly reduce the likelihood of deadlocks if the data is sorted beforehand to ensure that each thread processes the records in a fixed order .
5. Under the Repeateable-read isolation level, if two threads simultaneously record the same condition with select ... ROR Update plus an exclusive lock, two threads will be locked successfully if the 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.
6. When the isolation level is read commited, if two threads first perform a 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. In this case, you can do the insert operation directly before capturing the primary key weight exception, or when encountering a primary key weight error, always perform an exclusive lock obtained by the rollback release

　　　PS: If there is a deadlock, you can use the show INNODB Status command to determine the cause of the last deadlock and the improvement measures.

Summary: For InnoDBTable, the main points are as follows (1) InnoDB's marketing is based on index implementations, and InnoDB uses table locks if data is not accessed through the index.    (2) InnoDB clearance lock mechanism, and the reason why the InnoDB use Gap lock.    (3) Under different isolation levels, the INNODB lock mechanism differs from the consistent read strategy.    (4) MySQL recovery and replication have a significant impact on the INNODB lock mechanism and the consistent read strategy.    　　 (5) lock conflicts and even deadlocks are difficult to avoid altogether. After understanding the lock characteristics of the InnoDB, users can reduce lock collisions and deadlocks through such measures as design and SQL tuning, including:

• Try to use a lower isolation level
• Carefully design indexes and use indexes to access data to make locks more precise, thereby reducing the chance of lock collisions.
• Choose a reasonable transaction size, and the chances of a small transaction locking conflict are also smaller.
• When displaying locking to a Recordset, it is a good idea to request a sufficient level of locks at once. For example, to modify the data, it is best to apply for an exclusive lock, rather than apply for a shared lock, modify and then request an exclusive lock, which is prone to deadlock.
• When different programs access a set of tables, it is important to agree to access the tables in the same order as possible, and to access the rows in the table in a fixed order for a table. This can greatly reduce the chance of deadlock.
• Try to access the data with equal conditions to avoid the effect of a gap lock on concurrent insertions.
• Do not request a lock level that exceeds the actual need, and do not display a lock on the query unless necessary.
• For some specific transactions, table locks can be used to improve processing speed or to reduce the likelihood of deadlocks.

Reference documents:

[1] Baron Schwartz, Ninghai Yuanhao and other translations; "High-performance MySQL" (3rd edition); electronics Industry Press, 2013

[2] Jane book blog, HTTP://WWW.JIANSHU.COM/P/A40F28DC29CD

[3] CSDN Blog, http://blog.csdn.net/zhanghongzheng3213/article/details/51721903

[4] csdn blog, http://blog.csdn.net/dong976209075/article/details/8802778

[5] csdn blog, http://www.cnblogs.com/chenqionghe/p/4845693.html

[6] csdn blog, http://blog.csdn.net/psongchao/article/details/776172

[7] csdn blog, http://blog.csdn.net/zhanghongzheng3213/article/details/51753010

[8] Official documents, https://dev.mysql.com/doc/refman/5.6/en/lock-tables.html

mysql-innodb-Lock