innodb_locks_unsafe_for_binlog参数解析
来源:互联网 发布:现货看盘软件下载 编辑:程序博客网 时间:2024/04/30 16:19
前段时间我在研究mysql数据库锁,阅读了几天的mysql官方文档,对mysql的锁产生了浓厚的研究兴趣。我在之前做mysql中record lock导致dead lock的场景研究中,发现mysql不稳定,今天终于找到原因了。
--innodb-locks-unsafe-for-binlog
System VariableNameinnodb_locks_unsafe_for_binlog
Variable ScopeGlobalDynamic VariableNoPermitted ValuesTypebooleanDefaultOFF
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, InnoDB
retains 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-lockx-lock(2,3); update(2,3) to (2,5); retain x-lockx-lock(3,2); retain x-lockx-lock(4,3); update(4,3) to (4,5); retain x-lockx-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-lockx-lock(3,2); unlock(3,2)x-lock(4,3); update(4,3) to (4,5); retain x-lockx-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-lockx-lock(2,3); unlock(2,3)x-lock(3,2); update(3,2) to (3,4); retain x-lockx-lock(4,3); unlock(4,3)x-lock(5,2); update(5,2) to (5,4); retain x-lock
- innodb_locks_unsafe_for_binlog参数解析
- innodb_locks_unsafe_for_binlog
- innodb_locks_unsafe_for_binlog && transaction isolation level
- 参数解析
- 参数解析
- GHOST参数详细解析
- 视频格式参数解析
- Explorer.exe 参数解析
- 命令行参数解析(1)
- 命令行参数解析(2)
- 命令行参数解析实例
- main函数参数解析
- 连接池参数解析
- 变参数printf解析
- 解析命令行参数
- 命令行参数的解析
- linux网络参数解析
- Linux 命令行参数解析
- 第四章 4.3 最小生成树
- 软件测试的定义
- 闭包的理解和使用
- Linux安装Hadoop2.4.1和伪分布式部署
- 深度学习概述-神经网络与深度学习学习笔记(一)
- innodb_locks_unsafe_for_binlog参数解析
- 第五章 单词查找树
- 堆排序
- Spark 修改整列数据类型+写入数据到HDFS
- Virtuoso使用(下载安装配置)简介
- lucene5--索引域选项
- 吐槽---别走(如何做一个leader)
- CSS浮动
- 51nod 1564 && 1674 区间的价值(V2) 数列分治