Mysql之事务

1.　　事务的特性

　　　　InnoDB存储引擎中的事务完全符合ACID特性。

　　　　原子性　　atomicity

　　　　　　事务中任何一个SQL语句执行失败，已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态　　　　　　

　　　　一致性　　consistency

　　　　　　事务一致性的单位，如果事务中某个动作失败了，系统可以自动撤销事务，返回初始化的状态　　　　　　

　　　　隔离性　　isolation

　　　　　　事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，通常使用锁来实现

　　　　持久性　　durability　　

　　　　　　事务一旦提交，其结果就是永久性的，即使发生宕机等故障，数据库也能将数据恢复

2.　　事务的分类

　　　　1.　　扁平事务

　　　　　　　　实际生产环境下，使用最频繁的事务

　　　　　　　　由BEGIN开始，由COMMIT或ROLLBACK结束

　　　　　　　　　　　　　　　

　　　　2.　　带有保存点的扁平事务

　　　　　　　　保存点用来通知系统应该记住事务当前的状态，以便当之后发生错误时，事务能回到保存点当时的状态

　　　　　　　　

 

　　　　3.　　链事务

　　　　　　　　提交事务操作和开始下一个事务操作将合并为一个原子操作

　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　4.　　嵌套事务

　　　　　　　　

　　　　5.　　分布式事务

　　　　　　　　一个在分布式环境下运行的扁平事务　　　　　　

3.　　事务的实现

　　　　1.　　redo

　　　　　　　　1.　　基本概念

　　　　　　　　　　　　重做日志用来实现事务的持久性

　　　　　　　　　　　　由两部分组成：一是内存中的重做日志缓冲，其是易失的；二是重做日志文件，其是持久的。

　　　　　　　　　　　　参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略

　　　　　　　　　　　　　　0：表示事务提交时不进行写入重做日志操作

　　　　　　　　　　　　　　1：表示事务提交时必须调用一次fsync操作

　　　　　　　　　　　　　　2：表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。当mysql数据库发生宕机而操作系统不发生宕机，并不会导致事务的丢失

　　　　　　　　　　　　　　

 

　　　　　　　　2.　　log block

　　　　　　　　　　　　重做日志文件以块的方式保存，每个块的大小为512字节

　　　　　　　　　　　　

　　　　　　　　　　　　日志头12字节，日志尾8字节，日志快存储的大小为492字节

　　　　　　　　3.　　log group

　　　　　　　　　　　　由多个重做日志文件组成

　　　　　　　　4.　　redo log 格式

　　　　　　　　　　　　

 

　　　　　　　　5.　　LSN

　　　　　　　　　　　　日志序列号

　　　　　　　　　　　　LSN表示的含义：

　　　　　　　　　　　　　　重做日志写入的总量

　　　　　　　　　　　　　　checkpoint的位置

　　　　　　　　　　　　　　页的版本

　　　　　　　　　　　　查看LSN的情况

　　　　　　　　　　　　　　show engine innodb status;

　　　　　　　　　　　　　　Log sequence number表示当前的LSN

　　　　　　　　　　　　　　Log flushed up to表示刷新到重做日志文件的LSN

　　　　　　　　　　　　　　Last checkpoint at表示刷新到磁盘的LSN

　　　　　　　　6.　　恢复

　　　　　　　　　　　　不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作

　　　　　　　　　　　　恢复从上一个checkpoint的LSN到现在LSN的日志

　　　　2.　　undo

　　　　　　　　1.　　基本概念

　　　　　　　　　　　　undo存放在数据库内部的一个特殊段中，undo段位于共享表空间内。

　　　　　　　　　　　　可以通过py_innodb_page_info.py工具来查看当前共享表空间中undo的数量

　　　　　　　　　　　　undo作用：

　　　　　　　　　　　　　　1.　　回滚操作

　　　　　　　　　　　　　　2.　　MVCC

　　　　　　　　　　　　undo会产生redo log

　　　　　　　　2.　　undo存储管理

4.　　事务控制语句

　　　　默认设置下，事务都是自动提交。

　　　　1.　　常用语句

　　　　　　　　start transaction; |  begin;　　显示地开启一个事务，在存储过程中只能使用start transaction语句开启一个事务

　　　　　　　　commit;  |  commit work;　　提交一个事务

　　　　　　　　rollback;  |  rollback work;　　回滚一个事务

　　　　　　　　savepoint identifier;　　允许在事务中创建一个保存点，一个事务可以有多个保存点

　　　　　　　　release savepoint identifier;　　删除一个事务的保存点，当没有一个保存点执行这句语句时，会抛出一个异常　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　　　　　rollback to identifier;　　回滚到保存点

4.　　隐式提交的SQL语句　　　　　　　　　　　　　　　　　　

　　　　

　　　　注意：　　truncate table语句，是不能被回滚的

5.　　事务的隔离级别

　　　　1.　　设置事务隔离级别

　　　　　　　　1.　　实时修改

　　　　　　　　　　　　set global | session transaction isolation level [ read uncommitted | read committed | repetable read | serializable]

　　　　　　　　2.　　配置文件修改

　　　　　　　　　　　　[mysqld]

　　　　　　　　　　　　transaction-isolation = read-committed

　　　　2.　　查看事务隔离级别

　　　　　　　　select @@tx_isolation \G;

　　　　　　　　select @@global.tx_isolation \G;

　　　　3.　　数据库事务的隔离级别，从低到高有下面四种

　　　　　　　　1.　　Read uncommitted　　读未提交

　　　　　　　　　　　　一个事务可以读取另一个未提交事务的数据

　　　　　　　　　　　　事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

　　　　　　　　　　　　分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。　　　　　　　　　　　　

　　　　　　　　2.　　Read committed　　读提交

　　　　　　　　　　　　一个事务要等另一个事务提交后才能读取数据

　　　　　　　　　　　　事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他买单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

　　　　　　　　　　　　分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。　　　　　　　　

　　　　　　　　3.　　Repeatable read　　重复读

　　　　　　　　　　　　事务开启时，不再允许修改操作

　　　　　　　　　　　　事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

　　　　　　　　　　　　分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。　　　　　　　　　　　　

　　　　　　　　　　　　什么时候会出现幻读？

　　　　　　　　　　　　事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

　　　　　　　　　　　　那怎么解决幻读问题？Serializable

　　　　　　　　4.　　Serializable　　序列化

　　　　　　　　　　　　Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。