MySQL chooses what to think before you can read it repeatedly.

Original address: http://blog.itpub.net/29254281/viewspace-1398273/

MySQL chooses what to think before you can read the isolation level repeatedly.
Gap Lock

MySQL before use There are three must be aware of: (with the depth of understanding this is highly likely to increase again.) The
1.DDL causes metadata lock, which causes the request to block or even query requests. The Flush Table command for
http://blog.itpub.net/29254281/viewspace-1383193/

2.MySQLDump and xtrabackup causes waiting for Table serial blocking also blocks query requests.
http://blog.itpub.net/29254281/viewspace-1392757/

3. Select the REPEATABLE read transaction isolation level, which opens the gap lock. He locks more than is actually needed and is likely to cause deadlocks.

This article is dengfa into the great God article of the review and summary. The
links are as follows:
http://hedengcheng.com/?p=771
(not one of the best articles on this)

Oracle and MySQL (read commit and REPEATABLE Read) both implement MVCC multiple versioning concurrency control.
This means that normal select (consistent read or snapshot read) can read data in a non-locked form.

and the current read (Oracle's DB block gets) needs to be locked.
For example:
SELECT * FROM table where-lock in share mode;
SELECT * FROM table where? for update;
INSERT into table values (...);
Update table set? where?;
Delete from table where?;

Except for the first SQL to share a lock on a record, the rest is an exclusive lock.

MySQL avoids phantom reading at the REPEATABLE read transaction isolation level.
However, the current read of a repeatable read isolation level initiates a gap lock.

The current read (locked read) lock condition.

	Read Submit	REPEATABLE READ
Primary key Index	Lock PRIMARY Key Index	Lock PRIMARY Key Index
Unique index	Locks the value of a unique index and the value of a primary key index	Locks the value of a unique index and the value of a primary key index
Normal index	Locks the value of the normal index and the value of the primary key index	Locks the value of the normal index and the value of the primary key index, and the normal index increases the gap lock
No index	Lock all primary key indexes and unlock non-qualifying records at the server level	Locks all the primary key indexes and the primary key index gap

According to the teacher's example (another one, now the site is also said no)
View the same SQL
(delete from t1 where id = 10;)
In different situations, lock the case.

The first is the read COMMIT TRANSACTION isolation level,
If the ID is a primary key index, lock the key value of the primary key index


If the ID is a unique index, lock the correlation key value for the unique index and primary key index.


If the ID is a normal index, the correlation key values for the normal index and the primary key index are locked.


Finally, if the ID is not indexed, at the InnoDB level, an exclusive lock on all primary key indexes is made to the MySQL server layer to unlock the records that are not eligible.


And at the REPEATABLE read Transaction isolation level,
If the ID is a primary key index, as with a read commit, the correlation key value of the primary key index is locked.

If the ID is a unique index, MySQL will degenerate the gap lock into a row-level lock, locking only the associated key values for the unique index and primary key index. Same as read commit isolation level.
Exception:
If the unique index of the combination is create unique index inx_1 on test (A,B,C);
But the query only uses a, b select * from Test where a=? and b=? For update
MySQL will not degenerate into a row-level lock at this point, he will be treated the same way as the following ordinary index. (Row level lock + gap Lock)

If the ID is a normal index, MySQL locks the relevant key values for the normal index and primary key index, and locks the gap between the relevant normal indexes.


For example, select * from T1 where id=100 for update;
This SQL does not find any records, but he will also lock on the gap.
(In this case, a gap lock is generated even if the ID is a primary key index or a unique index)

Under REPEATABLE Read isolation level, if the ID is not indexed
He locks all records and all gaps in the primary key index.
Unlike read submissions, the MySQL server layer does not unlock non-qualifying records. And it locks all gaps in the primary key index.


Refer to an example in the teacher's article


Under REPEATABLE Read isolation level,
Index key:pubtime > 1 and Puptime < 20. This condition is used to determine the query scope of SQL on the Idx_t1_pu index.
Index Filter:userid = ' hdc '. This condition, which can be filtered on the Idx_t1_pu index, is not part of index Key.
Table filter:comment is not NULL. This condition, which cannot be filtered on the Idx_t1_pu index, can be filtered only on the clustered index.

His lock-up situation is as follows


It can be seen that, under the repeatable read isolation level, the range determined by index key is added to the gap lock; The index filter locks the given condition (UserID = ' hdc ') when filtering, depending on the version of MySQL, in MySQL Before version 5.6, index Condition pushdown (ICP) was not supported, so the index filter was filtered at the MySQL server layer, and the index Condition Pushdown was supported after 5.6, then filtered on index. If the ICP is not supported, do not satisfy the index filter record, also need to add record x lock, if the ICP is supported, does not satisfy the index filter record, no need to record X lock (in the figure, with the red arrow marked X lock, whether to add, depending on whether the ICP is supported); Filter is filtered at the MySQL server level after reading it in the clustered index, so an X lock is also required on the clustered index. Finally, we select a record that satisfies the condition [8,hdc,d,5,good], but the number of locks is much larger than the number of records satisfying the condition.

Conclusion: Under the REPEATABLE read isolation level, for a complex SQL, you first need to extract its where condition. Index key to determine the scope, need to add gap lock; Index filter filter, depending on whether the MySQL version supports ICP, if the ICP is supported, the index filter does not meet the record, no X lock, otherwise need x lock; Table Filter filter condition, The x lock is required whether or not it is satisfied.


The above is a summary of the teacher's article.

This article looked for a long time, but one question has been puzzling.
Experimental environment


Experimental data
CREATE TABLE T
(
A int primary key,
b int,
c int,
Key (B,c)
) Engine=innodb;

INSERT INTO T
Values
(1,10,10),
(3,10,20),
(5,20,30),
(7,20,40),
(9,20,50);
Commit

Terminal One:
SELECT * from t where b=10 and c=10 for update;
Theoretically, he will lock the secondary index (b=10 c=10), the primary key index (a=1) and lock (10, minus infinity) to (10,10), (10,10) to (10,20) the range, which is the gap lock.

But terminal two:
SELECT * from t where b=10 and c=15 for update;
The query is not blocked. That's not true. Because at this isolation level, a gap lock is generated even without data.

Terminal Three:
Rollback terminal two, in terminal three input
INSERT into T values (50,10,15);
I found that the SQL was blocked, which means that the gap lock of the terminal one exists.


The question looked at the official documentation,
Http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html
But actually did not understand.
On the basis of lack of theoretical basis,
I guess although the currently read row lock is exclusive (except for that lock in share mode).
However, the gap lock has the nature of the shared lock, once the gap lock, in the range of the gap can not be any new record
Whether the update was modified or the insert was added.
The gap lock can coexist as long as no new records appear in The Gap.


Reference:
Turn on lock monitoring, you can see more lock information at show engine InnoDB Status\g, and periodically write information to the error log.
CREATE TABLE Innodb_lock_monitor (a int) engine=innodb;

See if the ICP is open
SELECT @ @optimizer_switch \g

MySQL chooses what to think before you can read it repeatedly.