mysql autocommit undo_Mysql 中的MVCC原理，undo日志的依赖

一、 MVCC 原理了解

原文点击:MVCC原理浅析

读锁:

也叫共享锁、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服务器。但行级锁对系统开销较大，处理高并发较好。

Autocommit:

mysql一个系统变量，默认情况下autocommit=1表示mysql把没一条sql语句自动的提交，而不用commit语句。所以，当要开启事务操作时，要把autocommit设为0，可以通过“set session autocommit=0; ”来设置

MVCC: (MVCC,Multiversion Currency Control)

多版本并发控制。一般情况下，事务性储存引擎不是只使用表锁，行加锁的处理数据，而是结合了MVCC机制，以处理更多的并发问题。Mvcc处理高并发能力最强，但系统开销比最大(较表锁、行级锁)，这是最求高并发付出的代价。

当使用锁(尤其排他锁-行锁)进行并发控制，开销是非常大的 ，而使用MVCC机制来做，能一定程度的代替行锁，就可以有效降低系统开销

InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，没开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID.

话外内容：############开始###############

InnoDB的内核，会对所有row数据增加三个内部属性：

1.DB_TRX_ID：一个6byte的标识，每处理一个事务，其值自动+1

下面提到的“创建时间”和“删除时间”记录的就是这个DB_TRX_ID的值

如insert、update、delete操作时，删除操作用1个bit表示。

DB_TRX_ID是最重要的一个，可以通过语句“show engine innodb status”来查找

2.DB_ROLL_PTR: 大小是7byte,指向写到rollback segment(回滚段)的一条undo log记录

(update操作的话，记录update前的ROW值)

3.DB_ROW_ID: 大小是6byte,该值随新行插入单调增加。

当由innodb自动产生聚集索引时聚集索引(即没有主键时,因为MYSQL默认聚簇表,会自动生成一个ROWID)

包括这个DB_ROW_ID的值，

不然的话聚集索引中不包括这个值,这个用于索引当中。

话外内容：############结束###############

如：列子，假设版本好初始为1

create table tb(

id int auto_increment primary key,

name cahr(32));

创建几条数据：insert 操作:第一个事务ID为1

start transaction;

insert into tb(name) values('shenjian') ;

insert into tb(name) values('zhangshan');

insert into tb(name) values('lisi');

commit;

id

name

创建时间(事务ID)

删除时间(事务ID)

1

shenjian

1

undefinded

2

zhangsan

1

undefinded

3

lisi

1

undefinded

对select 的影响：

innodb 只会去查找版本好小于或等于(早于)当前事务ID的数据行，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的

行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.

只有1、2同时满足的记录，才能返回作为查询结果.

例2：delete操作

InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识.

看下面的具体例子分析:

第二个事务,ID为2;

start transaction;

select * from tb; //(第一步操作：1)

select * from tb; //(第二部操作：2)

commit;

假设：在这个事务的的执行过程中到第一步操作：1 的时候。

又开启了一个新事务(ID 为3)向表中执行Insert 操作插入数据

start transaction;

insert into tb(name) values('wangwu') ;

commit;

此时的表中数据为：

id

name

创建时间(事务ID)

删除时间(事务ID)

1

shenjian

1

undefinded

2

zhangsan

1

undefinded

3

lisi

1

undefinded

4

wangwu

3

undefinded

然后刚才事务2，中的第二步：2 开始执行，引文id=4的数据的创建时间(事务ID为3),执行当前事务的ID为2,而InnoDB只会查找事务ID小于等于当前事务ID的数据行,所以id=4的数据行并不会在执行事务2中的第二步：2 中被检索出来,在事务2中的两条select 语句检索出来的数据都只会下表:

id

name

创建时间(事务ID)

删除时间(事务ID)

1

shenjian

1

undefinded

2

zhangsan

1

undefinded

3

lisi

1

undefinded

再假设：假设在执行事务ID为2的过程中,刚执行到了第一步：1,假设事务执行完事务3后，接着又执行了事务4;

事务4：

st art transaction;

delete from tb where id=1;

commit;

此时数据库中的表为：

id

name

创建时间(事务ID)

删除时间(事务ID)

1

shenjian

1

4

2

zhangsan

1

undefinded

3

lisi

1

undefinded

4

wangwu

3

undefinded

接着执行事务ID为2的第二步：2,根据select 检索条件可以知道,它会检索创建时间(创建事务的ID)小于当前事务ID的行和删除时间(删除事务的ID)大于当前事务的行,而id=4的行上面已经说过,而id=1的行由于删除时间(删除事务的ID)大于当前事务的ID,所以事务2的第二步：2 select * from tb 会把id=1的数据检索出来.所以,事务2中的两条select 语句检索出来的数据都如下:

id

name

创建时间(事务ID)

删除时间(事务ID)

1

shenjian

1

4

2

zhangsan

1

undefinded

3

lisi

1

undefinded

update：

InnoDB执行UPDATE，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间.

再再假设：

假设在执行完事务2的第一步：1 后又执行,其它用户执行了事务3,4,这时，又有一个用户对这张表执行了UPDATE操作:

第5个事务:

start transaction;

update tb set name='xxx' where id=2;

commit;

按照update的更新原则:会生成新的一行,并在原来要修改的列的删除时间列上添加本事务ID,得到表如下:

id

name

创建时间(事务ID)

删除时间(事务ID)

1

shenjian

1

4

2

zhangsan

1

5

3

lisi

1

undefinded

4

wangwu

3

undefinded

2

xxx

5

undefinded

继续执行事务2第二步：2,根据select 语句的检索条件(创建时间事务ID小于自己查询事务ID的，和删除时间事务ID大于自身的查询事务ID),得到下表:

id

name

创建时间(事务ID)

删除时间(事务ID)

1

shenjian

1

4

2

zhangsan

1

5

3

lisi

1

undefinded

并得不到上上个表的ID=4的第四行(因为创建事务ID=3 > 查询事务ID=2) 和第五行Update 事务创建的ID=2 的更新数据(因为 创建他的Update 事务ID=5 > 查询事务ID=2)