MySQL lock mechanism and transaction ISOLATION level

MyISAM lock Mechanism:MyISAM only table locks, divided into X and S lock two (or called read Lock,write Lock). Read Plus s lock, write plus x lock. Blocking each other, so concurrent DML performance is not good. InnoDB lock Mechanism:

Lock Table/tables Tab_name read/write; --Manual locking statement: (same applies to myisam) unlock tables;  --The Unlock statement set @ @global. Innodb_status_output_locks=on; -So show engine InnoDB status\g can display additional lock information, only the number of locks is displayed in standard cases.

One, InnoDB lock mode (row lock)The locks mentioned below are only available when a row lock is used, and if you do not use an index, then InnoDB will use a table lock instead of a row lock. 1. Two types of row locks:shared Lock (S): allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set. exclusive Lock (X): transactions that are allowed to obtain exclusive locks update data, but prevent other transactions from acquiring shared and exclusive locks of the same data set. 2. Two kinds of Intent table lock:before adding a lock, you should first add a table-level intent Lock:Intent Shared Lock (IS): indicates that a transaction is ready to join a shared lock on a data row, which means that a data row is locked before a shared lock must be obtained for that table. Intent Exclusive Lock (IX): Similar to the above, the transaction is prepared to add an exclusive lock to the data row, stating that the transaction must obtain an IX lock on the table before a data row is added to the exclusive lock. 3. The range of the row lock ( can be s or x lock ):

• Record Lock: A row lock that locks a single row record.
  
• Gap Lock: Gap Lock, lock a range, but does not include the record itself, the purpose of the gap lock is to prevent phantom reading.
  
• Next-key Lock: Lock + gap lock.

The above talk about the type of row lock that InnoDB provides, and when it comes to lock mode it must be done under the concept of the transaction isolation level, otherwise meaningless. under the default thing isolation level, InnoDB uses a row lock only if the DML statement and the Select (for Update/lock in share mode) statement use the index, otherwise table locks are used. under the default thing isolation level, the use of row locks is divided into 2 cases:

1. Access to ordinary indexes uses next-key locks, that is, gaps in the upper and lower bounds of the locked row and index key values, which do not allow gap insertion and prevent phantom reads.
   
2. For single-row access to an index with a unique property, the rows lock is used to lock only the rows to be accessed. The exception is that if you access a value that does not exist, the gap row lock is also generated, and the range query uses the Next-key row lock.
   

in the Read Committed isolation level,the Delete/update or select (for Update/lock in share mode) operation does not add a gap lock, only the record lock is added, So there will be a phantom read to produce.   Second, the lock mode of INNODB under the isolation level of each transactionabout the MYQL transaction isolation level see:https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html

SET [GLOBAL | SESSION] TRANSACTION Isolation level| Repeatable read| READ committed| READ uncommitted| SERIALIZABLE

1. SERIALIZABLE This isolation level is similar to repeatable READ, and if autocommit is set to disable, then InnoDB implicitly converts all SELECT statements to select Lock in Share mode, but if Autocommit is set to enable, then select Self-service, and its lock mode is also consistent with repeatable read. The delete/update operation is also consistent with the lock mode under repeatable read. 2. Repeatable READ (default) At this isolation level, the database takes a transaction-level MVCC snapshot mechanism, and the select operation reads transaction-level snapshots without locking and table locks, while avoiding dirty reads, non-repeatable reads, and Phantom reads. For delete/update or select (for Update/lock in share mode) operations, if you use an index, add row locks in two cases, as described above. This level of isolation is similar to the snapshot isolation level of SQL Server. The ISO standard definition does not avoid phantom reading under this isolation level, but each database vendor directly makes the SELECT statement not lock the resource with the MVCC mechanism, thus avoiding phantom reading. 3. READ COMMITTEDAt This level of isolation, the database uses a statement-level MVCC snapshot mechanism to avoid dirty reads and non-repeatable reads, but inevitably phantom reads. The SELECT statement locks the data table with only Canadian dollar data and does not add locks and table locks. delete/update or select (for Update/lock in share mode) operation if the index is used, then no gap lock is added, only the record lock is created, so there is a phantom read. Ps: Note Binlog_format must use row mode under this isolation level! 4. READ UncommittedIn This isolation level, the database does not take the MVCC snapshot mechanism, dirty reads, and the SELECT statement is not locked. for delete/update or select (for Update/lock in share mode) operation, the locking mechanism READ committed consistent.
for a more detailed description of the various SQL statement locking instructions see:https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.htmlFor a description of MySQL transaction-level consistency read and statement-level consistency reading, refer to:http://www.cnblogs.com/digdeep/p/4947694.html Three, Summary: You can see that the InnoDB implements select without blocking writes under the REPEATABLE read and read Committed isolation levels through the MVCC mechanism. However, at the repeatable read isolation level , it is also possible to avoid non-repeatable reads and Phantom reads due to transactional-level consistent reads. This is actually the difference between the isolation level of the other database and the dirty read, non-repeatable read, and Phantom read. For example , the corresponding relationship between isolation level and exception read in SQL Server is as follows:   Iv. INSERT statementall of the above-mentioned lock modes and transaction isolation levels ignore the INSERT statement, so what is the lock form of the INSERT statement under the four isolation levels? The following pages are available on the website:https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

insert sets an exclusive lock on the inserted row. This lock was an index-record lock, not a next-key lock (which is, there was no gap lock) and does not 
 prevent other sess Ions from inserting into the gap before the inserted row. Prior to inserting the row, a type of gap lock called an insert intention gap lock is set.  This lock signals the intent to insert in such a-to-a-multiple transactions inserting into the same index gap need Not wait for each of the other if they is not inserting at the same position within the gap. Suppose that there is index records with values of 4 and 7.  Separate transactions that attempt to inserts values of 5 and 6 each lock the gap between 
 4 and 7 with insert intention Locks prior to obtaining the exclusive lock on the inserted row, but does not block each other because the rows is nonconf Licting. 

first, we need to make clear that the InnoDB table must have a primary key, whether explicit or implicit, the system customizes the primary key, which means that the insert must use the primary key index, and then look at the following:The core meaning of this paragraph is that the INSERT statement only adds an index record lock, does not add a gap lock, so the insert different primary keys do not block each other. Because the INSERT statement is a DML statement, it can be assumed that the lock mode is the same at all isolation levels, that is, the table-level IX lock is added first, plus the record lock for the X-mode of the inserted row.

MySQL lock mechanism and transaction ISOLATION level