大家好,我是anyux。本文介绍MySQL存储引擎InnoDB的核心特性。
事务
事务的核心特性
Atomic原子性
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态
Consistent一致性
如果数据库在事务开始时处于一种状态,则在执行该事务期间将保留一致状态
Isolated隔离性
事务之间不相互影响
Durable持久性
事务成功后,所做的所有更改会准确地记录在数据库中。所做的更改不会丢失
事务的生命周期(事务控制语句)
如何开启事务
begin;
或者
start transaction;
标准事务语句
事务语句就是DML数据操作语句
insert updatedelete
update city set countrycode='CHN' where id=1;
update city set countrycode='CHN' where id=2;
update city set countrycode='CHN' where id=3;
事务的结束
确认,提交
commit;
取消,回滚
rollback;
自动提交机制(autocommit)
默认情况下, MySQL启用自动提交模式(变量autocommit为ON)。这意味着,只要你执行DML操作的语句,MySQL会立即隐式提交事务(Implicit Commit)
在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。
select @@autocommit;
关闭/开启 自动提交
set autocommit=0/1;
set session autocommit=0;
全局级别修改后,需要重连后才能生效
set global autocommit=0;
如果要永久生效,在配置文件中修改系统变量。
[mysqld]
autocommit=0
隐式提交事务的情况
在同一个会话中,开启了一个事务,未提交,又开启新的事务,会触发隐式提交
在同一个会话中,开启了一事务,未提交,开启了自动提交,会触发隐式提交
导致提交的非事务语句:
DDL语句,(alter,create,drop)
DCL语句,(grant,revoke,set password)
锁定语句,(lock table和unlock table)
事务的ACID如何保证
概念名词
redo log:重做日志
ib_logfile0~1 默认50M 轮询使用
redo log buffer: redo 内存区域
ibd :存储数据行和索引
data buffer pool:数据缓冲区池,数据和索引的缓冲
LSN:日志序列号,ibd,redo log,data buffer pool,redo buffer.MySQL每次启动数据库,都会比较磁盘数据页和redlog的LSN,必须要求两者一致数据库才能正常启动
WAL: write ahead log 日志优先写的方式实现持久化,日志是优先于数据写入磁盘的动作
脏页:内存脏页,内存中发生了修改,没写入到磁盘之前,把内存页称之为脏页
CKPT:Checkpoint检查点,将脏页写入到磁盘的动作
TXID:事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务
redo重做日志
作用:重点保证持久性,也在一定程度上保证了原子性,一致性
记录了什么:记录了内存数据页的变化
内存数据的变化提供快速的持久化功能(WAL)CSR过程中实现前滚的操作(磁盘数据页和redo日志LSN一致)redo日志位置
redo的日志文件:iblogfile0,iblogfile1
redo的buffer:数据页的变化信息+数据页当时的LSN号
redo的刷写策略
commit;
刷写当前事务的redo buffer到磁盘,还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
MySQL:在启动时,必须保证redo日志文件和数据文件LSN必须一致,如果不一致就会触发CSR,最终数据一致
情况一:
我们做了一个事务,begin;update;commit;
1.在begin时,会立即分配一个TXID=tx_01;
2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data_buffer中
3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer中
5.执行commit时,LGWR日志写线程会将refo buffer信息写入redo log日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7.MySQL再次重启时,必须要redo log和磁盘数据页的LSW一致的,但是,此时dp_01,TXID=tx_01磁盘是LSW=101,dp_01,TXID=tx_01,redolog中LSN=102。MySQL此时无法正常启动,MySQL触发CSR,在内存追平ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redlog的LSN一值,这里MySQL正常启动
以上的工作过程,称之为基于redo的"前滚操作"
undo:回滚日志
作用:在ACID特性中,主要保证A的特性,同时对CI也有一定的功效
1.记录了数据修改之前的状态
2.rollback将内存的数据修改恢复到修改之前
3.在CSR看实现未提交数据的回滚操作
4.实现一致性快照,保证MVCC,讯和写的操作不会互相阻塞
锁
实现了事务之间的隔离功能,InnoDB中实现的是行级锁,row-level lock gap next-lock
锁是为了实现ACID中的C,保证隔离性
读的隔离级别
隔离级别主要控制的是读的隔离性
查看默认隔离级别
select @@tx_isolation;
RU:读未提交,可脏读,一般不提交出现
RC:读已提交,可能出现幻读,可以防止脏读
RR:可重复读(默认级别),功能是防止幻读现象,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR:可串行化读,可以防止死锁,但是并发事务性能差
补充:在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句。但是,请记住执行完一定要commit否则容易出现锁等待比较严重
RU:给示事务已经创建,但是未提交,类似取2000块钱还没有按下确认,此时就查询,显示余额减少2000块,明显是错误的
设置隔离级别
修改配置文件,以下隔离级别选择一个,然后重启服务,后面验证相应的隔离级别
vim /etc/my.cnf
[mysqld]
未提交读,RU隔离级别
transaction_isolation=read-uncommitted
已提交读,RC隔离级别
transcation_isolation=read-committed
可重复读,RR隔离级别
transcation_isolation=repeatable-read
此处演示RU隔离级别
开两个窗口,同时登录mysql服务器,使用world数据库。更新操作的命名为A窗口,查询操作的命名为B窗口。
B窗口查询更新前的数据
A窗口更新,执行更新操作,在内存中更新了,但是未提交到磁盘中
B窗口查询事务已创建,查询更新的数据,在内存中获取数据,此时内存中的数据与磁盘中数据不同,读取的内容为脏页,读取的行为叫脏读
A窗口回滚,rollback回滚数
B窗口查询,查询更新的数据
此处演示RC隔离级别
设置隔离级别操作为RC级别
B窗口查询更新前的数据
A窗口更新,执行更新操作,在内存中更新了,但是未提交到磁盘中
B窗口查询事务已创建,但未提交的数据
A窗口提交数据,即将内存中的数据写入到磁盘中
B窗口查询更新后,且已提交的数据
A窗口更新,执行更新操作,并提交事务
B窗口查询更新后,且已提交的数据
RC隔离级别的演示中,可以看出,如果事务没有被commit,则查询结果不会改变。提交读的方式完全可以避免脏读的产生。
但是,这在非金融行业中完全可以接受。对于金融行业如结算、统计,报表这种要示可以重复读取数据且保持不变的情况,RC隔离级别不适合。在RC隔离级别中,每一次commit后,查询的结果都会发生相应的变化,这样对于金融行业的一些业务不适用,就需要使用了RR隔离级别
演示RR可重复读演示
设置隔离级别操作为RR级别
B窗口查询更新前的数据
A窗口更新,执行更新操作,在内存中更新了,但是未提交到磁盘中
B窗口查询事务已创建,但未提交的数据
A窗口提交数据,即将内存中的数据写入到磁盘中
B窗口查询更新后,且已提交的数据
再开一个窗口C查询数据
在RR隔离级别中,可以看到即使commit提交了数据,写入磁盘,在B窗口中也不会查询到已提交的数据,只要B窗口不断开连接,它查询的数据永远是一致的。而在C窗口中,查询的数据为新的数据。RR模式为最高的隔离级别
这里利用了MVCC机制,基于undo的快照,MVCC机制会在每个会话开启时生成一个致性快照,不论表数据怎么被修改,读取的永远都是快照中的数据。多版本并发就是不同会话拥有不同快照,查询出的信息也不同
RR通过MVCC机制解决了不可重复读的问题,但是有可以还会出现幻读现象,可以通过GAP和Next-lock进行避免
演示幻读
创建test表use test;
创建数据表,插入数据表
create table test(id int auto_increment primary key,name char(10))engine=innodb charset=utf8;
insert into test values(1,"A"),(2,"B"),(3,"C"),(4,"D");
查看隔离级别,此时设置为RC隔离级别
select @@tx_isolation;
窗口A 业务需要将id大于2的,name属性全部修改为xupdate test set name='x' where id>2;
窗口B 插入新的数据,并提交insert into test values(5,'E');
commit;
窗口A ,提交。按照预想的那样,所有id大于2的name属性都会被修改为xcommit;
窗口A,查询数据select * from test where id>2;
以上情形,窗口A在批量更新过程中,在窗口B也在更新数据,预想中的数据所有属性应该为x,实际上却存在着name为E的记录。这种情况称为幻读
如何避免幻读现象呢
首先需要使用RR级别,然后使用的列为索引列,但是RR隔离级别也解决不了幻读现象。需要使用到两把锁提供帮助。将从id为2的行开始,将其以后所有的行都进行锁定,并且不允许插入,更新,删除操作。如果出现5-8之间为空的id记录使用GAP锁,锁定9到最大值的锁就称为Next-lock锁。GAP和Next-lock就可以防止幻读
演示RR隔离级别下避免幻读
注意需要在RR隔离级别下操作
创建gap表,并插入数据use test;create table gap(id int auto_increment primary key,name char(10))engine=innodb charset=utf8;insert into gap values(1,"A"),(2,"B"),(3,"C"),(9,"D");窗口A 业务需要将id大于2的,name属性全部修改为xupdate test set name='x' where id>2;
窗口B 插入新的数据insert into test values(5,'E');
在更新数据的过程中,语句无法执行,是因为区间锁的原因.这表明RR级别下,可以通过Next-lock和GAP锁防止幻读的出现