MySQL事物以及加锁分析
数据库事务具有ACID四大特性。ACID是以下4个词的缩写:
- 原子性(atomicity) :事务最小工作单元,要么全成功,要么全失败 。
- 一致性(consistency): 事务开始和结束后,数据库的完整性不会被破坏 。
- 隔离性(isolation) :不同事务之间互不影响,四种隔离级别为RU(读未提交)、RC(读已提交)、RR(可重复读)、SERIALIZABLE (串行化)。
- 持久性(durability) :事务提交后,对数据的修改是永久性的,即使系统故障也不会丢失。
注:
总结来说,事务的隔离性由多版本控制机制和锁实现,而原子性、一致性和持久性通过InnoDB的redo log、undo log和Force Log at Commit机制来实现。
redo log用于在崩溃时恢复数据,undo log用于对事务的影响进行撤销,也可以用于多版本控制。
而Force Log at Commit机制保证事务提交后redo log日志都已经持久化。
Undo Log
数据库崩溃重启后需要从redo log中把未落盘的脏页数据恢复出来,重新写入磁盘,保证用户的数据不
丢失。当然,在崩溃恢复中还需要回滚没有提交的事务。由于回滚操作需要undo日志的支持,undo日
志的完整性和可靠性需要redo日志来保证,所以崩溃恢复先做redo恢复数据,然后做undo回滚。
undoLog用于回滚操作. 在数据从Buffer Pool 脏页中落盘的时候,同时也会记录undo Log. undo Log中记录历史版本。
eg:
insert操作: insert into user (id, name, age) values (1,'zhangsan', 23); undo Log 记录为 null. 代表如果insert 操作要回滚的话,需要把这条记录回滚为null。
update操作: 假如上面insert 已经完成了. update user set name = 'zhangfei' where id = 1; undo Log 中记录的是修改之前的信息 id 为1, name为 'zhangsan', age 为23.
delete操作: 假如上面update 已经完成了,然后删除了id为1的数据 delete from user where id =1; undo Log 中记录的也是原来的数据 : id 为1, name为 'zhangfei', age 为23.
将Buffer Pool 数据记录到redu Log 之前,会先记录 之前版本到undo Log中。
commit以后崩溃,不存在历史版本,直接从redu log 回复就行。
如果commit 之前就崩溃了,就要从redo log 中恢复undo log, 然后从undo log 中找到历史记录并恢复。
FYI:
- mysql 崩溃重启好之后,首先会从redo log 中找是否有undo log, 如果有undo log 会先回滚。然后根据redo log 恢复数据。
- undolog作用 是回滚历史版本,另一个作用是MVCC, undo log 由mysql purge线程来删除。 存储在系统表空间的回滚段当中。
- undolog 的完整性是靠redo log 保证的。
并发存在是由于事物的隔离性
隔离性---事务并发问题
在事务的并发操作中可能会出现一些问题:
- 丢失更新:两个事务针对同一数据都发生修改操作时,会存在丢失更新问题。
- 脏读:一个事务读取到另一个事务未提交的数据。
- 不可重复读:一个事务因读取到另一个事务已提交的update或者delete数据。导致对同一条记录
读取两次以上的结果不一致。 - 幻读:一个事务因读取到另一个事务已提交的insert数据。导致对同一张表读取两次以上的结果不
一致。
事务隔离级别
四种隔离级别(SQL92标准):
现在来看看MySQL数据库为我们提供的四种隔离级别(由低到高):(RC 和 RR 都是MVCC)
① Read uncommitted (读未提交):最低级别,任何情况都无法保证。【无并发控制,以上事务并发问题的四种情况都无法避免】
② Read committed (RC,读已提交):可避免脏读的发生。
③ Repeatable read (RR,可重复读):可避免脏读、不可重复读的发生。
(注意事项:InnoDB的RR还可以解决幻读,主要原因是Next-Key(Gap)锁,只有RR才能使用Next-Key锁)
④ Serializable (串行化):可避免脏读、不可重复读、幻读的发生。
(由MVCC降级为Locking-Base CC)
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交(read-uncommitted) | 是 | 是 | 是 |
读提交(read-committed) | 否 | 是 | 是 |
可重复读(repeatable-read) | 否 | 否 | 是 |
串行化(serializable) | 否 | 否 | 否 |
eg: 考虑一个现实场景:
管理者要查询所有用户的存款总额,假设除了用户A和用户B之外,其他用户的存款总额都为0,A、B用
户各有存款1000,所以所有用户的存款总额为2000。但是在查询过程中,用户A会向用户B进行转账操
作。转账操作和查询总额操作的时序图如下图所示。
a.在Read uncommited 下,
session1,开启事物 ,先查A用户有1000,
session2, 开启事物, update account set money=1100 where name = 'B';
session1 查询B用户的钱有1100, 出现了脏读。
--设置隔离级读未提交(read-uncommitted)
mysql> set session transaction isolation level read uncommitted;
--session 1
mysql> start transaction ; select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
--session 2
mysql> start transaction; update tacount set acount=1100 where aname='b';
--session 1
mysql> select * from tacount where aname='b';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 2 | b | 1100 |
+----+-------+--------+
b. 串行化级别,任何语句都会加锁
--设置隔离级别为串行化(serializable) 死锁演示
mysql> set session transaction isolation level serializable;
--session 1
mysql> start transaction;select * from tacount where aname='a';
--session 2
mysql> start transaction ; update tacount set acount=900 where aname='b';
-- session 1
mysql> select * from tacount where aname='b';
-- session 2
mysql> update tacount set acount=1100 where aname='a';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction
c. RR隔离级别下
-- 显示当前隔离级别为 REPEATABLE-READ MySQL默认隔离级别
mysql> select @@tx_isolation;
-- session 1
mysql> start transaction ; select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
-- session 2
mysql> start transaction; update tacount set acount=1100 where aname='a';
-- session 1
mysql> select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
-- session 2 提交事务
mysql> commit;
-- session 1 显示在session 1 事务开始时的数据
mysql> select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
-- session 1 显示最新数据, 因为加了 for update 是当前读。(这里需要注意)
mysql> select * from tacount where aname='a' for update;
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1100 |
+----+-------+--------+
d. RC
-- 设置事务隔离级别为REPEATABLE-COMMITTED 读已提交
-- session 1
mysql> set session transaction isolation level read committed;
mysql> start transaction ; select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
-- session 2
mysql> set session transaction isolation level read committed;
mysql> start transaction; update tacount set acount=1100 where aname='a';
-- session 1
mysql> select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1000 |
+----+-------+--------+
-- session 2 提交事务
mysql> commit;
-- session 1 显示最新事务提交后的数据
mysql> select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
| 1 | a | 1100 |
+----+-------+--------+
#在当前session中设置隔离级别
set session transaction isolation level read uncommitted;
#查看MySQL默认隔离级别
select @@tx_isolation;
关闭binlog配置
#bin-log
#log-bin=mysql-bin
#server-id=103
#binlog-ignore-db=finup
#binlog-do-db=phone
在MVCC中读取 分为 快照度 和当前读(新增,修改,删除)
如果读取的行正在执行DELETE或UPDATE操作,这时读取操作不会因此去等待行上锁的释放。相反地,
InnoDB会去读取行的一个最新可见快照。
间隙锁,锁的是索引之间的间隙。