MySQL-数据库（5）

本文主要讲述了MySQL的并发控制与事务的隔离级别。

3.5并发控制与事务的隔离级别

3.5.1事务

3.5.1.1事务基础知识

事务管理的意义：保证数据操作的完整性；

事务的特性(ACID)：

• 原子性：事务的整个操作是一个整体，不可以分割，要么全部成功，要么全部失败；
• 一致性：事务操作的前后，数据表中的数据没有变化（按示例中解释为A转钱给B的前后两个人钱的总金额不会改变）；
• 隔离性：事务操作是相互隔离不受影响的；
• 持久性：数据一旦提交，不可改变，永久的改变数据表数据。

在 MySQL 命令行的默认下，事务采用自动提交（autocommit=1）模式。意味着，当你执行一个修改sql语句，MySQL 会立刻更新存储到磁盘中。也就是会立马执行commit操作。
因此开启一个手动事务必须使用begin，start transaction或者set autocommit=0：

事务回滚，即在事务运行的过程中发生了某种故障，系统将事务中对数据库的所有已完成的操作（对数据库的更新操作）全部撤销，回滚到事务开始时的状态。（这条语句也标志着事务的结束）

start TRANSACTION;  # 开启事务

commit; # 提交事务

rollback； # 事务回滚

自定义异常

#定义一个err变量并初始值为0  
declare err int default 0; 

#当有sql异常的时候，sql继续往下执行，并将变量err的值改变为1   
declare continue handler for sqlexception set err =1; 

(事务是并发控制的基本单位
隔离级别越高，一致性程度越高，并发度越低。

3.5.1.2事务隔离

数据的不一致性包括

• 丢失修改(lost update)
• 读脏数据(dirty read)
• 不可重复读(non-repeatable read)
• 幻读(phantom read)

MySQL的事务隔离级别从低到高分以下四级：

• 读未提交（READ UNCOMITTED）
• 读已提交（READ COMMITTED）
• 可重复读（REPEATABLE READ）
• 可串行化（SERIALIZABLE）

上表说明，最低的隔离级别不能避免读脏、不可重复读和幻读，而最高的隔离级别，可保证多个并发事务的任何调度，都不会产生数据的不一致性，但其代价是并发度最低。

# 查询MySQL的事务隔离级别 
select @@GLOBAL.transaction_isolation, @@transaction_isolation; # 前者为全局变量，后者为本会话期内的变量

# 设置事务的隔离级别
set session transaction isolation level read uncommitted;
# REPEATABLE-READ(缺省的事务隔离级别)

# 使得事务暂时休眠（单位：秒）
set @n = sleep(5);

说明：MySQL8的事务隔离级别变量名与之前的版本是不同的，请不要参考的旧版本。

3.5.1.3事务故障和恢复

create procedure pro_transfer(in id_from int, in id_to int, in money int)
modifies sql data
begin
    -- 捕获异常，进行事务回滚
    declare exit handler for sqlexception rollback;
    start transaction;
    update tb_account set balance=balance+money where id=id_to;
    update tb_account set balance=balance-money where id=id_from;
    commit;
end

恢复策略：

基于日志恢复：

基于检查点的恢复方法：

checkpoint之前commit不用管
checkpoint之后：
提交了(commit) -> redo (只要在事故发生前结束了事务，恢复之后的数据应该是redo之后的数据)
没有提交 -> undo（撤销操作，恢复数据之后应该是一开始的数据）

rollback也算事务提交的一种的方式。

日志记录格式

• 表示事务对数据元素A执行了更新操作。 更新前的值
• 表示事务已经开始。此时DBMS已经完成对事务的初始化工作，如分配事务工作区等
• 表示事务已经提交。（该事务对数据库的修改必须永久化）事务提交时其更新的数据都写到了数据缓冲区中，但是由于不能控制缓冲区管理器何时将缓冲块从内存写到磁盘，因此看到该日志记录时，通常不能确定更新是否写到了磁盘上。
• 表示事务已经中止，即执行失败。此时，若所做的更新已经反映到磁盘上，DBMS必须通过undo来消除对磁盘数据库得到影响。

3.5.2并发控制

3.5.2.1脏读

产生读脏的原因，是事务t1读取数据时，修改该数据的事务t2还没有结束(commit或rollback，统称uncommitted),且t1读取的时间点又恰在t2修改该数据之后。【读了未提交的数据】

3.5.2.2不可重复读

不可重复读(unrepeatable read)，是指一个事务(t1)读取到某数据后，另一个事务(t2)修改(update)了该数据，事务t1并未修改该数据，但当t1再次读取该数据时，发现两次读取的结果不一样。【读两次的结果不同】

3.5.2.3幻读

幻读是指一个事务(t1)读取到某数据后，另一个事务(t2)作了**insert**或**delete**操作，事务t1再次读取该数据时，魔幻般地发现数据变多了或者变少了(记录数量不一致)；
而不可重复读限指事务t2作了**update**操作，致使t1的两次读操作读到的结果(数据的值)不一致。

下图为幻读，查询得到的数据多了一条，是由于事务Binsert了新的数据。

3.5.2.4丢失更新

如果两事务都在对数据进行修改则会导致另外的问题：丢失更新。【自己的更新被别人覆盖】

丢失更新使用 行锁 来解决

3.5.3锁

3.5.3.1锁类别

锁的主要作用是管理共享资源的并发访问，锁可以用于实现事务的隔离。
为什么加锁？为了避免多个事务同时操作数据库导致数据异常，一般会通过锁机制解决。

MySQL的select语句支持for share和for update短语，分别表示对表加共享(Share)锁和写(write)锁，共享锁也叫读锁，写锁又叫排它锁（exclusive locks）。

不管share还是update锁，都是在事务结束时才释放。

表锁：偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发送锁冲突的概率最高，并发度最低。

3.5.3.2封锁协议

   |   ✔ |  |  |   ✔ |  |  |

| 二级封锁协议 | | ✔ | ✔ | | ✔ | ✔ | |
| 三级封锁协议 | | ✔ | | ✔ | ✔ | ✔ | ✔ |

• 一级封锁协议：修改数据加x锁直到事务结束才释放。在此协议中，仅仅是读数据是不需要加锁的，所以只能解决丢失修改问题，不能解决脏读和不可重复读。
• 二级封锁协议：在一级封锁的基础上，加了一条：T事务在读取数据R之前必须先对其加上S锁，读完释放S锁。可以解决丢失修改和脏读（加了读锁就可以防止在读的期间其他事务进行修改，但是读完之后，事务结束之前，依然可能会其他事务进行修改，导致不可重复读）。
• 三级封锁协议：一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。：解决了丢失修改、脏读和不可重复读的问题。

3.5.3.3加锁

# 加共享锁
select * from t1 for share;
select ... lock in share mode;  

# t1加共享锁  t2加update锁【排他锁】
select * from t1,t2 for share of t1 for update of t2;

# 读锁
lock table 表名 read;

# 解锁
unlock tables;

3.5.4串行化

多个事务并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同。两个事务t1,t2并发执行，如果结果与t1→t2串行执行的结果相同，或者与t2→t1串行执行的结果相同，都是正确的(可串行化的)。

serializable这种隔离级别会大大降低并发度，在实践中极小使用。