十四、InnoDB的ACID事务
一、什么是事务
二、事务的ACID特性
ACID模型是关系型数据库普遍支持的事务模型，用于保证数据的一致性，其中的ACID所代表的具体含义分别如下。 1）A：atomicity原子性。事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。 2）C：consistency一致性。事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。也就是说，数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。 3）I：isolation独立性。多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其他事务的运行效果。 4）D：durability持续性。在事务完成以后，该事务对数据库所做的更改便持久地保存在数据库之中，并不会被回滚。
举例来说：
比如银行的汇款1000元的操作，简单来说，可以拆分成A账户的余额-1000，B账户的余额+1000，还要分别在A和B的账户流水上记录余额变更日志，这四个操作必须放在一个事务中完成，否则丢失其中的任何一条记录对整个系统来说都是不完整的。 对于上述例子来说，原子性体现在要么四条操作每条都成功，这就意味着汇款成功；要么其中某一个操作失败，则整个事务中的四条操作都回滚，即汇款失败。一致性表示当汇款结束时，A账户和B账户里的余额变化和操作日志记录是可以对应起来的。独立性表示在汇款操作过程中，如果有C账户也在往B账户里汇款的话，那么两个事务之间相互不会影响，即“A->B”有四个独立操作，“C->B”也有四个独立操作。持久性表示当汇款成功时，A和B的余额就变更了，不管是数据库重启还是别的什么原因，该数据已经写入到磁盘中作为永久存储，不会再发生变化，除非有新的事务发生。 其中事务的隔离性是通过MySQL锁机制来实现的，原子性、一致性、持久性则是通过MySQL的redo和undo日志记录来完成的
三、显式事务启动|结束
1）以start transaction/begin开始事务。
begin 说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。
commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。
rollback ：回滚事务
将内存中，已执行过的操作，回滚回去
3)自动提交策略（autocommit）
[(none)]>select @@autocommit; [(none)]>set autocommit=0; [(none)]>set global autocommit=0; 注： 自动提交是否打开，一般在有事务需求的MySQL中，将其关闭,默认值为1及开启 不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能 (1)命令行关闭自动提交 仅当前窗口生效 set autocommit=0; 全局生效，配置后，需要退出当前会话窗口，重新进入 set global autocommit=0; (2)修改配置文件，下一次mysql重启生效 vim /etc/my.cnf autocommit=0
主要是DDL、DCL会引发事务隐形提交，比如create、alter语句以及grant、revoke等语句。
用于隐式提交的 SQL 语句： 1）在开启新一个事务begin开始，前一个没有commit begin sql语句 a sql语句 b begin #默认自动在第二个begin前添加commit,用于前一个事务的完结 2）设置了自动提交策略 SET AUTOCOMMIT = 1 3）导致提交的非事务语句： DDL语句： （ALTER、CREATE 和 DROP） DCL语句： （GRANT、REVOKE 和 SET PASSWORD） 锁定语句：（LOCK TABLES 和 UNLOCK TABLES） 导致隐式提交的语句示例： TRUNCATE TABLE LOAD DATA INFILE SELECT FOR UPDATE
1、检查autocommit是否为关闭状态 select @@autocommit; 或者： show variables like 'autocommit'; 2、开启事务,并结束事务 begin delete from student where name='alexsb'; update student set name='alexsb' where name='alex'; rollback; #事务回退，将数据结果返回事务开启前的结果 begin delete from student where name='alexsb'; update student set name='alexsb' where name='alex'; commit; #事务完成，更新数据结果
1、一些概念
redo log ---> 重做日志 ib_logfile0~1 50M , 轮询使用 redo log buffer ---> redo内存区域 ibd ----> 存储 数据行和索引 buffer pool --->缓冲区池,数据和索引的缓冲 LSN : 日志序列号 磁盘数据页,redo文件,buffer pool,redo buffer MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动 WAL : write ahead log 日志优先写的方式实现持久化 脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页. CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作 TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.
Redo是什么？ redo,顾名思义“重做日志”，是事务日志的一种。 作用是什么？ 在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用 redo日志位置? redo的日志文件：iblogfile0 iblogfile1 redo buffer? redo的buffer:数据页的变化信息+数据页当时的LSN号 LSN：日志序列号 磁盘数据页、内存数据页、redo buffer、redolog 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日志写线程会将redo buffer信息写入redolog日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记) 6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失 7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102,MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动 以上的工作过程,我们把它称之为基于REDO的"前滚操作"
undo是什么？
undo,顾名思义“回滚日志”
作用是什么？
在事务ACID过程中，实现的是“A” 原子性的作用
另外CI也依赖于Undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交(没有commit)的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备
redo怎么应用的:在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致. undo怎么应用的:在CSR实现的是,将redo当中记录的未提交(没有commit)的时候进行回滚. 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日志写线程会将redo buffer信息写入redolog日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记) 6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失 7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102,MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动 LSN :日志序列号 TXID:事务ID CKPT(Checkpoint)
“锁”顾名思义就是锁定的意思。 “锁”的作用是什么？ 在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与). 悲观锁:行级锁定(行锁) 谁先操作某个数据行,就会持有<这行>的(X)锁. 乐观锁: 没有锁
同时开启两个mysql窗口，一个先写入数据但不提交commit，另外一个编辑一样的数据会卡住
1）都关闭autocommit
[world]>set autocommit=0; [world]>select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+
[world]>update city set Name='xxx' where id=100; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction [world]>
影响到数据的读取,默认的级别是 RR模式. transaction_isolation 隔离级别(参数) [world]>select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.00 sec) 负责的是,MVCC,读一致性问题 RU : 读未提交,可脏读,一般不建议使用 RC : 读已提交,可能出现幻读,可以防止脏读. RR : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁) SR : 可串行化,可以防止死锁,但是并发事务性能较差 补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重. 例如: [world]>select * from city where id=999 for update; [world]>commit;
项目背景: 2台 IBM X3650 32G ,原来主从关系,2年多没有主从了,"小问题"不断(锁,宕机后的安全) MySQL 5.1.77 默认存储引擎 MyISAM 数据量: 60G左右 ,每周全备,没有开二进制日志 架构方案: 1. 升级数据库版本到5.7.20 2. 更新所有业务表的存储引擎为InnoDB 3. 重新设计备份策略为热备份,每天全备,并备份日志 4. 重新构建主从 结果: 1.性能 2.安全方面 3.快速故障处理
1、存储引擎相关
查看 show engines; show variables like 'default_storage_engine'; select @@default_storage_engine; 如何指定和修改存储引擎 (1) 通过参数设置默认引擎 (2) 建表的时候进行设置 (3) alter table t1 engine=innodb;
共享表空间 innodb_data_file_path 一般是在初始化数据之前就设置好 例子: innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend 独立表空间 show variables like 'innodb_file_per_table';
查询 select @@innodb_buffer_pool_size; show engine innodb status\G innodb_buffer_pool_size 一般建议最多是物理内存的 75-80%
作用
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
查询
select @@innodb_flush_log_at_trx_commit;
参数说明
1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush 到操作系统的文件系统缓存 fsync到物理磁盘. 0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且每秒fsync磁盘一次； 2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。 -------- The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit. With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash. With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash. -------
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method
作用
控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存
查看
show variables like '%innodb_flush%';
参数值说明
O_DIRECT :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC :日志缓冲区写磁盘,不走 OS buffer
最高安全模式 innodb_flush_log_at_trx_commit=1 Innodb_flush_method=O_DIRECT 最高性能: innodb_flush_log_at_trx_commit=0 Innodb_flush_method=fsync
innodb_log_buffer_size=16777216 innodb_log_file_size=50331648 innodb_log_files_in_group = 3
RR模式(对索引进行删除时): GAP: 间隙锁 next-lock: 下一键锁定 例子: id（有索引） 1 2 3 4 5 6 GAP： 在对3这个值做变更时，会产生两种锁，一种是本行的行级锁，另一种会在2和4索引键上进行枷锁 next-lock： 对第六行变更时，一种是本行的行级锁，在索引末尾键进行加锁，6以后的值在这时是不能被插入的。 总之： GAP、next lock都是为了保证RR模式下，不会出现幻读，降低隔离级别或取消索引，这两种锁都不会产生。 IX IS X S是什么?
I have a dream so I study hard!!!
