1、锁模型
InnoDB有两种锁模型:共享锁、排它锁。
共享锁(S):阻止其他链接行写,允许读取行,读取之前获得锁
排他锁(X):阻止其他链接读取行,根据事物隔离级别决定是否可写入行,写入前获得锁
事务在获取锁之前,需要获取意向锁(intention lock-I),意向锁是事务获取锁之前需要等待,目的是防止不同连接获得的锁在相同行上。
锁之间的阻塞关系
锁 被阻塞
X X, S, IX, IS
S X, IS
IX X, S
IS X
2、锁类型
表级锁
锁住整张表,通过LOCK TABLES来获得表级锁,在实践中不提倡使用,在InnoDB中一般不使用。使用innodb_table_locks服务参数打开该功能,LOCK TABLES的目的是保证非事物表的一致性,比如Aria 和MyISAM。表级锁不仅仅是锁住事务中的行,而是锁住整个表,因此性能极差。
行级锁
Record Lock: 作用于单个索引记录。如果表没有明确创建索引,锁将作用于聚集索引(clustered index)中的记录。
Next-Key Lock:作用于一个索引记录及它之前的所有记录,这样可以防止其他连接插入或修改当前事务已访问的记录,只能用于重复读(REPEATABLE READ)
Gap Lock:锁定一个结果集,集合可以是一条记录、多条记录及空集合。该锁不能用于使用UNIQUE索引查询的单条记录上,除非是多列索引。一个特殊示例是插入间隙锁,该锁在插入新行前获得,当其他连接试图使用相同索引值插入行时,它将挂起等待当前事务提交或者回滚。该锁不能用于读提交(READ COMMITTED)锁诊断
使用 SHOW ENGINE INNODB STATUS快速查看锁信息
使用SHOW PROCESSLIST查看数据库进程信息
锁信息存放位置information_schema.INNODB_LOCKS表中
表INNODB_LOCK_WAITS中包含等待获取锁的事务,阻塞事务信息
3、读一致
不可重复读(The non-repeatable reads)
在当前事务没有修改数据的情况下,执行两次查询得到不同的结果,引发原因是当前事务没有同其他修改请求的连接相隔离,侧重数据的修改。
幻读(Phantom reads)
执行两次范围查询语句,得到的数据行记录不同,主要是数据的新增、删除影响。
一致性读(Consistent reads)
不使用锁,当前线程第一次读表是创建一个快照,使用START TRANSACTION WITH CONSISTENT SNAPSHOT开启,当提交事务时,快照中的更改更新到表中,从而对所有连接可见。在重复读隔离级别中使用。
锁定读(Locking reads)
比一致性读更强,需要锁表。在select语句中使用 LOCK IN SHARE MODE 或 FOR UPDATE。
LOCK IN SHARE MODE阻止其他连接修改数据,但可以读。如果事务隔离级别为SERIALIZABLE且自动提交为FALSE,LOCK IN SHARE MODE总会被添加到select语句中。
FOR UPDATE 排他锁(X)。
4、事务隔离级别
读未提交(READ UNCOMMITTED)
在事务执行时为每个读命令创建一个单独的快照,读取使用数据快照,此事务中的数据可能包含未提交的数据,因此表中可能并不包含此数据
读提交(READ COMMITTED)
在事务中为每个一致性读设置一个不同的快照,不会使用为提交的数据创建快照。更新、删除语句及锁定读不会使用间隙锁(gap lock)
重复读取(REPEATABLE READ)
事务内的所有读使用相同快照,有比读提交更强的一致性约束,更新、删除语句及锁定读及record lock 使用唯一索引,gap lock及next-key lock 则会阻塞
串行化(SERIALIZABLE)
可以认为重复读取模式下所有非锁定select自动转换为LOCK IN SHARE MODE,这两者是等价的:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT ....LOCK IN SHARE MODE;
UPDATE ...;
COMMIT;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT ...;
UPDATE ...;
COMMIT;