mysql 锁机制

表锁：InnoDB 支持行锁，不代表着就不支持表锁

• 表锁的 S 和 X 锁，MyISAM 的锁（不建议在 InnoDB 中使用）

  -- 查看当前有表锁的表
  show open tables where in_use > 0;
  
  -- 给表加 S 锁（所有事务都能读，所有事务都不能写）
  lock tables table_name read;
  
  -- 给表加 X 锁（拿到锁的事务可写可读，别的事务不能读也不能写）
  lock tables table_name write;
  
  -- 释放锁
  unlock tables;
  

• 意向锁

  • 如果 T1 给一行数据加了 X 锁，T2 想给表加锁？只能等 T1 结束 T2 才能成功。具体做法是：遍历每行数据看是否有 X 锁，如果都没有就给表加锁，如果有就阻塞
  • 如果表数据量太大，这样性能就不高了，于是有了意向锁
  • 如果有行锁，自动给表添加对应的锁
  • 意义： 做表锁的时候不需要挨行去扫描行锁

• 自增锁：维护自增主键

• 元数据锁

  • 也叫 MDL 锁，是个排他锁，alter 表结构的时候如果不持有 mdl 锁就会阻塞
  • 意义：保证查询修改时和表结构是一致的
  • 查询数据时，也会给表上意向锁，这时更新表结构就会阻塞，因为 mdl 锁是排他锁，意向锁没释放是不能获取到元数据锁的

行锁（ record lock ）

• 记录锁，存储引擎层实现，InnoDB才有
• 并发能力强，但是维护锁的消耗变大，会出现死锁
• 也有 S 和 X 锁，如果根据非索引列更新，会升级为表锁
• 增删改会自动加 X 锁，查询自动加 S 锁（查询也能手动指定为 X 锁）
• 一行记录有了 S 锁，别的事务还是能获取 S 锁，但是不能获取 X 锁
• 一行记录有了 X 锁，别的数据不能获取 S 和 X 锁
• commit; 提交事务之后会释放锁

间隙锁（ gap lock）

• 是一种特殊的行数，自然也是 X 锁（排他锁）

• 删除和更新是普通的行数，如果是插入，这条数据原来不存在，给那行记录加锁呢？给间隙加锁！

• 目的是避免幻影记录即解决幻读

• 给不存在的记录加锁，这个锁就是间隙锁，该条记录的前后作为区间，开区间，锁区间不锁边界

  -- 数据库存在数据：id = 1， id = 3， id = 8;
  -- 没有的 id=4 的记录，这就是间隙锁，具体区间是值的最近的两个值之间，这里就是(3,8)
  BEGIN;
  SELECT * FROM USER WHERE ID = 4 FOR UPDATE;
  
  -- 另一个事务，插入这个区间的值会阻塞(3和8之间的都不能插入)
  BEGIN;
  INSERT INTO USER (ID, NAME) VALUES (5, 'Marry');
  
  -- 这个间隙锁的区间就是 (8, 正无穷)
  BEGIN;
  SELECT * FROM USER WHERE ID = 10 FOR UPDATE;
  

临键锁（ next-key lock ）

• 间隙锁的特殊形式，包括边界

• 间隙锁是锁区间不锁边界，临键锁就是既锁区间也锁边界

• 也可以理解为普通的行锁+间隙锁

  -- 数据库存在数据：id = 1， id = 3， id = 8;
  -- 给 (3,8]这个区间加 X 锁，这就是临键锁
  BEGIN;
  SELECT * FROM USER WHERE ID > 3 AND ID <=8 FOR UPDATE;
  
  -- 这时也是不允许插入的
  BEGIN;
  INSERT INTO USER (ID, NAME) VALUES (5, 'Marry');
  

乐观锁悲观锁

• 悲观锁数据库层面做的，比如扣减库存，查询和更新都加 X 锁来保证并发安全
  • where 条件要有索引，能直接定位到某条记录进行上锁
  • 如果 where 条件没有索引，就是全表扫描，知道找到目标记录，结果会把扫描的记录全部加锁！
  • 适用于写比较多的场景
• 乐观锁是程序层面做的，两种思路
  • 版本号机制：给每条数据添加个版本，version，每次修改 version+1
    • 每当有修改数据的业务，先查询该条数据的 version，比如当前是 2
    • 做完业务后，更新数据时带上条件 where version = 2
    • 如果更新不成功，说明 version=2 的找不到数据（别的业务更新过该条数据，version 变了）
    • CAS 自旋，再次查询 version，然后更新当前 version 的数据
  • 时间戳机制：和 version 思想一致，值是时间错而已
  • 适用于读比较多的场景

死锁

• 同一张表（行锁）下的死锁

  session1	session2
  begin;	begin;
  id = 1 加 X 锁 select * from user where id=1 for update;	id = 2 加 X 锁 select * from user where id=2 for update;
  阻塞，等待 session2 释放 select * from user where id=2 for update;
  	阻塞，等待 session1 释放 select * from user where id=1 for update;
  commit;	commit;

• 不同表下的死锁

  session1	session2
  begin;	begin;
  select * from tableA where id=1 for update;	select * from tableB where id=1 for update;
  select * from tableB where id=1 for update;
  	select * from tableA where id=1 for update;
  commit;	commit;

• 间隙锁下的死锁（当前数据库存在id = 1， id = 3， id = 9 三条记录）

  session1	session2
  begin;	begin;
  (3,9) 间隙锁 select * from user where id = 5 for update;	再加一个 (3,9) 间隙锁 select * from user where id = 6 for update;
  插入数据，id = 7 insert into user (id, name) values (7, 'Bob');
  	插入数据，id = 8 insert into user (id, name) values (8, 'Milk');
  commit;	commit;

• 死锁解决方案

  • 等待，直到超时。时间由参数 innodb_lock_wait_timeout 决定，默认 50s（show VARIABLES like 'innodb_lock_wait_timeout';）
  • 回滚 undo 最小的事务。是否死锁根据每个事务再等待那个事务结束，画一个图，如果能形成一个环，那么就是死锁。这时也能知道哪个事务做的操作最小，回滚它来释放锁

• 避免死锁思路

  • 优化 sql，比如给 name = 'Marry' 的记录加锁，name 涉及成索引列（扫描的行记录都会加锁，如果能根据索引只定位一条记录，那么扫描的行就是一条）
  • 避免大事务，或者拆为小事务
  • 乐观锁
  • 较低隔离级别，提升锁粒度