mysql锁机制

环境

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `number_idx` (`number`),
  KEY `name_idx` (`name`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

索引情况

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY    |            1 | id          | A         |           7 | NULL     | NULL   |      | BTREE      |         |               |
| t1    |          0 | number_idx |            1 | number      | A         |           7 | NULL     | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | name_idx   |            1 | name        | A         |           7 |      191 | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

乐观锁

程序实现，版本号或者时间戳。

悲观锁

表级锁

1. 表锁
   加锁
   读: lock table t1 read;
   写: lock table t1 write;

   释放锁： unlock tables;
   –>读读不互斥，读写互斥。
   –>加锁以后，只能操作锁定的表。

2. 元数据锁
   CRUD自动加读锁
   DDL自动加写锁

3. 意向锁（为的就是全表更新的时候，不用逐行判断）
   IS 共享读锁
   IX 共享写锁

行级锁（如果没有使用到索引，会上升为表级锁）

1. 按功能分
   读锁(阻止其他连接获取写锁)

    select * from student where id = 1 lock in share mode;
   

   写锁(阻止其他链接获取锁，但是由于MVCC机制，所以其他连接可以读取数据)

    insert ,update,delete 自动加写锁
    select * from student where id = 1 for update;
   

2. 按锁定范围分
   记录锁（Record Lock）
   –>主键或者唯一键等值查询，会锁定该行。
   主键

    session1:
    begin;--开启事务
    select * from t1 where id = 3 for update;
    
    session2:
    begin;
    update t1 set number = 999 where id  = 3;（阻塞）
    update t1 set number = 999 where id  = 1;（成功执行）
    insert t1 values(4,'g',20,'x');(成功执行)
    session1：
    rollback;
    session2:
    rollback;
   

   唯一索引

    session1:
    begin;--开启事务
    select * from t1 where number = 3 for update;
    session2:
    begin;
    update t1 set name= 'x' where nunber= 3;（阻塞）
    update t1 set name= 999 where  = 1;（成功执行）
    insert t1 values(4,'g',8,'x');(成功执行)
    session1：
    rollback;
    session2:
    rollback;
   

   间隙锁
   –>范围查询或者非唯一索引检索
   主键产生间隙锁

   主键范围查询
   主键索引的模拟情况

 session1:
 begin;
 select * from t1 where id>2 and id<8  for update;
 --查询出 id为3的数据，前项为ID=2,后一项为ID=8;
 session2:
 insert into t1 values(4,'x1',88,'t1');(阻塞)
 insert into t1 values(9,'x1',88,'t1');(成功执行)
 session 1:
 rollback;
 session 2:
 rollback

非唯一索引
name列索引的模拟情况

   session1:
   begin;
   select * from t1 where name = 'd' for update;   
   session2:
   begin;
   insert into t1 values(10,'h',700,'');(阻塞)
   insert into t1 values(12,'c',700,'');（阻塞）
   insert into t1 values(12,'a',700,'');（成功执行）（说明并不会锁定主键的间隙）

无索引升级表锁

  session1:
  begin;
  select * from t1 where remark='e' for update;
  session2:
  begin:
  所有DML都阻塞
  session1:
  rollback;
  session2:
  rollback;
  

 Next-key锁（RR隔离级别下）
 记录锁+间隙所