锁类型 | 说明 | 锁级别 |
|---|---|---|
意向共享锁 IS Lock | 事务想要获得一张表中某几行的数据的共享锁 | 表级别锁 |
意向排他锁IX Lock | 事务想要获得一张表中某几行数据的排他锁 | 表级别锁 |
共享锁S Lock | 允许事务读取一行数据 | 行级别锁 |
排他锁X Lock | 允许事务更新或删除一条数据 | 行级别锁 |
加锁方式
记录r进行上X锁，先对数据库A、表、页上加意向锁IX，才能对记录r上X锁。
兼容性
IS | IX | S | X | |
|---|---|---|---|---|
IS | 兼容 | 兼容 | 兼容 | 不兼容 |
IX | 兼容 | 兼容 | 不兼容 | 不兼容 |
S | 兼容 | 不兼容 | 兼容 | 不兼容 |
X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
锁的监控表
查询锁的情况：information_schema下
- innodb_trx事务表
- innodb_locks锁表
- innodb_lock_wait锁等待表
锁算法
3种锁算法
Record Lock
单行记录加锁
Gap Lock
Gap Lock间隙锁，锁一个范围
阻止多个事务将记录插入到同一范围内
Next Key Lock
Next Key Lock：Record Lock Gap Lock，锁一个范围 锁一个记录
查询的列是唯一索引的情况时，降级为Record Lock。
举例说明
建表插入数据
代码语言：javascript复制CREATE TABLE z(
a INT,
b INT,
PRIMARY KEY(a),
index index_b(b)
);
INSERT INTO z SELECT 1,1;
INSERT INTO z SELECT 3,1;
INSERT INTO z SELECT 5,3;
INSERT INTO z SELECT 7,6;
INSERT INTO z SELECT 10,8;会话A
代码语言：javascript复制begin;SELECT*FROM z WHERE b=3 FOR UPDATE;
会话B
代码语言：javascript复制begin;SELECT*FROM z WHERE a=5 LOCK IN SHARE MODE;
不能执行Record Lock 锁定了5
2. INSERT INTO z SELECT 4,2; 
不能执行有Gap Lock
非一致性锁定读/一致性锁定读
非一致性锁定读
1. 不需要等待访问的行上X锁的释放，直接读快照，提高了数据库的并发性。
2. 快照数据来自undo段。undo段会用在事务回滚，所以快照数据本身没有额外开销。
3. 读快照信息不需要上锁，没有事务需要处理历史数据。
READ COMMITTED和 REPEATABLE READ快照定义的区别
隔离级别 | 读取的快照数据 | 是否默认 隔离级别 | 存在的问题 |
|---|---|---|---|
READ COMMITTED | 读锁定行最新的一份提交过数据 | 否 | 会出现幻读 |
REPEATABLE READ | 读锁定行事务开始前的版本 | 是 | 无 |
一致性锁定读
锁定读的语句 | 加锁类型 | 注意事项 |
|---|---|---|
select ... for update | 加X锁 | 务必加上BEGIN， START TRANSACTION或者 SET AUTOCOMMIT=0 |
select ... lock in share mode | 加S锁 |
锁常见问题
脏读，违反隔离性Isolation
不同的事务下，当前事务可以读到另外事务未提交的数据。
read uncommitted隔离级别下会发生
不可重复(Phantom Problem幻读)
一个事务内两次读到的数据是不一样的情况（当前事务没有结束。另外一个事务修改了）。
READ COMMITTED下会发生，会读到已经提交的数据 。
默认的事务隔离级别是
REPEATABLE READ。采用Next-Key Locking的算法，解决。
锁一个范围 锁一个记录。
丢失更新
任何隔离级别下都不会发生，但是应用层面会发生.
代码语言：javascript复制//假设id=3的账号余额为100，A事务转账99，
Update t set a=1 where id=3
//B事务转账1
Update t set a=99 where id=3
//B事务后提交，最后余额是99，A事务的丢失了解决方式：
串行化处理，乐观锁等
阻塞
一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源
innodb_lock_wait_timeout来控制等待时间默认50s
innodb_rollback_on_timeout设置超时时是否回滚，默认off，不回滚。
死锁
两个事务，争夺锁，相互等待。
死锁发生的概率一般很低
- 系统中事务的数量（n），数量越多发生死锁的概率越大
- 每个事务操作的数量（r），每个事务操作的数量越多，发生死锁的概率越大
- 操作数据的集合（R），越小则发生死锁的概率越大
解决方式：
设置超时，等待超时的回滚，没有超时的继续，但是并发下降
innodb_lock_wait_timeout来控制等待时间默认50s
通常来说InnoDB存储引擎选择回滚undo量最小的事务
补充2-自增长与锁
自增长的列，必须是索引，且必须是索引的第一个列。
AUTO-INC Locking：
当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化。
执行： SELECT MAX(auto_inc_col)FROM t FOR UPDATE;
插入操作会依据这个自增长的计数器值加1赋予自增长列
该锁在执行完插入自增长值的SQL后释放
提高了部分性能，但是对应insert select会受影响，需要等待另外一个事务的完成AUTO-INC Locking。
Mysql5.1.22后，提供了轻量级互斥量，在内存中计算自增值提高性能
补充3-外键与锁
innodb外键自动加索引
插入或更新数据时，
先使用SELECT…LOCK IN SHARE MODE方式，即主动对父表加一个S锁。
