MySQL 锁

龍九^

已于 2022-08-22 21:36:44 修改

阅读量341

点赞数 3

文章标签： mysql 数据库

于 2022-08-22 21:01:04 首次发布

本文链接：https://blog.csdn.net/m0_66412572/article/details/126473231

版权

MySQL 锁

1. MySQL支持的锁有哪些？有哪些使用场景？

从锁的粒度上分，MySQL支持的锁分为表级锁、行级锁、页级锁
从锁的操作上说可以分为读锁和写锁
从实现方式上分可以分为乐观锁和悲观锁

使用场景

修改数据库表结构会自动加表级锁——元数据锁
更新数据未使用索引，行级锁会上升为表级锁
更新数据使用索引会使用行级锁
select … for update 没用索引的话就是表锁,否则会使用行级锁

锁的机制往往都是自动触发的，因为我们很少会去主动加lock（锁）

2. 一图了解锁的分类

在这里插入图片描述

3. 表锁

表锁是在MySQL Server层实现。每次操作都会锁住整张表。锁定粒度比较大，发生锁冲突的概率最高（对于行锁来说），并发度最低。想象一下如果client1进行了表锁，那么这个时候剩下的N个client的请求就会进行等待，所以它的效率是非常低的。

表锁应用在myisam、innodb、BDB等存储引擎当中，MySQL的表级锁有两种：

一种是表锁。
一种是元数据锁（meta data lock，MDL）

表锁有两种表现形式：

表共享读锁（table read lock）
表独占写锁（table write lock）

表级锁的使用：

client1 : lock table mylock read; //给mylock表加读锁

mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)

client1 : select * from mylock; //加完锁之后查询该表

mysql> select * from mylock;
+----+--------+
| id | name   |
+----+--------+
|  1 | 龍九   |
+----+--------+
1 row in set (0.00 sec)

client1 ：select * from tdep; //访问非加锁的表

mysql> select * from tdep;
ERROR 1100 (HY000): Table 'tdep' was not locked with LOCK TABLES  //表 tdep 没有被加锁  所以不能被访问

client2 : select * from mylock; //client2访问client1加了读锁的表不会被阻塞因为read锁和read锁是不冲突的

mysql> select * from mylock;
+----+--------+
| id | name   |
+----+--------+
|  1 | 龍九   |
+----+--------+
1 row in set (0.00 sec)

client2 ：update mylock set name=‘龍九’ where id=1；//client2给client1加了读锁的表加写锁。被阻塞（排队等待client1释放锁），因为read锁和write锁

mysql> update mylock set name='龍九' where id=1;
等待.....

client1 : unlock tables；//client1释放表锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
//锁被释放，client2修改成功
mysql> update mylock set name='龍九' where id=1;
Query OK, 0 rows affected (4 min 52.40 sec)
Rows matched: 1  Changed: 0  Warnings: 0

client1：select * from tdep； //释放锁后client1进行访问表tdep时，成功访问。

mysql> select * from tdep;
+----+--------+
| id | name   |
+----+--------+
|  1 | 龍九   |
+----+--------+
1 row in set (0.00 sec)

以上是加读锁，下面来加写锁

client1 ： lock table mylock write； //给mylock加写锁

mysql> lock table mylock write;
Query OK, 0 rows affected (0.03 sec)

client1 : select * from mylock； //查询加过锁的表，可以查询

mysql> select * from mylock;
+----+--------+
| id | name   |
+----+--------+
|  1 | 龍九   |
+----+--------+
1 row in set (0.00 sec)

client1：select * from tdep； // 查询非锁定表，不可访问

mysql> select * from tdep;
ERROR 1100 (HY000): Table 'tdep' was not locked with LOCK TABLES  // 表tdep没有加锁 所以不能被访问

client1 : update mylock set name = ‘龍九1’ where id = 1; //可以执行

mysql> update mylock set name='龍九' where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

client2 ：select * from mylock； // 查询其他用户已锁定的表，状态为阻塞（等待client1释放锁）因为write锁与read锁互斥

mysql> select * from mylock;
等待中.....

client1 ：unlock tables； //释放表锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
client1释放锁，client2查询成功
mysql> select * from mylock;
+----+--------+
| id | name   |
+----+--------+
|  1 | 龍九   |
+----+--------+
1 row in set (2 min 34.81 sec)

clinet1：select * from tdep；放掉锁之后就可以访问其他表了

mysql> select * from tdep;
+----+--------+
| id | name   |
+----+--------+
|  1 | 龍九   |
+----+--------+
1 row in set (0.00 sec)

4. 元数据锁

MDL（metaDataLock）元数据：表结构

在MySQL5.5版本中引入了MDL，当对一个表做CURD操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。打个比方，client1 在做CURD操作的时候，client2 是不可以进行修改表结构的；client2 修改表结构的时候，client1 是不可以做CURD操作的。它们是互斥的。

实例

