- 快照读:普通的 select 语句,基于 MVCC 和 undo log 实现,不加锁
- 当前读:update、delete、insert,select…lock in share mode (共享读锁) 等操作,基于锁实现
当我们执行 update、delete 操作时,首先需要找到对应的记录再操作、insert 操作类似,需找到最后一条数据,确保两条并行的 insert 不会混合插入到同一行。此时的读操作就采用当前读实现,当前读每次读取最新的数据,基于锁实现。
根据机制不同,锁可以分为以下两种:
- 共享锁:多个线程可以同时占有锁
- 独占锁:每个锁同时最多只能被一个线程占有
MySQL 读操作采用共享锁,写操作采用独占锁,也就是说多个线程可以同时读某个数据、但不允许修改。写操作时单个线程占用锁资源,其余线程不能访问。
根据粒度,锁又可以分为以下几种:
- 行锁:锁粒度最小,锁定某张表的某一行数据,会出现死锁
- 页面锁:锁粒度居中,锁定某个表的某一页,会出现死锁
- 表锁:锁粒度最大,直接锁定某个表,MyISAM 表锁不会出现死锁
死锁:不同线程分别占有对方需要获取的锁资源,都不释放锁资源就会产生死锁,多个线程互相请求锁形成环也会导致死锁
当用到表锁时,MyISAM 通过一次性获取所有需要的锁来避免死锁。InnoDB 存储引擎能检测到死锁的循环依赖并立即返回错误。当死锁涉及外部锁或表锁时,InnoDB 可能无法检测到死锁,此时可以通过设置锁最大超时等待参数(innodb_lock_wait_timeout)来解决。
总得来说,锁冲突越少,产生死锁的概率也就越小。InnoDB 可以通过以下方法减少死锁:
- 在事务开启时首先通过 select … for update 直接获取必要的锁
- 如果存在更新操作,直接获得独占锁,而不是先申请共享锁,防止第二次申请独占锁时其它事务占有共享锁
- 如果事务需要修改多个表,则每个事务中以相同的顺序访问表使用加锁语句
- 改变事务隔离级别
出现死锁可以通过 SHOW INNODB STATUS 命令确定最后一个死锁产生的原因,据此分析死锁产生的原因和改进措施。
死锁会严重影响数据库性能,在高并发系统上,当许多线程阻塞等待同一个锁时,死锁检测可能导致数据库处理速度变慢。 有时禁用死锁检测可能性能更高,此时可以设置 innodb_lock_wait_timeout 进行事务回滚。要想打破死锁,必须强制回滚部分事务打破环,InnoDB 默认回滚持有最少行级锁的事务。
其中锁粒度越大,锁操作的消耗越小,并发性越低。锁粒度越小,锁操作消耗越大,并发性越高。不同的存储引擎支持不同的锁粒度,实现原理也大不相同,常见的存储引擎锁实现有:
- InnoDB:支持行级锁和表级锁,默认采用行级锁
- MyISAM:支持表级锁
InnoDB 实现了两种类型的行锁:
- 行共享锁:允许事务去读一行,阻止其它事务获取当前行的独占锁
- 行独占锁:允许事务去修改一行,阻止其它事务获取当前行的共享锁及独占锁
为了确保行锁及表锁共存,InnoDB 还提供了两种意向锁,两种意向锁都是表锁:
- 意向共享锁:事务打算获取某行的共享锁前,必须先获取该表的意向共享锁
- 意向独占锁:事务打算获取某行的独占锁前,必须先获取该表的意向独占锁
锁之间的兼容关系如下:
- 共享锁兼容共享锁和意向共享锁,不兼容独占锁和意向独占锁
- 独占锁和任何锁都不兼容
- 意向共享锁兼容共享锁、意向共享锁和意向独占锁
- 意向独占锁兼容意向独占锁和意向共享锁
事务请求的锁如果和当前锁兼容,InnoDB 就分配锁资源给当前事务,否则只能等待锁资源释放。实际场景有以下几种:
- 事务1 执行写操作,占有表的意向独占锁和行的独占锁。此时事务2 执行读操作,占有表的意向共享锁,意向共享锁和意向独占锁兼容,此时根据读的是否同一行判断,如果是同一行则阻塞,否则可以正常读取
- 事务 1 执行读操作,占有表的意向共享锁和行的共享锁。此时事务2 执行写操作,栈有表的意向独占锁,意向共享锁和意向独占锁兼容,此时根据写的是否同一行判断,如果是同一行则阻塞,否则可以正常写
- … ,其它类似
意向锁由 InnoDB 控制,自动加锁,无须用户干预。insert、update、delete 自动加独占锁,普通的 select 语句不会加锁,按快照读实现,常见的 select 显式加锁有两种方案:
- 共享锁:select * from table_name where … lock in share mode
- 独占锁:select * from table_name where … for update
即使没有显式加锁,InnoDB 在事务执行过程中会根据设置的隔离级别自动加锁,这种加锁方式也叫隐式加锁。锁在执行 commit 或者 rollback 时释放,所有锁在同一时间释放。
InnoDB 行锁通过对索引上的索引项加锁实现,也就是说只有通过索引查询时可能使用行锁,否则仍然使用表锁。无论使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁。只有真正用到索引查询时才会使用行锁,这块由执行计划决定:当表数据很少时,InnoDB 认为全表查询效率更高不使用索引就不会使用行锁。由于基于索引实现,可能存在不同行基于多个相同的索引键,此时即使不同行,仍有可能冲突。
间隙锁:当我们使用范围查询作为检索条件,InnoDB 会给符合记录的索引项加锁,对于键值在条件范围内但并不存在的记录,我们称之为间隙,InnoDB 默认会对间隙加锁,这种锁机制就是所谓的间隙锁。
InnoDB 默认使用间隙锁可以解决幻读问题,满足隔离级别,间隙锁也方便恢复和复制的需要:MySQL 的主从复制(恢复)基于 binlog 实现,binlog 是 SQL 语句级别的,恢复时重新执行 SQL,其中 binlog 日志根据事务提交的前后顺序记录。要想恢复数据一致,其他并发事务不能插入当前事务满足其锁定条件的任何记录,也就是不允许出现幻读。
但间隙锁会阻塞符合条件范围内键值的并发插入,在实际场景中我们应优先使用相等条件判断,少使用范围查询,减少间隙锁,降低锁冲突,提高性能。
MyISAM 表锁包含两种类型:
- 表共享读锁:不会阻塞其它线程对当前表的读操作,但会阻塞写操作
- 表独占写锁:会阻塞其它用户对当前表的读写操作
默认写锁比读锁具有更高的优先级,也就是说锁释放时,锁会优先分配给写锁队列中阻塞的线程,这也就导致当写操作非常频繁时,读操作可能永远无法抢占到锁了,此时可以通过配置修改优先级解决
MyISAM 永远不会导致死锁,因为 MyISAM 在执行查询操作前自动加读锁,在执行更新操作前直接加写锁,无须用户干预。在自动加锁的场景下,MyISAM 总是一次性获取当前 sql 需要的所有锁,因此不会死锁
MyISAM 支持并发插入,以减少锁的竞争消耗:如果 MyISAM 表中没有空白块,则 insert 操作和 select 操作可以并发执行不需要加锁,每次插入到文件末尾。当存在空白块时则不行,空白块可能由删除或者移动造成,当所有空白块都填充有新数据时,恢复并发插入。
并发插入特性通过 concurrent_insert 参数配置:
- concurrent_insert 为 0 时:不允许并发插入
- concurrent_insert 为 1 时:不存在空白块时允许并发插入,否则不允许,默认配置
- concurrent_insert 为 2 时:无论是否有空白块,都支持并发插入
上面提到的都是存储引擎层面的锁,MySQL 服务层提供 lock tables 和 unlock tables 执行锁:
- lock tables:锁定当前线程需要访问的表
- unlock tables:释放当前线程获取到的所有锁
在 InnoDB 表中使用数据库锁时需要注意以下几点:
- 将 autocommit 设为0,否则不会加锁
- 必须在事务结束后执行 unlock tables,因为 unlock tables 会隐式提交事务
- commit 和 rollback 不能释放 lock tables 加的表锁,必须通过 unlock tables 显式解锁
根据是否乐观,锁又可以分为乐观锁和悲观锁:
- 乐观锁:假设不会发生冲突,只在提交操作时判断期间是否被修改,不会上锁,不能解决脏读。如果数据库提供 write_condition 机制都是通过乐观锁
- 悲观锁:认为一定发生冲突,必须加锁。行锁、表锁、读锁、写锁都是悲观锁。
关于锁优化的建议:
- 尽量使用低隔离级别
- 建立索引,尽量使用索引作为查询条件查询
- 对于修改操作,直接获取独占锁,不要先申请共享锁,修改时再申请独占锁,降低锁冲突和死锁的概率
- 对于多表操作,约定表的访问顺序,减少表之间死锁的概率
- 少使用 MySQL 服务层提供的锁,innoDB 下普通 select 有 MVCC 保证同步