mysql 事件存储过程

转载

mob64ca1405a060 2024-07-09 06:48:18

文章标签 mysql 事件存储过程 mysql innodb 事务 undo 文章分类 MySQL 数据库

本文笔记摘自于《Mysql 技术内幕 InnoDB存储引擎》

Mysql InnoDB笔记(1)——体系架构 Mysql InnoDB笔记(2)——表 Mysql InnoDB笔记(3)——索引 Mysql InnoDB笔记(4)——锁 Mysql InnoDB笔记(5)——事务 Mysql InnoDB笔记(6)——备份和性能调优

事务

事务特性

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

事务分类

扁平事务（Flat Transactions）
这种是我们最常用的事务类型，只有一个保存点。
带有保存点的扁平事务（Flat Transactions with Savepoints）
有多个保存点，可以设计保存点，并可回滚到某个保存点。
链事务（Chained Transactions）
提一个事务的同时开启下一个事务。
嵌套事务（Nested Transactions）
分布式事务（Distributed Transactions）

嵌套事务和分布式事务一般都不会直接用，都是在应用层做控制。

事务的实现

通过【锁】实现【隔离性】，【redo log】实现【持久性】，【undo log】实现【原子性、一致性】。

redo log是物理日志，恢复提交事务修改的页操作，undo log是逻辑日志，回滚行记录到某个版本。

redo log

在事务commit前，会把本次事务的操作记录到redo log buffer里，然后刷到磁盘的redo log file。在commit出异常的时候，可以从redo log恢复数据，保证数据的持久性。redo log基于页进行管理。

redo log是顺序写的，因此性能比较高。
innodb_flush_log_at_trx_commit可以设置从redo log buffer刷到redo log file的策略。默认是在事务提交时，强制调用fsync刷到磁盘。
注意一下redo log和bin log的区别

log block
redo log以log block的形式存储在redo log buffer里，每个block大小为512字节。
log group
redo log以block为单位写入redo log file,一个log group会包含多个redo log file,log group是一个逻辑上的概念。
redo log 格式
不同的操作类型会有不同的格式
LSN（Log Sequence Number）
在redo log file和redo log页都有记录LSN，用于判断该页是否要进行恢复操作。

undo log

undo log存放于undo段（undo segment）,位于共享表空间内。

MVCC（多版本控制）也是通过undo segment来实现。
一致性非锁定读就是从undo segment读取快照。
undo log也会产生redo log

InnoDB有rollback segment，每个回滚段记录了1024个undo log segment，而undo页的申请在undo log segment中进行。

通过innodb_undo_logs设置rollback segment的数量，默认128

事务提交时，会把undo log放入purge的列表中，留给后续的purge操作，并且判断undo log所在的页是否可以重用，若可以则分配给下个事务使用。

事务提交后并不会马上删除undo log，这个事情交给purge线程来处理

undo log 格式
有2种：insert undo log和update undo log

因insert操作对其他事务不可见，所以insert undo log在事务提交后，可以马上删除，不需要purge操作。
通过字典表INNODB_TRX_ROLLBACK_SEGMENT和INNODB_TRX_UNDO可以查看undo相关信息

purge

purge用于完成最终的update和delete操作。对于按事务提交顺序挂在history list的undo log，purge会去列表尾端找到最先提交的事务，然后找到对应的undo page，判断相应的行的undo log有没有事务引用，如果没有则清除，清除后，此页可以被重用。

history list的长度会影响性能，innodb_max_purge_lag用于控制history list的长度，innodb_purge_batch_size用于控制每次操作清理的undo page数量。

group commit

将多个事务提交的fsync操作放在同一个fsync里完成，以提高磁盘效率。

事务控制语句

START TRANSACTION | BEGIN
COMMIT
ROLLBACK
SAVEPOINT identifier
RELEASE SAVEPOINT identifier
ROLLBACK TO [SAVEPOINT] identifier
SET TRANSACTION [READ UNCOMMITTED | READ COMMITTED | REPTATABLE READ | SERIALIZABLE]

了解一下隐式提交的SQL语句，DDL是其中一种，理解TRUNCATE TABLE和DELETE的区别，那就是TRUNCATE TABLE无法回滚。

事务的隔离级别

READ UNCOMMITTED
会产生脏读、不可重复读和幻读。
READ COMMITTED
解决了脏读，但会出现不可重复读和幻读。
REPTATABLE READ
INNODB默认的隔离级别。解决了脏读和不可重复读，但会出现幻读。

在RR级别下，其他数据库会产生幻读，但INNODB不会，因为INNODB使用了Next-Key Lock的算法，从而避免了幻读。
一个问题：在RR级别下使用快照读（MVCC）来支持可重复读，那为何A事务 update tb_test set a=2 where a=1;返回0呢？（A事务先开启，读取select a from tb_test where a=1;返回1；然后B事务开启，update tb_test set a=2 where a=1;提交）

答案是：要先理解快照读和当前读。当前读就是读最新提交的数据，那什么情况下会执行当前读呢？就是下面这些了，我理解是在当前事务执行。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…); 
update table set ? where ?; 
delete from table where ?;