InnoDB Learning notes for indexes and locks

Appendix: The previous period of time to learn the next InnoDB lock related knowledge, lock and transaction has a general understanding, here do a small summary.

1.INNODB Transaction and lock relationships.

The two features that distinguish InnoDB from MyISAM are InnoDB support for transactions and support for row locks. The transaction requires the acid characteristics of a set of SQL statements, and in order to avoid concurrent updates to a row of records, the InnoDB itself is locked under certain circumstances, and then the lock is released after the transaction where the statement is located (Rollbak or commit). In fact, when Autocommit=true, a SQL itself is a transaction.

InnoDB Write locks (exclusive locks, plus exclusive locks, no more shared and exclusive locks) when executing update,delete,insert. The SELECT statement does not lock the record.

shared Lock: SELECT * FROM table_name WHERE ... LOCK in SHARE MODE.

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

Example:

, the left transaction has an exclusive lock (for update), the right-side transaction direct query does not wait (no lock), the exclusive lock fails, and the shared lock fails (lock in share mode). Release the lock after the left transaction commits or rolls back.

The 2.Innodb row lock feature.

InnoDB uses row locks to lock records, which means that the specific rows accessed will be locked under lock conditions without locking the entire table. This can greatly improve the throughput of table access and reduce unnecessary record locks. InnoDB's row locks are implemented using indexes, so there are a few things to note:

• if the data accessed by a lock statement can not be indexed or not indexed, in fact, he locked the whole table, this should be noted.
• if the index uses a range, then he locks the hit record and locks the non-existent record gap to prevent insertion (this line does not exist), Gap lock.

         

The left transaction locks the age>3 record, so it is natural that the age=4 record will be locked. In the right-hand side of the transaction, a age=5 record is inserted, and the insert operation is stuck, which is the gap lock. Gap Lock is to avoid the occurrence of Phantom read, that is, a transaction batch update condition=x record, and B transaction into a condition=x record, then a transaction commits, found that there is still a record of no update, seemingly there is a hallucination, this is the Phantom read.

Because the gap lock appears to prevent insertions, update age=5 does not lock the table because the record does not exist--.

• if two queries go through different indexes but need to process the same record, the lock will be contested.
• Although the records of two queries are different, but the same index is going, the lock will still be competitive.

For example:

Learntransaction table in the age index, the left window query age=122 and id=6 records, the right window query age=122 and id=3 records, you can find the record and different, but there is a lock competition, Since two are common one index age=122.

• When the query field of the where statement involves multiple indexes, MySQL optimizes SQL to decide which index to go, so if the ID is also indexed, then it is in the where statement that the age is in front, but not necessarily the index of the age to walk the index of the ID. So this time for how to lock to see the use of specific indexes, it is recommended to use the Explain tool query execution plan judgment.

It is important to note that slow queries cause the database to hang and not necessarily because of a competition lock, slow queries may be due to too many records to update, resulting in SQL delay, and InnoDB default 32 slots, which is 32 concurrent worker threads, When 32 threads are running and cannot receive new SQL, the data may be stuck, regardless of the lock.

Resources:

"mysql--database development, optimization and management maintenance" http://book.51cto.com/art/200803/68127.htm

InnoDB Learning notes for indexes and locks