MySQL学习笔记Atomic（原子性） 所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。主要和undo，redo有关。 Consistent（一致性） 如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。 Isolated（隔离性） 事务之间不相互影响。主要和隔离级别,Lock,MVCC有关。 Durable（持久性） 事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。主要和redo有关 简单地理解，当A给B转账100元钱时，数据库必须做到： 原子性（Atomicity）：A的账户减少100元，B的账户增加100元，这两件必须同时发生，像原子一样不可分割； 一致性（Consistency）：转账之后，A和B的账户总和必须跟转账前一致； 隔离性（Isolation）：A和B之间的转账不影响其它任何账户； 持久性（Durability）：转账记录必须长久存在，即使发生断电等故障也不能消失。
事务的生命周期（事务控制语句）
事务的开始
begin 说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。 标准事物语句： insert update delete select
事务的结束
commit：提交事务 mysql>begin; mysql>delete from city where id>10; mysql>commit; 完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。 rollback ：回滚事务 mysql>begin; mysql>delete from city where id>10; mysql>rollback; 将内存中，已执行过的操作，回滚回去，多级回滚可以给DML语句加save point;
自动提交策略（autocommit）
查看：select @@autocommit;1为开启，0为关闭。 注： 自动提交是mysql5.6之后的新特性 自动提交是否打开，一般在有事务需求的MySQL中，将其关闭 不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能 临时关闭： set autocommit=0; 临时关闭当前会话 set global autocommit=0;临时关闭全局会话 永久关闭： vim /etc/my.cnf autocommit=0 应用场景： autocommit=1,一般适合于非交易类业务场景。 如果是交易类业务： 方案1：autocommit=0;commit,手工提交才有效。 autocommit=1；每次想要发生事务性型操作，begin和commit都手工操作。
隐式事务控制语句
隐式提交： 1.设置了autocommit=1 2.DDL,DCL等非DML语句时会触发隐式提交 举例： begin DML1 DML2 执行下方语句会在这里自动添加一个commit;导致隐式提交 drop databases 库名;-------->非DML语句 在事务执行期间不要触发能够触发隐式提交的语句 3.导致隐式提交的非事务语句： DDL语句： （ALTER、CREATE 和 DROP） DCL语句： （GRANT、REVOKE 和 SET PASSWORD） 锁定语句：（LOCK TABLES 和 UNLOCK TABLES） 导致隐式提交的语句示例： TRUNCATE TABLE LOAD DATA INFILE SELECT FOR UPDATE 隐式回滚： mysql>begin; mysql>DML1 mysql>关闭会话，数据库宕机，事物语句执行失败， DML1没有执行操作成功，相当于回滚了
模拟事务流程操作
1、检查autocommit是否为关闭状态 select @@autocommit; 或者： show variables like 'autocommit'; 2、开启事务,并结束事务 begin use school; 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，ib_logfile1，默认50M，轮询使用，记录的是数据页的变化。 redo log buffer：redo内存区域 2.数据页存储位置 ibd：存储数据行和索引 buffer pool：缓冲区池,数据页和索引页的缓冲，默认128M 3.LSN : 日志序列号 存在于磁盘数据页,redo文件,buffer pool,redo buffer MySQL每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动 4.WAL : write ahead log 日志优先于数据页，写的方式实现持久化 5.脏页: 内存脏页,内存（buffer pool）中发生了修改,没回写入到磁盘之前,我们把内存页称之为脏页. 6.CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作 7.TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务的生命周期. 8.UNDO：位置ibdata1,存储了事务工作过程中的回滚信息。 9.MVCC：在每个事务启动时，针对当前数据行申请一个全新的快照，尽可能提高事务多版本并发的机制。
InnoDB事务的工作流程
Redo log
1.概念： redo,顾名思义“重做日志”，是事务日志的一种. 2.作用 在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用 3.redo日志位置 redo的日志文件：iblogfile0 iblogfile1 4.redo buffer redo的buffer:数据页的变化信息+数据页当时的LSN号 LSN是日志序列号，磁盘数据页->内存数据页->redo buffer->redolog 5.redo的刷新策略 commit; 刷新当前事务的redo log buffer到磁盘，还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘,此时为了区分 不同状态的redo,会加一些比较特殊的标记（是否提交标记） 6.CSR(自动故障恢复)过程 补充：redo存储的是在事务工作过程中数据页变化。commit时会立即写入磁盘，日志落盘则成功commit。 正常MySQL工作过程中，主要的工作时快速提供持久化功能，MySQL出现crash-safe异常宕机时，主要提供的是前滚功能。 扩展补充： a.双一标准innodb_flush_log_at_trx_commit=0/1/2; 值等于1表示在每次事务提交时，会立即刷新redo到磁盘，commit才能成功。 值等于0表示每秒刷新redo buffer到os buffer，再fsync()到磁盘，异常宕机时，会有可能导致丢失1秒内的事务。 值等于2每次事务提交时，redo buffer到os buffer，再每秒fsync()到磁盘，异常宕机时，会有可能导致丢失1秒内的事务。 默认是1，redo buffer还和操作系统缓存机制有关，所以刷写策略可能和innodb_flush_method参数有一定的关系
undo 回滚日志
1.作用 在事务ACID过程中，实现的是“A” 原子性的作用 另外CI也依赖于undo 在rolback时,将数据恢复到修改之前的状态 在CSR实现的是,先redo前滚，再undo前滚。 2.一致性快照：每个事物开启时，都会通过undo生成一个一致性快照。 undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备 undo在生成过程中，也会记录redo buffer日志.
CSR过程解析
MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致 情况一: 我们做了一个事务,begin;update;commit. 1.在begin ,会立即分配一个TXID=tx_01. 2.update时,会将需要修改的数据页(dp_01）,加载到data buffer中，此时LSN=101 3.相关线程,会进行dp_01数据页修改更新,并更新LSN=102 4.专用日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer 5. 执行commit时,LGWR日志专用写线程会将redobuffer信息写入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回滚，即CSR
隔离级别和锁
1.作用 在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与). 2.事物隔离性介绍 transaction_isolation 事物隔离性 主要是影响到数据的读取,这里的读指存储引擎的读，MVCC读一致性问题，默认的级别是 RR模式. RU（READ-UNCOMMITTED） : 读未提交 RC（READ-COMMITTED） : 读已提交 ，默认级别 RR（REPEATABLE-READ） : 可重复读 SR : 可串行化 3.隔离级别说明： RU : 读未提交。出现的问题：脏页读,不可重复读，幻读 RC: 读已提交。不可重复读，幻读 RR: 可重复读,有可能出现幻读，但是可以防止出现 SR : 可串行化,可以防止死锁,但是并发事务性能较差 4.参数修改 查看：select @@tx_isolation; 临时修改： set global transaction_isolation='READ-COMMITTED'; 永久修改：vim /etc/my.cnf transaction_isolation=READ-COMMITTED; 5.结论： a.脏读：在session2事务中读取到session1未提交的脏数据，这个现象就是脏读。对于脏读，在生产业务中是一般不允许出现的。 b.不可重复读：session2中的事务中，执行相同查询命令时，读到了session1正在发生变化的数据，对于事务的隔离性 和数据最终一致性要求比较高的业务，不允许出现。 c.幻读：在session1事务更新操作，session2事务在session1事务commit之前commit，则session1事务出现了别的插入数据的幻行。 d.防止不可重复读现象，利用的就是undo的一致性快照读。是MVCC重要功能。 e.通过RR隔离级别，可以解决99%以上的幻读，为了更加严谨，利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)。 GAP锁就是防止在session1事务操作相应行时，session2事务操作表的行的一种间隙锁，配合next-lock，可解决幻读。 6.锁的分类 悲观锁:行级锁定(行锁)，谁先操作某个数据行,就会持有这行的(X)锁.其他人不能修改这行锁。 乐观锁: 没有锁 对象锁粒度： a.MDL(元数据锁) b.TABLE_lock:表锁 c.record(row) lock:行锁，索引锁，锁定剧集索引 d.GAP：间隙锁，RR级别 e.Next-lock:下一键锁，普通辅助索引的范围锁 IS:意向共享锁，表级别 S:共享锁，读锁，行级别 IX：意向排他锁，表级别 X:排他锁，写锁，行级别
案例：架构改造项目
项目背景: 2台 IBM X3650 32G ,原来主从关系,2年多没有主从了,"小问题"不断(锁,宕机后的安全) MySQL 5.1.77 默认存储引擎 MyISAM 数据量: 60G左右 ,每周全备,没有开二进制日志 架构方案: 1. 升级数据库版本到5.7.20 2. 更新所有业务表的存储引擎为InnoDB 3. 重新设计备份策略为热备份,每天全备,并备份日志 4. 重新构建主从 结果: 1.性能 2.安全方面 3.快速故障处理
扩展
RR模式(对索引进行删除时): GAP: 间隙锁 next-lock: 下一键锁定 例子: id（有索引） 1 2 3 4 5 6 GAP： 在对3这个值做变更时，会产生两种锁，一种是本行的行级锁，另一种会在2和4索引键上进行枷锁 next-lock： 对第六行变更时，一种是本行的行级锁，在索引末尾键进行加锁，6以后的值在这时是不能被插入的。 总之： GAP、next lock都是为了保证RR模式下，不会出现幻读，降低隔离级别或取消索引，这两种锁都不会产生。