client1：begin；//开启事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

client1：select * from mylock; //这个时候我们进行CURD操作都是加一个MDL读锁；

mysql> select * from mylock;
+----+--------+
| id | name   |
+----+--------+
|  1 | 龍九   |
+----+--------+
1 row in set (0.10 sec)

client2：alter table mylock add age int; //向表中添加一个年龄字段，在client1加了MDL读锁之后client2修改表结构是一个被阻塞的状态。

mysql> alter table mylock add age int;
等待中.....

client1 ： commit; //提交事务，或者rollback 进行释放锁，在client1释放锁之后，client2刚刚进行的修改表结构就会成功

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

//client2 修改表结构成功
mysql> alter table mylock add age int;
Query OK, 1 row affected (7 min 56.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

5. 行级锁

InnoDB实现，以上所讲到的都是mysql server层实现的，而行级锁的实现原理就是存储引擎

行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

RecordLock锁：锁定单个行记录的锁。RC、RR隔离级别都支持。
GapLock：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。RR隔离级别支持。
Next-key Lock 锁：行锁和间隙锁组合，同时锁住数据，并且锁住数据前面的Gap。RR隔离界别支持。

行锁的使用

查看行锁状态：show status like ‘innodb_row_lock%’;

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

client1：begin; // 开启事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

client1 : select * from mylock where id=1 lock in share mode; // 添加id=1的行读锁，使用索引

mysql> select * from mylock where id=1 lock in share mode;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 龍九id1   |   18 |
+----+-----------+------+
1 row in set (0.00 sec)

client2: update mylock set name=‘龍九’ where id=2; //可以修改因为client1锁定的是id=1的行

mysql> update mylock set name='龍九id1' where id=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

client2：update mylock set name=‘龍九’ where id=1; //不可修改,被阻塞。因为client1锁定了该行，需等到client1进行释放锁，该修改才能成功。

mysql> update mylock set name='龍九id1' where id=1;
等待中.....

client1：commit; // 提交事务或者 rollbock 释放锁

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

//client1释放锁之后client2修改就会被执行
mysql> update mylock set name='龍九id1' where id=1;
Query OK, 0 rows affected (11 min 24.72 sec)
Rows matched: 1  Changed: 0  Warnings: 0

以上就是行读锁的使用，下面来看行读锁升级为表锁的情况

client1 : begin; // 开启事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

client1 : select * from mylock where name=‘龍九1’ lock in share mode; //手动为name='龍九’的行加行读锁，而且不使用索引，这个时候行锁会上升为表锁。

mysql> select * from mylock where name='龍九1' lock in share mode;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | 龍九1   |   18 |
+----+---------+------+
1 row in set (0.00 sec)

client2 : update mylock set name=‘龍九id1’ where id=2; //修改被阻塞，等待修改。因为client1对name列进行加锁，name列是没有索引的，锁定了整张表，所以无论修改谁都是被阻塞的状态

mysql> update mylock set name='龍九id1' where id=1;
等待中.....

client1 : commit; // client1提交事务或者 rollback 释放读锁之后，client2对表进行修改

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

//client2 修改成功
mysql> update mylock set name='龍九id1' where id=1;
Query OK, 0 rows affected (5 min 21.69 sec)
Rows matched: 1  Changed: 0  Warnings: 0

行写锁

client1 : begin; // 开启事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

client1 : select * from mylock where id = 1 for update; // 手动给id=1的行加上一个行写锁;

mysql> select * from mylock where id=1 for update;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 龍九id1   |   18 |
+----+-----------+------+
1 row in set (0.12 sec)

client2 : select * from mylock where id=2; // 可以访问

mysql> select * from mylock where id=2;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | 龍九1   |   18 |
+----+---------+------+
1 row in set (0.00 sec)

client2 : select * from mylock where id=1； // 可以访问，不加锁

mysql> select * from mylock where id=1;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 龍九id1   |   18 |
+----+-----------+------+
1 row in set (0.00 sec)

client2 : select * from mylock where id=1 lock in share mode; //加读锁被阻塞，因为这个时候已经加了写锁，写锁和读锁冲突，所以阻塞

mysql> select * from mylock where id=1 lock in share mode;
等待中.....

client1 : commit; // client1 提交事务或者 rollback 释放写锁，这时client2加读锁成功

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

//client2
mysql> select * from mylock where id=1 lock in share mode;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 龍九id1   |   18 |
+----+-----------+------+
1 row in set (7 min 59.90 sec)

6. 什么是读写锁？什么是排它锁？

锁是计算机协调多个进程或者线程并发访问某一资源的机制。锁使用独占的方式来保证只有一个版本的情况下事务之间互相隔离，所以锁可以理解为低版本控制。

引入锁之后就可以支持并行处理事务，如果事务之间涉及到相同的数据项时，会使用他排他锁，或交互斥锁，先进入的事务独占数据项后，其他事务被阻塞，等待前面的事务释放锁。

读写锁，可以让读和读并行。而读和写、写和读、写和写这几种之间还是要加排他锁。

排他锁

引入锁之后就可以支持并行处理事务，如果事务之间涉及到相同的数据项时，会使用排它锁，或交互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。（在整个事务A结束之前，锁是不会被释放的，所以，事务B必须等到事务A结束之后开始）

读写锁

读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排它锁。如果几个事务之间没有共享数据项，完全可以并行被处理。

7. 行锁的分类和原理

行锁由InnoDB存储引擎实现，每次操作锁住一行数据。锁的粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB的行级锁，按照锁定的范围来锁，分为三种：

RecordLock锁：锁定单个行记录的锁。RC、RR隔离级别都支持。
GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。RR隔离级别支持。
Next-key Lock 锁：行锁和间隙锁组合，同时锁住数据，并且锁住数据前面的Gap。RR隔离级别支持。

行级锁分类

按照功能来锁，分为俩种：

共享读锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。（意识就是在我们开启了共享读锁的时候，只能允许其他事务进行共享读锁，而不能进行排他写锁）

排他写锁（X）：允许获得排他写锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁（不是读）和排他写锁。

行级锁的原理：主键加锁

加锁行为：如果对id = 5 的行进行update操作，仅在 id = 5 的主建索引记录上加 X 锁（排他写锁），如果是select则加 S 锁（共享读锁）。
在这里插入图片描述

行级锁的原理：唯一键加锁

加锁行为：如果对id = 5 的唯一键进行update操作，先在唯一索引 id 上加 id = 5 的 X 锁，再在 id = 10 的主建索引记录上加 X 锁。如果是select，就加 S 锁。
在这里插入图片描述

行级锁的原理：非唯一键加锁

加锁行为:对满足 id = 10 条件的记录和主键分别加X锁，然后在(6, c)~(10,b)、(10, b)~(10,d)
(10,d)~(11,f)间隙分别加Gap锁
在这里插入图片描述

行级锁的原理：无索引键加锁

加锁行为：对 name = zhangsan 这样的记录进行加锁时（name列没有索引），表里所有行和间隙加 X 锁。由于InnoDB引擎行锁机制是基于索引实现记录索引的，因此没有索引时会导致全表锁定。
在这里插入图片描述

8. 死锁是如何产生的？

加锁是实现数据并发控制的一个非常重要的技术，当两个事务的锁发生冲突，互相等待对方的锁释放，不能继续执行事务逻辑，就会出现死锁，严重影响应用的正常执行。死锁的现象主要有：表锁死锁、行级锁死锁、共享锁转换排它锁。

死锁的产生：表锁死锁

client1 ：访问表A之后访问表B

client2 ：访问表B之后访问表A

如果client1和client2同时进行访问，就会产生死锁。

产生原因：在 client1 访问表B时，由于 client2 已经锁住了表B，使用它必须等待 client2 释放表B才能继续，同样 client2 要等 client1 释放表A才能继续，这样死锁就产生了。

解决方案：调整程序的逻辑，比如说，如果要访问A 、B俩表的话，必须先访问表A然后再访问表B，避免同时锁定两个资源。

死锁的产生：行锁死锁

产生原因1 ：在事务中对没有索引列进行 for update 操作，就会进行全表扫描，行级锁上为表级锁，多个这样的事务同时执行的话，很容易就会产生死锁和阻塞。

解决方法：SQL语句中不要使用太复杂的关联表的查询，优化索引。

产生原因2 ：两个事务分别想拿到对方持有的锁，于是产生死锁。（和上面表锁产生死锁的方式一样，只不过把表换成了行）

解决方法：在同一个事务中，尽可能做到一次锁定需要的所有资源。进行有序排序，然后按照顺序进行处理。或者采用MVCC机制处理（MVCC对读操作不会上锁）。

死锁的产生：共享锁转排它锁

client1 查询一条记录，然后更新该条记录；此时 client2 也更新该条记录，这时 client2 的排它锁由于 client1 有共享锁，必须等待 client1 释放共享锁后才可以获取，只能排队等待。client1 再执行更新操作时，此处发生死锁，因为 client1 需要对这条记录上一个排它锁做更新操作。但是，无法授予该锁请求，因为 client2 已经有了一个排他锁请求，并且正在等待 client1释放共享锁。

龍九^

关注

3
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL 锁

锁是计算机协调多个进程或者线程并发访问某一资源的机制。锁使用独占的方式来保证只有一个版本的情况下事务之间互相隔离，所以锁可以理解为但版本控制。引入锁之后就可以支持并行处理事务，如果事务之间涉及到相同的数据项时，会使用他排他锁，或交互斥锁，先进入的事务独占数据项后，其他事务被阻塞，等待前面的事务释放锁。读写锁，可以让读和读并行。而读和写、写和读、写和写这几种之间还是要加排他锁。
复制链接

扫一扫