innodb_locks_unsafe_for_binlog分析

在5.1.15的时候，innodb引入了一个概念叫做“semi-consistent”，这样会在innodb_locks_unsafe_for_binlog的状态为ennable时在一定程度上提高update并发性。

幻读（Phantom Read): 是指当用户读取某一范围的数据行时，B事务在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影”行。InnoDB和Falcon存储引擎通 过多版本并发控制机制解决了幻读问题。

Consider the following example, beginning with this table:

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
In this case, table has no indexes, so searches and index scans use the hidden clustered index for record locking (seeSection 14.8.2.1, “Clustered and Secondary Indexes”).

Suppose that one client performs an UPDATE using these statements:

SET autocommit = 0;
UPDATE t SET b = 5 WHERE b = 3;
Suppose also that a second client performs an UPDATE by executing these statements following those of the first client:

SET autocommit = 0;
UPDATE t SET b = 4 WHERE b = 2;
As InnoDB executes each UPDATE, it first acquires an exclusive lock for each row, and then determines whether to modify it. If InnoDB does not modify the row and innodb_locks_unsafe_for_binlog is enabled, it releases the lock. Otherwise, InnoDBretains the lock until the end of the transaction. This affects transaction processing as follows.

If innodb_locks_unsafe_for_binlog is disabled, the first UPDATE acquires x-locks and does not release any of them:

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock
The second UPDATE blocks as soon as it tries to acquire any locks (because the first update has retained locks on all rows), and does not proceed until the first UPDATE commits or rolls back:

x-lock(1,2); block and wait for first UPDATE to commit or roll back
If innodb_locks_unsafe_for_binlog is enabled, the first UPDATE acquires x-locks and releases those for rows that it does not modify:

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)
For the second UPDATE, InnoDB does a “semi-consistent” read, returning the latest committed version of each row to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE:

x-lock(1,2); update(1,2) to (1,4); retain x-lock x-lock(2,3); unlock(2,3) x-lock(3,2); update(3,2) to (3,4); retain x-lock x-lock(4,3); unlock(4,3) x-lock(5,2); update(5,2) to (5,4); retain x-lock

---------------------
作者：彭薄
来源：CSDN
原文：https://blog.csdn.net/cxl0921/article/details/77623439
版权声明：本文为博主原创文章，转载请附上博文链接！

innodb_locks_unsafe_for_binlog分析

标签：tps   nts   detail   not   ISE   _for   过多   wait   提高