Mysql锁详解

行级锁和表级锁

行级锁：开销大，锁的粒度小。
表级锁：开销小，锁的粒度大。

InnoDB同时支持行级锁和表级锁

共享锁(S)和排他锁(X)

共享锁：也称为读锁，允许多个事物读，但是不允许写
排他锁：也称为写锁，不允许读和写

锁类型	共享锁	排他锁
共享锁	兼容	互斥
排他锁	互斥	互斥

意向共享锁(IS)和意向排他锁(IX)

意向共享锁：事务在给数据行加行级共享锁之前，必须先取得该表的意向共享锁。
意向排他锁：事务在给数据行加行级排他锁之前，必须先取得该表的意向排他锁。

主要作用：当表要变更结构时，要获取表的X锁，需要判断每行是否有排他锁，没有才能获取，效率比较低。于是引入了意向共享锁和意向排他锁。
表级排他锁和意向共享锁和意向排他锁是冲突的，这样就避免扫描每行数据

锁类型	共享锁	排他锁	意向共享锁	意向排他锁
共享锁	兼容	互斥	兼容	互斥
排他锁	互斥	互斥	互斥	互斥
意向共享锁	兼容	互斥	兼容	兼容
意向排他锁	互斥	互斥	兼容	兼容

行锁：

记录锁(Record Lock)
间隙锁(Gap Lock)
临键锁(Next-Key Lock)

表结构：

CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` bigint DEFAULT NULL,
  `idNum` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_unique_idNum` (`idNum`),
  KEY `test_age_IDX` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查看隔离级别：

SELECT @@TRANSACTION_ISOLATION;

本人是REPEATABLE-READ 级别，可重复读

把死锁日志监控打开，用于后面查看死锁日志

# 设置标准监控打开
set GLOBAL innodb_status_output=ON;
# 设置锁监控打开
set GLOBAL innodb_status_output_locks=ON;

主键上加锁：

事务1:

# 设置当前会话自动提交关闭
SET autocommit = OFF;

select * from test where id = 1 for update

事务2:

# 设置当前会话自动提交关闭
SET autocommit = OFF;

select * from test where id = 1 for update

执行事务2时死锁，查看死锁日志，如下：

show engine innodb status

可以看到事务1(事务ID：7988)加了2个锁

1个是表级意向排他锁IX, 一个是行锁，主键索引上存在一个记录锁。

非唯一索引加锁

例子1: 加锁对象存在

SET autocommit = OFF;
select * from test where idNum = '1001' for update

查看死锁日志：

分别对应表级意向排他锁、基于idNum索引1001的锁，基于主键索引1的锁。

例子2：加锁对象不存在

SET autocommit = OFF;
select idNum from test where idNum = '1002' for update

查看死锁日志：

可以看到只加了2个锁，一个是表级意向排他锁，一个是间隙锁。锁区间范围是（1001， 1005），防止其他数据插入

非唯一索引