学习视频:B站
MySQL数据库教程天花板,mysql安装到mysql高级,强!硬!_哔哩哔哩_bilibili0基础如何学起?宋红康30天搞定Java核心:BV1Kb411W75N基础篇:P1 - P95高级篇:P96 - P199MySQL课程天花板:6大范式讲解、7大日志剖析、7大SQL性能分析工具、9大存储引擎剖析、10大类30小类优化场景、15个不同锁的应用讲解、18种创建索引的规则、300+张高清无码技术剖析图......https://www.bilibili.com/video/BV1iq4y1u7vj?p=161
一、事务的ACID特性
1.原子性(atomicity):全部提交,或者全部回滚
2.一致性(consistency):根据定义,一致性是指事务执行前后,数据从一个 合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的,跟具体的业务有关。比如说银行存款,张三有100,李四有100,无论他们之间如何转账,总钱还是200,如过不是,则不符合一致性。
3.隔离性(isolation):事务之间互不干扰
4.持久性(durability):事务一旦提交,数据的改变是永久性的。
事务的原子性、一致性、持久性通过事务日志体现的,事务日志分为重做日志(REDO LOG)和回滚日志(UNDO LOG);事务的隔离性通过锁来实现。
二、事务的状态
三、事务的分类
事务分为显示事务和隐式事务。
1. 显示事务
- 开启事务
- 方式一:start transaction 后面可以跟read only / read write with consistent snapshet
- 方式二:begin,后面什么也不跟
- 保存点savepoint
2.隐式事务
- 自动提交:autocommit
- 关闭隐式事务
- 方式一:开启显示事务,则隐式事务自动关闭
- 方式二:set autocommit = off / false,针对DML(数据定义语言),不针对DDL
三、数据并发问题
1. 脏写:事务A修改了事务B修改的但未提交的数据
2. 脏读:事务A读写了事务B更新的但未提交的数据
3. 不可重复读:事务A读取数据后,事务B更新数据,A再读取,两次读取的数据不一样
4. 幻读:事务A读取数据后,事务B插入数据,A再读取,读取到的数据比之前多出几行数据
问题按照严重性来排一下序:脏写 > 脏读 > 不可重复读 > 幻读
四、SQL中的四种隔离级别
Oracle支持READ COMMITED(提交读)和SERIALIZABLE(串行化)
Mysql支持4中隔离,默认REPEATABLE READ(可重复读)
隔离级别 | 脏写可能性 | 脏读可能性 | 不可重复读可能性 | 幻读可能性 |
READ UNCOMMITED(未提交读) | No | Yes | Yes | Yes |
READ COMMITED(提交读) | No | No | Yes | Yes |
REPEATABLE READ(可重复读) | No | No | No | Yes |
SERIALIZABLE(串行化) | No | No | No | No |
五、MySQL并发事务访问相同记录
1. 读-读情况
2. 写-写情况
- 建立锁结构
- 几种说法
- 不加锁。不生成锁结构。
- 获取锁,或者加锁成功。生成锁结构,但iswaiting:false
- 获取锁失败,或者加锁失败。生成锁结构,但iswaiting:true
3. 读-写或者写-读情况
- 会出现的问题:脏读、不可重复读、幻读
- 解决办法:
- 方案一:读写都加锁
- 方案二:读操作用MVCC(多版本并发控制,具体可参考下篇文章),写操作加锁。
- 方案二比方案一性能更高
六、锁的分类
1.对数据的操作类型分为:
(1)读锁 / 共享锁 / S锁
- 对读取加S锁:
select 、、、 LOCK IN SHARE MODE; 或者 select 、、、 FOR SHARE;(8.0版本新增)
(2)写锁 / 排它锁 / X锁
- 对读取加X锁
select 、、、 FOR UPDATE
- 写操作
- delete / update:加X锁
- Insert:隐式锁(因为在加锁时,么有确切存在的数据)
2.锁粒度角度分为:
(1)表级锁(开销小,不会出现死锁,发生锁冲突概率大,并发度最低)
- 表级别的S锁、X锁
- 意向锁(intention Lock)
①意向锁还可以再细分为意向共享锁(IS)和意向排它锁(IX)
②意向锁视为调节表锁和行锁的问题
③当某行加了行锁,会自动在 表 / 页 上加一个意向锁
④
IS | IX | |
IS | 兼容 | 兼容 |
IX | 兼容 | 兼容 |
⑤
IS | IX | |
S锁 | 兼容 | 互斥 |
X锁 | 互斥 | 互斥 |
- 自增锁(AUTO-INC锁)
- 元数据锁(MDL锁)
- MDL读锁
- MDL写锁
(2)行级锁(会出现死锁,开销大,发生锁冲突概率小,并发度最高)
- 记录锁(Record Locks)
- X型记录锁
- Y型记录锁
- 间隙锁(Gap Locks)
- 是为了解决REPEATALBE READ隔离级别下的幻读问题
- 因为幻影记录尚不存在,所以不能加记录锁
- 锁定一个范围,不包括记录本身
- 临间锁(Next-Key Locks)
- 既锁住某行记录,同时阻止在其他事务在记录前边的间隙插入记录
- 官方名:LOCK_ORDINARY
- Innodb中事务级别在可重复读的情况下使用的数据库锁就是临间锁
- 锁定一个范围,包含记录本身
- 举例:
begin select * from student where id <= 8 and id > 3 for update
- 插入意向锁(Insert Intention Locks)
- 插入意向锁是插入一条记录行前,由Insert 操作产生的一种间隙锁
(3)页级锁
-
页级锁是MySQL锁定粒度介于行级锁和表级锁中间的一种锁。
-
表级锁速度快,但冲突多;行级锁冲突少,但速度慢。
-
所以页级锁是折中的,一次锁定相邻的一组记录。BDB支持页级锁。
3.对待锁的态度分为
(1)乐观锁
- 通过程序实现
- 适合读操作多的情况下
- 实现方式有
- 版本号机制
- 在表中设计一个 版本字段 version ,第一次读的时候,会获取 version 字段的取值。然后对数据进行更新或删除操作时,会执行 UPDATE ... SET version=version+1 WHERE version=version 。此时如果已经有事务对这条数据进行了更改,修改就不会成功。
- CAB机制(时间戳机制)
- 时间戳和版本号机制一样,也是在更新提交的时候,将当前数据的时间戳和更新之前取得的时间戳进行比较,如果两者一致则更新成功,否则就是版本冲突。
你能看到乐观锁就是程序员自己控制数据并发操作的权限,基本是通过给数据行增加一个戳(版本号或者时间戳),从而证明当前拿到的数据是否最新。
- 时间戳和版本号机制一样,也是在更新提交的时候,将当前数据的时间戳和更新之前取得的时间戳进行比较,如果两者一致则更新成功,否则就是版本冲突。
- 版本号机制
(2)悲观锁
- 通过锁的机制实现
- 适合多写的操作
- 一操作就加锁
4.加锁的方式分为:
(1)隐式锁
(2)显示锁
5.其他分为:
(1)全局锁
- 对整个数据库加锁
- 使用场景:全库逻辑备份
(2)死锁
- 资源互相占用,进入死循环
- 解决办法:
- 方法一:进入等待,知道循环超时结束
- 方法二:主动回滚死锁链中的某一个事务(持有最少行级排他锁的事务进行回滚)