In MySQL the InnoDB storage engine provides MVCC, row-level locking, full ACID compliance as well as other features.
In my understanding of database theory, access to modify independent sections of unique data (e.g. UPDATE) under MVCC should fully support concurrency. I have however experienced a level of exclusive locking under Innodb.
I wanted to clearly document this situation so I could then seek the advice of the guru’s in InnoDB Internals such as Mark Callaghan, Percona and the Innodb development team for example. I’m happy to say I’m not a MySQL expert in every aspect of MySQL, specifically internals where I have not had the detailed time to read the code, and understanding all internal workings.
The situation
Single table updates on a range of rows by primary keys are being blocked by other similar operations on the same table yet the set of data for each query is effectively unique.
Reproducing the problem
$ mysql -u -p test
drop table if exists numbers;
create table numbers (id int unsigned not null primary key, f1 int not null, f2 int not null) engine=innodb;
delimiter $$
drop procedure if exists fill_numbers $$
create procedure fill_numbers(in p_max int)
deterministic
begin
declare counter int default 1;
truncate table numbers;
insert into numbers values (1,1,1);
while counter < p_max
do
insert into numbers (id,f1, f2)
select id + counter, counter + f1, id - f2
from numbers;
select count(*) into counter from numbers;
select counter;
end while;
end $$
delimiter ;
call fill_numbers(2000000);
In two separate threads I execute similar statements on different ranges of the primary key.
--thread 1 start transaction; update numbers set f2 = f2 +200 where id between 1 and 1000000; commit; --thread 2 start transaction; update numbers set f2 = f2 +300 where id between 1000001 and 2000000; commit;
And in a third thread we can monitor the transactions inside Innodb.
-- thread 3 show engine innodb status\G
During the update process, the following error can be observed.
---TRANSACTION 0 7741, ACTIVE 20 sec, process no 2159, OS thread id 1188534592 fetching rows, thread declared inside InnoDB 275 mysql tables in use 1, locked 1 2007 lock struct(s), heap size 292848, 1001862 row lock(s), undo log entries 999858 MySQL thread id 918563, query id 16802707 localhost root Updating update numbers set f2 = f2 +300 where id between 1000001 and 2000000 ---TRANSACTION 0 7740, ACTIVE 21 sec, process no 2159, OS thread id 1178949952 fetching rows mysql tables in use 1, locked 1 LOCK WAIT 2008 lock struct(s), heap size 292848, 1002005 row lock(s), undo log entries 1000000 MySQL thread id 918564, query id 16802694 localhost root Updating update numbers set f2 = f2 +200 where id between 1 and 1000000 ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 16052 n bits 568 index `PRIMARY` of table `test`.`numbers` trx id 0 7740 lock_mode X waiting Record lock, heap no 256 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 000f4241; asc BA;; 1: len 6; hex 000000001e3d; asc =;; 2: len 7; hex 00000033630110; asc 3c ;; 3: len 4; hex 800f4241; asc BA;; 4: len 4; hex 80050584; asc ;;
The problem has been reproduced on various different MySQL versions and different hardware including, 5.0.67, 5.0.81 and 5.1.25.
What is causing the problem?
- Is it a bug? No.
- Is my understanding of MVCC theory incorrect? Maybe.
- Is it InnoDB’s implementation of MVCC incomplete. No. Heikki and his team have a far greater understanding of data theory then most database experts
- Is it the MySQL kernel interfering with the InnoDB storage engine? No, this is not possible as the MySQL kernel has passed the queries to InnoDB, and InnoDB is handling these threads independently.
- Is it a gap locking issue, a problem that can cause deadlocks when inserting data in a high concurrency situation? Not likely as the data is inserted in primary key, i.e. auto increment order, and there are no gaps.
- Is it related to InnoDB access method via the primary key, where InnoDB uses a clustered index to store the primary key. Given the data is physically in primary key order, this clustered index would in theory reduce possible locking.
- Is it related to the page size of indexes, e.g. the 16k index page, effectively causing a page level lock for overlapping index data? My understanding is that InnoDB supports row level locking, and MVCC should cater for this.
- Is is related to the ranges of primary keys being adjacent, i.e. 1,000,000 and 1,000,001. Not likely as I can reproduce the problem not using adjacent ranges.
- Is it some weird interaction to managing the undo space of the transactions in the Innodb buffer pool?
- Is it some weird interaction with marking/locking the dirty pages in the Innodb buffer pool of modified pages?
- Is it some weird interaction with logging the successful Innodb transaction to the redo logs.
I’ve listed these points more as an information exercise for all those that have less understanding of the problem to see my though process.
Additional testing can definitely be performed. Additional analysis of InnoDB internals with SHOW ENGINE INNODB STATUS such as spin waits, OS waits (context switches), looking at Mutexes with SHOW ENGINE INNODB MUTEX can be undertaken.
My hope and request is that this has been observed by others and that a simple hybrid solution exists.
--------------------------------------------------------------------------------
深入浅出INNODB MVCC机制与原理
摘要：
1、基础知识
2、MVCC实现原理以及视图化理解（包含些测试以便理解）
3、深MVCC实现机制
一、基础知识
事务：
事务是一组原子性sql查询语句，被当作一个工作单元。若mysql对改事务单元内的所有sql语句都正常的执行完，则事务操作视为成功，所有的sql语句才对数据生效，若sql中任意不能执行或出错则事务操作失败，所有对数据的操作则无效（通过回滚恢复数据）。事务有四个属性：
1、原子性：事务被认为不可分的一个工作单元，要么全部正常执行，要么全部不执行。
2、一致性：事务操作对数据库总是从一种一致性的状态转换成另外一种一致性状态。
3、隔离性：一个事务的操作结果在内部一致，可见，而对除自己以外的事务是不可见的。
4、永久性：事务在未提交前数据一般情况下可以回滚恢复数据，一旦提交（commit）数据的改变则变成永久（当然用update肯定还能修改）。
ps:MYSAM 引擎的数据库不支持事务，所以事务最好不要对混合引擎（如INNODB 、MYISAM）操作，若能正常运行且是你想要的最好，否则事务中对非支持事务表的操作是不能回滚恢复的。
读锁：
也叫共享锁、S锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S 锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
写锁：
又称排他锁、X锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
表锁：操作对象是数据表。Mysql大多数锁策略都支持（常见mysql innodb），是系统开销最低但并发性最低的一个锁策略。事务t对整个表加读锁，则其他事务可读不可写，若加写锁，则其他事务增删改都不行。
行级锁：操作对象是数据表中的一行。是MVCC技术用的比较多的，但在MYISAM用不了，行级锁用mysql的储存引擎实现而不是mysql服务器。但行级锁对系统开销较大，处理高并发较好。
MVCC：多版本并发控制（MVCC,Multiversion Currency Control）。一般情况下，事务性储存引擎不是只使用表锁，行加锁的处理数据，而是结合了MVCC机制，以处理更多的并发问题。Mvcc处理高并发能力最强，但系统开销比最大（较表锁、行级锁），这是最求高并发付出的代价。
Autocommit：是mysql一个系统变量，默认情况下autocommit=1表示mysql把没一条sql语句自动的提交，而不用commit语句。所以，当要开启事务操作时，要把autocommit设为0，可以通过“set session autocommit=0; ”来设置
二、MVCC实现原理以及例化理解（包含些测试以便理解）
第一：先看看网络上几乎全部一样的理解，包括《高性能mysql第二版（中文版）》也如此说明，这样是很容易理解。但笔者觉得2个地方不妥，先看内容，在后面笔者会给出不妥地方用(1、2…)加粗标志出来，且给出测试证明。
Ps：这些只是外部看来的理解层面，深层次在第三点讲解
------------------------------------------
InnoDB实现MVCC的方法是，它存储了每一行的两个(1)额外的隐藏字段，这两个隐藏字段分别记录了行的创建的时间和删除的时间。在每个事件发生的时候，每行存储版本号，而不是存储事件实际发生的时间。每次事物的开始这个版本号都会增加。自记录时间开始，每个事物都会保存记录的系统版本号。依照事物的 版本来检查每行的版本号。在事物隔离级别为可重复读的情况下，来看看怎样应用它。
SELECT
Innodb检查没行数据，确保他们符合两个标准：
1、InnoDB只查找版本早于当前事务版本的数据行（也就是数据行的版本必须小于等于事务的版本），这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行
2、行的删除操作的版本一定是未定义的或者大于当前事务的版本号。确定了当前事务开始之前，行没有被删除(2)
符合了以上两点则返回查询结果。
INSERT
InnoDB为每个新增行记录当前系统版本号作为创建ID。
DELETE
InnoDB为每个删除行的记录当前系统版本号作为行的删除ID。
UPDATE
InnoDB复制了一行。这个新行的版本号使用了系统版本号。它也把系统版本号作为了删除行的版本。
----------------------------------------------
(1) 不是两个，是三个。
1DB_TRX_ID：一个6byte的标识，每处理一个事务，其值自动+1，上述说到的“创建时间”和“删除时间”记录的就是这个DB_TRX_ID的值，如insert、update、delete操作时，删除操作用1个bit表示。 DB_TRX_ID是最重要的一个，可以通过语句“show engine innodb status”来查找，如下：
-----------------------------------------
……
TRANSACTIONS
------------
Trx id counter 0 430621
Purge done for trx's n:o < 0 430136 undo n:o < 0 0
History list length 7
……
------------------------------------------
2DB_ROLL_PTR: 大小是7byte,指向写到rollback segment（回滚段）的一条undo log记录（update操作的话，记录update前的ROW值）
3DB_ROW_ID: 大小是6byte,该值随新行插入单调增加，当由innodb自动产生聚集索引时，聚集索引包括这个DB_ROW_ID的值，不然的话聚集索引中不包括这个值. 这个用于索引当中
(2) 这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候。网上的说法很容易让读者误解
(3) 这点上面没有标注，在insert操作时 “创建时间”=DB_ROW_ID，这时，“删除时间 ”是未定义的；在update时，复制新增行的“创建时间”=DB_ROW_ID，删除时间未定义，旧数据行“创建时间”不变，删除时间=该事务的DB_ROW_ID；delete操作，相应数据行的“创建时间”不变，删除时间=该事务的DB_ROW_ID；select操作对两者都不修改，只读相应的数据
第二、下面用图形化形式表示MVCC如何处理select、insert、delete、update
有两个事务A、B
假设开始时间顺序ABCD，且DB_TRX_ID满足以下情况
A. DB_TRX_ID = 2010
B. DB_TRX_ID = 2011
C. DB_TRX_ID = 2012
D. DB_TRX_ID = 2013
注意：
1、B. DB_TRX_ID> A. DB_TRX_ID是因为DB_TRX_ID的值是系统版本号的值，系统版本号是自动增加的，所以DB_TRX_ID也是自动增加。但是会出现这种情况，假如A事务开始后B事务开始前有一个insert操作插入一行数据（没有bengin、comint），则B. DB_TRX_ID= A. DB_TRX_ID+1+1 ，并不符合不是说系统版本号增量为1，其实并不矛盾，其实每一条sql操作可以当作一个事务，因为autocommit=1，所以这个insert操作是一个事务，A事务之后新增2个事务， 所以是加2而不是1。
2、下面例化图只是笔者方便大家理解而设计的图片，红色框代表隐藏两列
例化1：SECLET
这是表test数据
trx代表改行数据是那个事务创建
creat_num是“创建时间”，也就是DB_TRX_ID值
dele_num是“删除时间 ”，空列代表没被任何事务标志为已“删除”，图中id为2的数据行的dele_num=2012表示事务C“删除”了改行。
B事务有select * from test;语句，按照MVCC原理，该语句相当于：select * from test where creat_num<=2011 and (dele_num=NULL OR dele_num>2011),所以返回数据是id为1、2、3行。
D事务select * from test;则返回出id不为2的行。因为2行被C事务删除了。
例化2：UPDATE
A事务一条语句“update from test set col=’winben’ where col=’benwin’”。
则先复制一条数据如蓝色框，creat_num=DB_TRX_ID（这里是2010），dele_num=NULL,然后把旧行数据的设dele_num=2010，等commit后则删除旧数据行
例化3：DELET
删除就是设dele_num= DB_TRX_ID
三、深入MVCC实现机制
1、到这里很多人就会发现，如果确实根据creat_num 即时事务DB_TRX_ID去比较获取事务的话，按道理在一个事务B（比A后，但A还没commit）select的话B. DB_TRX_ID>A.DB_TRX_ID则应该能返回A事务对数据的操作以及修改。那不是和前面矛盾？其实不然，只是前面没有讲到以下内容。
InnoDB每个事务在开始的时候，会将当前系统中的活跃事务列表（trx_sys->trx_list）创建一个副本（read view），然后一致性读去比较记录的tx id的时候，并不是根据当前事务的tx id，而是根据read view最早一个事务的tx id（read view->up_limit_id）来做比较的，这样就能确保在事务B之前没有提交的所有事务的变更，B事务都是看不到的。当然，这里还有个小问题要处理一下，就是当前事务自身的变更还是需要看到的。
在storage/innobase/read/read0read.c中实现了创建read view的函数read_view_open_now，在storage/innobase/include/read0read.ic中实现了判断一致性读是否可见的read_view_sees_trx_id
代码：
- read_view_t*
- read_view_open_now(
- /*===============*/
- trx_id_t cr_trx_id, /*!< in: trx_id of creating
- transaction, or 0 used in purge */
- mem_heap_t* heap) /*!< in: memory heap from which
- allocated */
- {
- read_view_t* view;
- trx_t* trx;
- ulint n;
- ut_ad(mutex_own(&kernel_mutex));
- view = read_view_create_low(UT_LIST_GET_LEN(trx_sys->trx_list), heap);
- view->creator_trx_id = cr_trx_id;
- view->type = VIEW_NORMAL;
- view->undo_no = 0;
- /* No future transactions should be visible in the view */
- view->low_limit_no = trx_sys->max_trx_id;
- view->low_limit_id = view->low_limit_no;
- n = 0;
- trx = UT_LIST_GET_FIRST(trx_sys->trx_list);
- /* No active transaction should be visible, except cr_trx */
- while (trx) {
- if (trx->id != cr_trx_id
- && (trx->conc_state == TRX_ACTIVE
- || trx->conc_state == TRX_PREPARED)) {
- read_view_set_nth_trx_id(view, n, trx->id);
- n++;
- /* NOTE that a transaction whose trx number is <
- trx_sys->max_trx_id can still be active, if it is
- in the middle of its commit! Note that when a
- transaction starts, we initialize trx->no to
- IB_ULONGLONG_MAX. */
- if (view->low_limit_no > trx->no) {
- view->low_limit_no = trx->no;
- }
- }
- trx = UT_LIST_GET_NEXT(trx_list, trx);
- }
- view->n_trx_ids = n;
- if (n > 0) {
- /* The last active transaction has the smallest id: */
- view->up_limit_id = read_view_get_nth_trx_id(view, n - 1);
- } else {
- view->up_limit_id = view->low_limit_id;
- }
- UT_LIST_ADD_FIRST(view_list, trx_sys->view_list, view);
- return(view);
- }
2、MVCC如何控制update操作
前面说先复制新数据，并插入DB_TRX_ID的值，在把旧数据的删除标志DB_TRX_ID
现在先介绍几个概念：
DB_ROLL_PTR是指向回滚段中旧版本7byte回滚指针。
redo log：重做日志，就是每次mysql在执行写入数据前先把要写的信息保存在重写日志中，但出现断电，奔溃，重启等等导致数据不能正常写入期望数据时，服务器可以通过redo_log中的信息重新写入数据。
undo log：撤销日志，与redo log恰恰相反，当一些更改在执行一半时，发生意外，而无法完成，则可以根据撤消日志恢复到更改之前的壮态。
mvcc中update步骤：
1、 记录事务中修改行数据的相应字段和值（包括旧版本事务id）在undo-log中记录。
2、 修改相应数据。
3、 在redo-log中保存要修改的相应（新版本事务id）数据写入
以上骤详细代码内容可看：
http://hi.baidu.com/gao1738/blog/item/dcec39d6185af2049d163d8c.html
4、 假如update不能正常运行怎根据undo-log redo-log 来回复
5、 当然如果当前版本事务没有commit的话则通过undo-log信息恢复原始数据状态
终于写完了，笔者用了一个多星期，若有什么纰漏的地方，请多多指教！445235728@qq.com（benwin）
参考文献：
《高性能mysql第二版（中文版）》
http://www.ningoo.net/html/2011/innodb_mvcc_consistency_read.html
http://hi.baidu.com/gao1738/blog/item/dcec39d6185af2049d163d8c.html
http://www.mysqlops.com/2012/04/20/mysql-innodb-mvcc.html
1111
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
