读锁与写锁

读锁：共享锁，Shared Locks，S 锁
写锁：排他锁，Exclusive Locks，X 锁

— X 锁 S 锁
X 锁 冲突 冲突
S 锁 冲突 不冲突

隐式锁：一个事务插入一条记录后，还未提交，这条记录会保存本次的事务ID，而其他事务如果想来对这条记录加锁的时候。会发现事务ID不对应，这是会产生 X 锁，所以相当于再插入一条记录时，隐式的给这条记录加了一把隐式 X 锁

读操作

对于普通 SELECT 语句，InnoDB 不会加任何锁

SELECT … lock in share mode

  • 将查找到的数据加上一个 S 锁，允许其他事务继续获取这条记录的 S 锁，不能获取这条记录的 X 锁
  • 使用场景：读出数据后，其他事务不能进行改变（会发生阻塞）。但是自己也不一定可以修改，因为其他事务依然可以使用 select ... lock in share mode 继续对这行数据进行加读锁

SELECT … for update

  • 将查找到的数据加上一个 X 锁，不允许其他事务获取这些记录的 S 锁和 X 锁
  • 使用场景：读出数据后，其他事务既不能写，也不能加读锁，那么只有本事务可以修改数据

，

写操作

  • DELETE：删除一条记录时，先对记录加 X 锁，再执行删除操作
  • INSERT：插入一条记录是，会先加“隐式锁”来保护这条新插入的记录在本事务提交前不被别的事务访问
  • UPDATE：
    • 如果被更新的列，修改前后没有导致存储空间发生变化，那么就会给记录先加 X 锁，再直接对记录进行修改
    • 如果被更新的列，修改前后导致存储空间发生了变化，那么就会给记录先加 X 锁，然后将记录删除，再 insert 一条新记录

行锁与表锁

查看锁情况的SQL

  1. -- 记录当前运行的事务
  2. select * from information_schema.INNODB_TRX; -- 5.X
  3. -- 记录当前出现的锁
  4. select * from information_schema.INNODB_LOCKS; -- 5.X
  5. select * from performance_schema.DATA_LOCKS; -- 8.X
  6. -- 记录锁等待的对应关系
  7. select * from information_schema.INNODB_LOCK_WAITS; -- 5.X
  8. select * from performance_schema.DATA_LOCK_WAITS; -- 8.X

INNODB_TRX 字段

  • trx_id：事务 ID
  • trx_status：事务状态，RUNNING，LOCK_WAIT，ROLLING BACK，COMMITTING
  • trx_started：事务开始时间
  • trx_requested_lock_id：事务等待的锁的 ID

行锁

LOCK_REC_NOT_GAP：单个行记录上的锁
LOCK_GAP：间隙锁，锁定一个范围，但不包括记录本身，GAP 锁的目的是为了防止同一事物的两次当前读，出现幻读的情况
LOCK_ORDINARY：锁定一个范围，并锁定记录本身。对于行的查询，都采用该方法。主要目的是为了解决幻读的问题

间隙锁（LOCK_GAP、GAP 锁）

READ COMMITTED 级别下

  • 对主键索引加锁 - 会再主键处加一把锁

    1. -- session 1
    2. select * from t1 where a = 1 for update;
    3. a | b | c
    4. 1 | 1 | 1
    5. -- session 2
    6. select * from t1 where a = 1 for update; -- 阻塞
    7. select * from t1 where a = 2 for update; -- 不会阻塞

  • 对唯一键索引加锁 - 会再唯一索引处加一把锁，主键加一把锁，防止其他查询条件也找到了一样的记录

    1. -- session 1
    2. select * from t1 where b = 1 for update;
    3. a | b | c
    4. 1 | 1 | 1
    5. 2 | 2 | 1
    6. -- session 2
    7. select * from t1 where b = 1 for update; -- 阻塞
    8. select * from t1 where b = 2 for update; -- 不会阻塞

  • 对普通索引加锁 ```sql — session 1 select * from t1 where c = 1 for update; a | b | c 1 | 1 | 1 2 | 2 | 1

3 | 3 | 3 — session 2 select from t1 where c = 1 for update; — 阻塞 select from t1 where b = 2 for update; — 阻塞 select * from t1 where a = 3 for update; — 不会阻塞 insert into t1(b,c) values(4, 2) for update; — 不会阻塞，但是会阻塞 session 1 的读

  2. - 对没有索引的数据加锁，只会对查出来的数据加锁，其他不会
  4. 首先全表扫描，对每一条数据都加锁，然后根据 where 条件将不符合条件的数据上的锁释放
  5. ```sql
  6. -- session 1
  7. select * from t1 where d = 1 for update;
  8. a | b | c | d
  9. 1 | 1 | 1 | 1
  10. 2 | 2 | 1 | 1
  11. -------------
  12. 3 | 3 | 2 | 2
  13. -- session 2
  14. select * from t1 where a = 1 for update; -- 阻塞
  15. select * from t1 where a = 2 for update; -- 阻塞
  16. select * from t1 where a = 3 for update; -- 不会阻塞

