事务
事务是用户定义的一个数据库操作序列,这些操作要么全做,要么全不做,是一个不可分割的工作单位,它可以是一条SQL语句(默认)、一组SQL语句、或整个程序,要多条语句一个事务,需要通过 ‘begin’(等价start transaction)、‘commit’、‘rollback ’来控制事务,并且它的开始可以由用户显示的控制,如果用户没有显示地定义事务,则由数据库管理员默认规定自动划分事务。
事务的ACID特性
事务具有4个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、和持续性(Durability)。这四个特性简称为ACID特性(ACID properties)。
- 原子性:多个SQL作为一个整体运行,不可分割。
- 一致性:一个事物内结果应当一致
- 隔离性:一个事务的执行不能被其他事务干扰
- 持续性:事务一旦提交,事务内的修改将应当永久生效
事务控制语言
begin; // 表示事务的起点 等价方式: `start transaction`
sql1
sql2
...
commit(提交,表示结果都将生效) 和 rollback(回滚,用来撤销事务内的更改) 表示事务的终点
事务隔离性
事务具有不同的隔离级别,隔离级别越低,并发性越好,但数据的一致性差,反之则并发性差,但数据一致性高。
由低到高:
读未提交 < 读提交 < 可重复读(mysql默认) < 序列化读
脏读(读未提交)
脏读是由于事务一修改某一数据并将其写回磁盘,事务二读取同一数据后,事务一由于某种原因被撤销,这时被事务一修改过的数据恢复原值,事务二读到的数据就为“脏”数据,即不正确的数据,将隔离级别提升到“读提交”,可以避免脏读。
如下图:
如上图,事务一将小红的工资修改为700, 事务二读到小红的工资为700,而事务一由于某种原因撤销了修改,小红的工资恢复为1000,这时事务二读到的数据为700,与数据库内容不一致,这就是脏读,将隔离级别提升到“读提交”,可以避免脏读。
不可重复读
不可重复读是指事务一读取数据后,事务二执行更新操作,使事务一无法再现前一次读取结果。
如下图:
事务二查询小红工资时(1000),事务一修改了小红工资(700),之后事务二在查询小红工资时,发现查到的工资和之前查到的不一样,这就是不可重复读。
幻读
一边做查询,一边做插入。
如下图:
这里的编号为主键,事务一查询编号的同时,事务二插入编号,这时事务一查询出来的编号为10和20,没查出编号30,事务一也想插入按顺序插入编号,也就是插入30,但是由于事务二已经插入了编号30,因此事务一插入就报错了(主键冲突,因为主键是唯一的),这就是幻读。
因此要避免脏读、不可重复读、幻读:将隔离级别提高到`序列化读`,所谓的`序列化读`就是把多版本并发退化到锁的并发控制(也就是select语句上会被偷偷加上共享锁)。
锁
InnoDB 行级锁, 只要两个客户端更新的是不同的行,互不干扰,默认添加的是意向锁
- X锁:增删改(insert update delete) 都会在行上加排他锁(X锁),上了这个锁别人都不能对其进行操作,只能等它释放锁才行,并且都能再加上别的类型的锁了
select * from 表名 where ... for update
- S锁:查询可以加共享锁 (S锁):表示可以同时查询,但其他人不能增删改(insert update delete)
select * from 表名 where ... lock in share mode; 添加共享锁-别人可以再加共享锁,但不能再加排他锁
- IS锁:事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁(意向共享锁)
- IX锁:事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁(意向排它锁)
MyISAM 表锁,是锁住整个表,MySQL 8.0中已经去除MyISAM引擎的表,也表明MySQL已不建议使用该引擎。
InnoDB 行级锁的兼容列表
S(共享锁) | X(排它锁) | IS(意向共享锁) | IX(意向排它锁) | |
S | 兼容 | 不兼容 | 兼容 | 不兼容 |
X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
IS | 兼容 | 不兼容 | 兼容 | 兼容 |
IX | 不兼容 | 不兼容 | 兼容 | 兼容 |
上述列表表示,如果一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放,并且意向锁是InnoDB自动加的,不需用户干预。