Mysql性能优化 - innodb的事务和锁
事务
什么是事务
事务数据库中的最小操作单元,是一组不可再分割的操作集合
mysql中开启事务
begin/start transaction -》 commit/ rollback 手动启动事务提交或者回滚
set session autocommit = on/off 设定事务是否自动开启
事务ACID特性
原子性(Atomicity)
最小工作单元,要么都执行,要么都不执行
一致性(Consistency)
事务中操作的数据及状态改变是一致的,即写入的资料的结果必须完全符合预设的规则,不会因为出现的系统意外等原因导致状态不一致
隔离性(Isolation)
一个事务所操作的数据在提交之前,对其他事务的可见性设置(一般设置为不可见)
持久性(Durability)
事务所做的修改就会永久保存,不会因为系统意外导致数据的丢失
事务并发会产生的问题
脏读
一个事务读取到了另一个事务尚未进行事务提交的数据
不可重复读
一个事务的操作导致另一个事务前后两次读取到不同的数据
幻读
一个事务的操作导致另一个事务前后两次查询的结果数据量不同
事务的四种隔离级别
SQL92 ANSI/ISO标准
Read Uncommited(未提交读)
事务未提交对其他事务也是可见的,导致脏读
Read Committed(提交读)
一个事务开始之后,只能看到自己提交事务所做的修改,解决了脏读问题,但是有不可重复读问题
Repeatable Read(可重复读)
在同一个事务中多次读取同样的数据结果是一样的,解决了不可重复读问题,为解决幻读
Serializable(串行化)
最高隔离级别,强制事务串行执行
innodb对隔离级别的支持程度
锁
表锁和行锁
锁是用于管理不同事务对共享字段的并发访问
表锁和行锁的区别
- 锁定粒度: 表锁 > 行锁
- 加锁效率: 表锁 > 行锁
- 冲突概率: 表锁 > 行锁
- 并发性能: 表锁 < 行锁
Innodb 支持表锁和行锁(表锁为锁所有行)
innodb 锁类型
- 共享锁(行锁)
- 排他锁(行锁)
- 意向锁共享锁(表锁)
- 意向锁排他锁(表锁)
- 自增锁
锁算法
- 记录锁
- 间隙锁
- 临键锁
共享锁
共享锁又称为读锁,简称s锁,共享锁多个事务可以对于同一数据共享一把锁,都能访问到数据,但是只能读不能改
加锁: select * from users where id=1 LOCK IN SHARE MODE
解锁:commit/rollback
排他锁
排他锁又称为写锁,简称X锁,排他锁不能与其他锁并存,一个事务获得了一个数据行的排他锁,其他事务就不能再获取该行的锁(共享锁,排他锁),只有获取了排他锁的事务是可以对数据进行读取和修改的(其他事务读取的数据来自于快照)
加锁:delete/update/insert默认加上x锁 select * from 。。。。。FOR UPDATE
解锁:commit/rollback
innodb锁的实现
Innodb的行锁是通过给索引上的索引项加锁来实现的。
只有通过索引条件进行数据检索,Innodb才能使用行级锁,否则,将使用表锁
表锁:lock tables xx read/write
意向共享锁(IS)
表示事务准备给数据行加入共享锁,即一个数据行加共享锁前必须先取得该表的IS锁,意向共享锁之间是可以相互兼容的
意向排他锁(IX)
表示事务准备给数据行加入排他锁,即一个数据行加排他锁之前必须先取得该表的IX锁,意向排他锁之间是可以相互兼容的
意向锁是innodb数据库操作之前自动加的,不需要用户干预,当事务想去进行锁表时,可以先判断意向锁是否存在,存在时则可以快速返回该表不能启用表锁
自增锁
针对自增列自增长的一个特殊表锁,默认值取1代表连续,事务未提交ID永久丢失
死锁
- 多个事务并发
- 每个事务都持有锁
- 每个事务都需要再继续持有锁
- 事务之间产生加锁的循环等待,形成死锁
避免死锁
- 类似的业务逻辑以固定的顺序访问表和行
- 大事务拆成小事务
- 同一个事务中,尽可能一次锁定所有需要的资源,减少死锁的概率
- 在业务允许的情况下降低隔离级别
- 添加合理索引,防止直接锁住全表