REPEATABLE READ 级别下（存在 GAP 锁，解决了幻读）

  • 对主键索引加锁 - 会再主键处加一把锁：和 READ COMMITTED 一样
  • 对唯一键索引加锁：和 READ COMMITTED 一样
  • 对普通索引加锁 ```sql — session 1 select * from t1 where c = 1 for update; a | b | c 1 | 1 | 1 2 | 2 | 1

3 | 3 | 3 — session 2 select from t1 where c = 1 for update; — 阻塞 select from t1 where b = 2 for update; — 阻塞 select * from t1 where a = 3 for update; — 不会阻塞 insert into t1(b,c) values(4, 2) for update; — 会阻塞，因为存在间隙锁

  2. - 对没有索引的数据加锁
  4. 首先全表扫描，对每一条数据都加锁，因为防止你修改数据，出现幻读
  5. ```sql
  6. -- session 1
  7. select * from t1 where d = 1 for update;
  8. a | b | c | d
  9. 1 | 1 | 1 | 1
  10. 2 | 2 | 1 | 1
  11. -------------
  12. 3 | 3 | 2 | 2
  13. -- session 2
  14. select * from t1 where a = 1 for update; -- 阻塞
  15. select * from t1 where a = 2 for update; -- 阻塞
  16. select * from t1 where a = 3 for update; -- 阻塞，因为存在间隙锁

表锁

表级别的 S 锁和 X 锁

在对某个表执行 SELECT、INSERT、DELETE、UPDATE 操作时，InnoDB 存储引擎时不会为这张表添加表级别的 S 锁或者 X 锁。
在对某个表执行 DROP TABLE、ALERT TABLE 这些 DDL 语句时，其他事务对这个表执行 SELECT、INSERT、DELETE、UPDATE 操作时会发生阻塞，或者其他事务执行 DML 语句时，也会阻塞 DDL 语句，这个过程时通过元数据锁（Metadata Locks，简称 MDL）来实现的，并没有使用 S 锁和 X 锁

  • LOCK TABLES t1 READ; 对 t1 表加表级别的 S 锁
  • LOCK TABLES t1 WRITE; 对 t1 表加表级别的 X 锁

尽量不要使用这种方式去加锁。因为 InnoDB 的优点是行锁，所以尽量使用行锁，性能更高

IS 锁、IX 锁

  • IS 锁：意向共享锁，Intention Shared Lock，当事务准备在某条记录上加 S 锁，需要先在表上加要给 IS 锁
  • IX 锁：意向排他锁，Intention Exclusive Lock，当事务准备在某条记录上加 X 锁，需要先在表上加要给 IX 锁

IS 锁和 IX 锁是表级锁，他们的提出仅仅为了在之后加表级别的 S 锁和 X 锁时可以快速判断表中是否有记录被上了锁，已避免使用遍历的方式来查看表中有没有被上锁的记录

AUTO-INC 锁

  • 在执行插入语句时，在表级别加一个 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，该语句执行结束后，再把 AUTO-INC 锁释放掉。这样一个事务持有 AUTO-INC 锁的过程中，其他事务的插入语句都会被阻塞，可以保证一个语句中分配的递增值是连续的
  • 采用一个轻量锁，在作为插入语句生成 AUTO_INCREMENT 修饰的列的值时获取一下这个轻量锁，然后生成本次插入语句所需要用到的 AUTO_INCREMENT 值之后，就把这个锁释放掉了，并不需要等到整个插入语句执行完才释放

系统变量：innodb_autoinc_lock_mode

show global variables like '%innodb_autoinc_lock_mode%'
innodb_autoinc_lock_mode 值为 0：采用 AUTO-INC 锁
innodb_autoinc_lock_mode 值为 2：采用轻量锁
innodb_autoinc_lock_mode 值为 1：当插入记录数不确定时采用 AUTO-INC 锁，反之采用轻量锁

悲观锁

上述都是悲观锁

乐观锁

操作时才去判断，通常用 version 来控制

死锁

  1. -- session 1
  2. select * from t1 where a = 1 for update;  -- 步骤 1
  3. update t1 set b = 1 where a = 2; -- 步骤 3，等待 session 2 提交
  5. -- session 2
  6. delete from t1 where a = 2; -- 步骤 2
  7. delete from t1 where a = 1; -- 步骤 4，等待 session 1 提交

死锁检测

MySQL 会检测死锁，上述情况通常不会发生，结果是其中一个 session 会回滚

  • 系统变量 innodb_deadlock_detect：控制是否打开死锁检测，默认打开
  • 系统变量 innodb_deadlock_timeout：等待死锁超时时间。默认 50s
  • 系统变量 innodb_deadlock_deadlocks：将所有死锁日志写入到 mysql 的错误日志中，默认关闭

检测到死锁时，InnoDB 会在导致死锁的事务中选择一个权重较小的事务进行回滚，这个权重值可能由该事务影响的行数（新增，修改，删除）决定
show engine innodb status 可以查看最近的死锁日志

避免死锁

  • 按固定顺序执行事务
  • 大事务拆小事务
  • 在同一个事务尽可能一次性锁定所有资源
  • 降低隔离级别
  • 为表添加合理的索引（全表扫描会全部加锁）就会加大死锁的概率