- 浏览: 789299 次
- 性别:
- 来自: 杭州
最新评论
-
137578346:
...
各版本MySQL并行复制的实现及优缺点 -
db_code_peon:
引用而这个“调整”,也不是简单的指针重赋,而是将这个page ...
MySQL源码学习:简述InnoDB的BP LRU策略 -
xtha21:
预申请ID的个数为:大于批量插入记录数的最近一个2的指数值再- ...
MySQL源码学习:innodb_autoinc_lock_mode 下自增id不连续的原因 -
lc15808798846:
奇哥好试了下,好像这个问题在5.7 已经不存在了,SQL不会h ...
autocommit=0引起的业务hang住 -
wangliushui:
Transfer 怎样保证事物的一致性,一个事物关联多个表的 ...
MySQL-Transfer2.2发布
An example about consistent read and next-key lock in InnoDB
- 博客分类:
- MySQL
This article begins with a misunderstanding to discuss consistent read and next-key lock, and end up with a bug in MySQL optimizer. All bellow are based on the default isolation level, repeatable read, and innodb_locks_unsafe_for_binlog is off.
1、 Misunderstanding
1-1
Look into session1, the transaction starts at the query “select … for update”, so the TRX_ID of session2’s transaction is larger than that of session1’s. But it looks “strange” that after the session2’s transaction committed, the query “select * from tb” can return the new row (200,200).
2、 Explanation
Is it Phantom Read? As I understood before, the consistent view of session1’s transaction is generated at the first select query.
In fact, as described in manual page, there are statements as followed:
"All consistent reads within the same transaction read the snapshot established by the first such read in that transaction"
"Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a time point according to which your query sees the database. "
The “ordinary SELECT statement” is pointed specially, it means that the “select … for update” statement will not generate consistent view, which is in fact generated in the “select * from b”. So it is normal that we can see the (200,200) here.
3、 Verification
As verification, we can insert another (201, 201) in session2 now, and the next “select * from tb” will not return (201,201).
Another clue comes from the command “show engine InnoDB status”.
insert into tb values(10,10),(11,11),(13,13),(20,20); |
begin; |
select * from tb force index a where a>15 and a<18 for update; |
show engine InnoDB status \G
------------ TRANSACTIONS ------------ Trx id counter 1E82110B Purge done for trx's n:o < 1E821107 undo n:o < 0 History list length 8 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1E82110A, ACTIVE 2 sec, process no 5438, OS thread id 1311758656 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 16, query id 757 localhost root show engine InnoDB status |
select * from tb; |
show engine InnoDB status \G
------------ TRANSACTIONS ------------ Trx id counter 1E82110B Purge done for trx's n:o < 1E821107 undo n:o < 0 History list length 8 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1E82110A, ACTIVE 29 sec, process no 5438, OS thread id 1311758656 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 16, query id 759 localhost root show engine InnoDB status Trx read view will not see trx with id >= 1E82110B, sees < 1E82110B |
Till the second execution of “show engine InnoDB status”, there is “Trx read view” generated. The consistent view id is not 1E82110A but 1E82110B.
A “for update” statement is not “ordinary select query”, so no consistent view is generated, the same results when run “select … lock in share mod” and update statements.
If I want to create the consistent view at the beginning of the transaction, “START TRANSACTION WITH CONSISTENT SNAPSHOT” can be issued.
4、 Next-Key Lock
a) Locked When Insertion
If we want to offer a simpler case to tell about this, perhaps we can prefer to drop the column b, and make the table as “create table tb (a int key) engine=InnoDB” here. Then you will find that inserting (200) is locked in session2.
4-1
What happens and why?
b) Next-Key Lock
We must mention a concept that named next-key lock in InnoDB. Look into the next case first.
4-2
The insertion is locked here, what is the difference between this and the prior example? In the “for update” query, the “Gap” is locked. The definitions of the gaps are as follow:
(Negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
So the whole gap (13, 20] is locked by session1, it prevent session2 from inserting (19, 19) here. Certainly the (200,200) will not be locked here, because it is located in another gap.
c) Rows that Locked
Then come back to the question that why the (200) can’t be inserted when there is only one column in the table.
We can look into the “show engine InnoDB status\G” again.
CREATE TABLE `tb` ( `a` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk; |
Insert into tb values(10),(11),(13),(20); |
begin; |
select * from tb where a>15 and a<18 for update; |
show engine InnoDB status\G
------------ TRANSACTIONS ------------ Trx id counter 1E82112A Purge done for trx's n:o < 1E821121 undo n:o < 0 History list length 17 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1E821129, ACTIVE 4 sec, process no 5438, OS thread id 1312815424 2 lock struct(s), heap size 376, 5 row lock(s) |
That seems to be strange that there are “5 row locks”. In fact the optimizer decides it to be a full table scan, because the row number is so small.
As proof we can see Innodb_rows_read increases 4 here. The implicit “supremum” makes the row lock to 5.
So it can say that all the gaps are locked by the “select … for update” query, so any insertion will be prevent here.
We can insert (300),(301),(302)….(10000) into the table, can run the steps again, now the (200) can be inserted in session2.
So it seems that we reach a bug from a “fake bug” :)
5、 Reference
http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html
发表评论
-
各版本MySQL并行复制的实现及优缺点
2016-01-19 18:02 11763MySQL并行复制已经是老生常谈,笔者从2010年开 ... -
一个mysqldump导出失败的案例分析
2014-06-03 23:16 8186背景 MySQL全量逻辑备份恢复最基础的方法,就是m ... -
MySQL 聚集UDF,计算列表中的奇数总和
2014-06-02 23:08 2833技痒之作 -__- CREATE AGG ... -
autocommit=0引起的业务hang住
2014-05-25 17:53 8367背景 有用户报告一个普通的select 语句被hang住 ... -
一个用户SQL慢查询分析,原因及优化
2014-05-23 03:25 11845问题描述 一个用户反 ... -
一个用户迁移数据库前后的性能差异case
2014-05-21 14:21 3395问题 一个用户问题,数据从ECS迁移到RDS,相同的 ... -
MySQL一个异常查询问题追查
2014-05-16 12:38 4352问题 线上碰到的问题:相同的语句,只是最后的limit行 ... -
Webscalesql代码浏览记录
2014-04-03 18:28 3644浏览了下webscalesql 的代码。目前包含的62个c ... -
Webscalesql代码浏览记录
2014-04-03 18:27 1982浏览了下webscalesql 的代码。目前包含的62个co ... -
Webscalesql代码浏览记录
2014-04-03 18:27 3055浏览了下webscalesql 的代码。目前包含的62个co ... -
MySQL Q&A 解析binlog的两个问题
2014-03-31 17:18 4789连续碰到两个同学问类似的问题,必须要记录一下。 问题 ... -
mymysql与go-mysql-driver的一个区别
2014-01-23 21:16 4889今天要写个工具就想顺便学下go。网上翻了下发现用比较多的是 ... -
MySQL-Transfer2.3发布
2013-12-01 21:17 6380Transfer 2.3发布,下载地址 此版本除了升 ... -
关于MySQL count(distinct) 逻辑的另一个bug
2013-11-28 09:59 5930背景 上一篇博文(链接)介绍了count ... -
Is it a bug in MySQL or in java-connector?
2013-11-19 13:58 3096Description In mysql ... -
关于MySQL count(distinct) 逻辑的一个bug
2013-11-16 22:56 9157背景 客户报告了一个count(d ... -
MySQL-Transfer2.2发布
2013-06-18 12:08 4751Transfer 2.2发布。下载地址 版本说明 ... -
MySQL5.5加主键锁读问题—续
2013-06-14 17:38 2488背景 上一篇说到MySQL 5.5加主键导致 ... -
MySQL5.5加主键锁读问题
2013-06-09 09:30 4632背景 有同 ... -
InnoDB row_id边界溢出验证
2013-05-28 13:34 2643背景 跟同学聊到row_id一个边界问题 ...
相关推荐
In this paper, we focus on the problem of motion tracking in unknown environments using ...an iterative, sliding-window fixed-lag smoother, in both Monte-Carlo simulations and real-world testing. I
目标检测论文 Consistent Optimization for Single-Shot Object Detection.pdf
Nginx:一致性哈希(第三方模块ngx_http_consistent_hash):ngx_http_consistent_hash-master.zip
His consistent style of presenting the theoretical structures lucidly unifies his test's wide and well-chosen range of applications. I wish that all my students could take a course based on this book...
In our example, if one transaction (T1) holds an exclusive lock at the table level, and another transaction (T2) holds an exclusive lock at the row level, each of the transactions believe they have ...
资源分类:Python库 所属语言:Python 资源全名:jump_consistent_hash-3.1.1-cp27-cp27m-win_amd64.whl 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
* Delivering functionality and interfaces that are consistent on all platforms * Understanding key similarities and differences among leading platform-specific GUI APIs, including Win32/.NET, Cocoa,...
Consistent Hashing and Random Trees
CVPR2015-Cross-modality Consistent Regression for Joint Visual-Textual Sentiment Analysis.pdf 全文
It is a challenging task to read the balance sheet of an insurance company. This derives from the fact that different positions are often measured by different yardsticks. Assets, for example, are ...
Soft self-consistent pseudopotentials in a generalized eigenvalue formalism
Applications of self-consistent field theory in polymer systems.pdf
ECCount: Efficient and Consistent Counter for high-speed network devices
In this paper, we address the issue of tracking moving objects in an environment covered by multiple uncalibrated cameras with overlapping fields of view, typical of most surveillance setups. In such ...
Jemter测试MQ的插件 JMeter-Rabbit-AMQP在github上17年便停止更新了,不支持rabbitmq的交换机类型“x-consistent-hash”,为此我更改了源码使其支持"x-consistent-hash
二维潜水波问题的数值方法,隐格式,非线性
Ringo is an experimental, distributed, replicating key-value store based on consistent hashing and immutable data. Unlike many general-purpose databases, Ringo is designed for a specific use case: For...
What I wanted to do was provide a streamlined and consistent plug-in architecture to allow any MFC app to be converted across with ease. I have also had requests to post an article on the subject. ...
一致的稀疏深度学习:理论与计算我们提出了一种类似于常客的方法来学习稀疏DNN,并证明其在贝叶斯框架下的一致性。 稀疏DNN的结构可以在经过训练的贝叶斯神经网络与常规先验混合的基础上,使用基于拉普拉斯近似的...