MySQL-锁

对于 InnoDB 引擎来说,读锁和写锁可以加在表上,也可以加在行上

1.从数据的操作类型划分

**读锁/共享锁(Shared Lock,S Lock)**:即S锁,

**写锁/排他锁(Exclusive Lock,X Lock)**:即X锁


对于读操作:

对读取的记录加上S锁:

1
2
3
select ... lock in share mode;
#或
select ... for share;

这样后面的事务可以对这些记录加S锁,但是不能加X锁

对读取的记录加X锁:

1
select ... for update;

这样其他事务就不可以对这些记录加S锁和X锁

可以在这些加锁的SQL语句后面加上nowait或者skip locked,这样当获取不到锁的时候就不会一直等待,而是直接报错或者结束,返回空


对于写操作:

必须加上X锁


2.从数据操作的粒度划分

针对不同的数据规模进行加锁,其并发度也不同,如果将锁加在一行记录上,获得的并发度是最大的,加在表上,并发度是最小的,而且管理锁也是很消耗资源的,因此数据库系统需要在并发响应和系统性能两方面进行平衡,这样就产生了锁粒度的概念。

行锁的锁粒度最细

2.1 表级锁

表级锁会锁定整张表,它是MySQL中最基本的锁策略,因此它并不依赖于存储引擎,而且表锁的开销比较小,粒度比较大。

(1)表级别的S锁、X锁

一般情况下,不会使用InnoDB存储引擎提供的表级别的 S锁X锁 。只会在一些特殊情况下,比方说 崩溃恢复 过程中用到。而由于MyISAM没有行锁,所以一般在MyISAM中使用表级锁

MySQL的表级锁有两种模式:(以MyISAM表进行操作的演示)

  • 表共享读锁(Table Read Lock)S锁

    自己可以读,自己不能写, 也不能操作其他表,别人可读,不可以写

    1
    lock tables 表名 read;
  • 表独占写锁(Table Write Lock)X锁

    自己可写可读,自己不可以操作其他表,别人不可以读和写

    1
    lock tables 表名 write;

(2)意向锁(intention lock)

为什么要有意向锁?

假如事务A在表T的某一行加了排他锁,那事务B想要在表T上加表级别的排它锁时,需要将这个表中的所有记录都遍历一遍,看看是否有行锁存在,如果没有,才可以加表锁,这步操作如果表中的记录很多时,非常消耗时间,因此在InnoDB中当为表中某行记录加锁后,会自动为表加一个意向锁,这样当另一个事务来加表锁,一看这个意向锁,就不可以加锁了。


InnoDB支持多粒度锁,它允许行级锁和表级锁共存,而意向锁就是其中的一种表锁。

意向锁是由存储引擎自己维护的,用户无法手动操作意向锁,在为数据行加共享/排他锁之前,InnoDB会先获取该数据行所在数据表的对应意向锁。

  • 意向共享锁(intention shared lock, IS)

    事务有意向对表中的某些行加共享锁,InnoDB存储引擎会自动给这个表加上IS锁

    1
    select column from table ... lock in share mode;
  • 意向排他锁(intention exclusive lock, IX)

    事务有意向对表中的某些行加排他,InnoDB存储引擎会自动给这个表加上IX锁

    1
    select column from table ... for update;

(3)自增锁(AUTO-INC锁)

1
2
3
4
5
CREATE TABLE `teacher` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

当创建这个表时,在id字段上添加了自增属性

在MySQL8.0是交错锁定模式

(4)元数据锁(MDL锁)

meta data lock,简称MDL锁,属于表锁范畴。

当对一个表做增删改查操作的时候,加 MDL读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

不需要显示使用,在访问一个表的时候会被自动加上

2.2 页级锁

在页的粒度上进行锁定。

2.3 InnoDB中的行锁(MyISAM中没有行锁)

(1)记录锁(Record Locks)

指把一条记录锁上,类型名称为:LOCK_REC_NOT_GAP,将一条记录加行锁,对其他记录没有影响

记录锁是有S锁和X锁之分的,称之为 S型记录锁X型记录锁

  • 当一个事务获取了一条记录的S型记录锁后,其他事务也可以继续获取该记录的S型记录锁,但不可以继续获取X型记录锁;
  • 当一个事务获取了一条记录的X型记录锁后(当一个事务update但不提交时,就会自动获得一个记录锁),其他事务既不可以继续获取该记录的S型记录锁,也不可以继续获取X型记录锁。

(2)间隙锁(Gap Locks)

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的,解决方案有两种,可以使用 MVCC 方案解决,也可以采用 加锁 方案解决。但是在使用加锁方案解决时有个大问题,就是事务在第一次执行读取操作时,那些幻影记录尚不存在,我们无法给这些 幻影记录 加上 记录锁 。InnoDB提出了一种称之为Gap Locks 的锁,官方的类型名称为: LOCK_GAP ,我们可以简称为 gap锁 。

对一条记录加gap锁,不会限制其他事务对这条记录加记录锁或者继续加gap锁,只是限制了在某个间隙不能insert操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select * from tb_user9;
+-------+----------+
| tb_id | tb_name |
+-------+----------+
| 1 | 张三 |
| 3 | 李四 |
| 4 | 王五5 |
| 6 | 王五5 |
| 7 | 王五7 |
| 8 | 王五 |
| 9 | 王五 |
| 15 | 王五15 |
+-------+----------+
#事务A查找id为10这条记录不存在后,就会自动在(9,15)加上间隙锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb_user9 where tb_id = 10 for update;
Empty set (0.00 sec)#无论这里是不是空,这个范围内都不允许被insert
#事务B想要对这个范围插入一条记录,就会被阻塞
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb_user9 values(13,'王五13');
#(阻塞),直到事务A提交了事务,放弃间隙锁

(3)临键锁(Next-key Locks)

记录锁和gap锁的合体,在这个范围内不允许被insert,如果是写锁,在边界处不允许被读和写

1
2
mysql> begin;
mysql> select * from tb_user9 where tb_id > 9 and tb_id <= 15 for update;

(4)插入意向锁(Insert Intention Locks)

如果一个事务在插入数据的时候,这个id所在的范围内有间隙锁或临键锁,则这个插入操作不能执行,需要等待这个锁释放,这时也会给这步骤加一个锁,即插入意向锁。

插入意向锁是一种 Gap锁 ,不是意向锁,在insert操作时产生。插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁

3.锁的内部结构

  1. 锁所在的事务信息 :
    不论是 表锁 还是 行锁,都是在事务执行过程中生成的,哪个事务生成了这个 锁结构 ,这里就记录这个事务的信息。
    此 锁所在的事务信息 在内存结构中只是一个指针,通过指针可以找到内存中关于该事务的更多信息,比方说事务id等。

  2. 索引信息 :
    对于 行锁 来说,需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。

  3. 表锁/行锁信息 :
    表锁结构和 行锁结构 在这个位置的内容是不同的:

  4. type_mode :
    这是一个32位的数,被分成了 lock_modelock_typerec_lock_type 三个部分

  5. 如果是行锁,在该结构后面还放置了一堆比特位,比特位的数量是由上边提到的n_bits属性表示的。在记录头中有heap_no这个属性,最小记录和最大记录对应的它的值是0,1。之后每插入一条数据记录中的这个属性就增1

4. 查看MySQL的锁情况

1.行锁情况:

1
show status like 'innodb_row_lock%';

2.查询正在被锁阻塞的sql语句

1
SELECT * FROM information_schema.INNODB_TRX\G;

3.查询锁等待情况

1
SELECT * FROM data_lock_waits\G;

4.查询锁的情况

1
SELECT * from performance_schema.data_locks\G;

MySQL-锁
https://vickkkyz.fun/2022/04/07/计算机/mysql/锁/
作者
Vickkkyz
发布于
2022年4月7日
许可协议