MySQL中锁分类总结

不管现实多么惨不忍睹,都要持之以恒地相信,这只是黎明前短暂的黑暗而已。不要惶恐眼前的难关迈不过去,不要担心此刻的付出没有回报,别再花时间等待天降好运。真诚做人,努力做事!你想要的,岁月都会给你。MySQL中锁分类总结,希望对大家有帮助,欢迎收藏,转发!站点地址:www.bmabk.com,来源:原文

锁是为了解决并发访问某一个资源的解决机制,在某一个时刻,只能有一个获取到锁的进程或线程才能访问该资源。
在MySQL中,按照锁的粒度可以划分为如下:

  1. 全局锁:锁住整个数据库中的所有表,这时候其他连接只能读取数据库中的数据,不能对数据库进行修改操作,该类型的锁常用于对数据库执行备份操作时,具体命令和过程如下:
给数据库加全局锁:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

执行数据备份操作,该命令不需要在MySQL客户端执行,直接在命令行执行(test为数据库名):
MacBookPro:~ xingo$ mysqldump -uroot -p123456 test > ./test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
MacBookPro:~ xingo$ 

释放全局锁:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

在数据库加全局锁时,会带来下面的问题:

  • 加上全局锁的数据库不能执行更新操作,这样会导致整个业务无法写入数据。
  • 如果全局锁加在从库,由于从库不能执行binlog日志,这样会导致主从复制的延迟。

对于innodb存储引擎,可以在数据备份时添加参数 –single-transaction 来完成不加锁的数据备份。它的底层是通过快照读来实现数据一致性的。

在执行数据备份时添加参数 --single-transaction ,就可以不加全局锁执行备份数据(test为数据库名):
MacBookPro:~ xingo$ mysqldump --single-transaction -uroot -p123456 test > ./test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
MacBookPro:~ xingo$ 
  1. 表级锁:锁住整张表,这种锁粒度大,发生锁冲突概率高,并发度低。
    对于表级锁,可以分为:
  • 表共享读锁和表独占写锁。对于加了读锁的表,所有连接都只能读取表中的数据,不能对表进行更新操作;对于加了写锁的表,当前连接可以读取表中的数据也可以更新表中的数据,其他客户端既不能读取表中的数据也不能更新表中的数据。
加锁(后面跟read就是加读锁、write就是写锁):
mysql> lock tables tb_book read / write;
Query OK, 0 rows affected (0.01 sec)

释放锁:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
  • 元数据锁(MDL):当访问一张表的时候,系统会自动控制加锁,无需显式使用。MDL锁主要是保证表中元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入。
SQL语句 锁类型 说明
lock tables xxx read / write SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select … lock in share mode SHARED_READ 与SHARED_READ、SHARED_WRITE兼容,与EXCLUSIVE互斥
insert 、update 、delete 、select … for update SHARED_WRITE 与SHARED_READ、SHARED_WRITE兼容,与EXCLUSIVE互斥
alter table … EXCLUSIVE 与其他MDL锁互斥

元数据锁信息保存在系统表metadata_locks中,可以通过如下sql语句进行查询:

mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

+-------------+--------------------+----------------+-------------+---------------+
| object_type | object_schema      | object_name    | lock_type   | lock_duration |
+-------------+--------------------+----------------+-------------+---------------+
| TABLE       | performance_schema | metadata_locks | SHARED_READ | TRANSACTION   |
+-------------+--------------------+----------------+-------------+---------------+
1 row in set (0.00 sec)
  • 意向锁:当要对某一个表加表级锁时,它会首先检查这个表是否已经加了其他锁(如行锁),如果检查到当前表已经加了锁,那么加锁就会失败,这个检查过程是逐行进行的,性能非常低;为了解决这个问题,当表已经加了行锁,会自动添加一个意向锁,这时在对这个表加表级锁时,就会通过判断意向锁来确定是否可以加锁。当释放行锁时,这个意向锁也会自动释放。
    意向锁分为共享锁和排它锁。意向共享锁与表锁的读锁(read)兼容,与表锁的写锁(write)互斥,意向排它锁与表锁的读锁(read)以及写锁(write)都是互斥的,意向锁之间不互斥。
意向共享锁:
select ... lock in share mode;

意向排它锁(执行下面的语句时自动添加):
insert 、update 、delete 、select ... for update;

意向锁也是在系统数据库中,通过如下sql语句查询:

mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)

演示一下加意向锁和查看意向锁数据:

开始事务:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

执行一条查询语句使用共享锁:
mysql> select * from tb_book where id=1 lock in share mode;
+----+--------+-----------------+--------------------------------------+
| id | author | name            | content                              |
+----+--------+-----------------+--------------------------------------+
|  1 | 金庸   | 倚天屠龙记        | 主角张无忌相关的奇人异士                  |
+----+--------+-----------------+--------------------------------------+
1 row in set (0.00 sec)

查看锁信息:
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| mydb          | tb_book     | NULL       | TABLE     | IS            | NULL      |
| mydb          | tb_book     | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

提交事务:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查看锁信息:
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)
  1. 行级锁:锁住表中的某一行数据。锁粒度最小,发生锁冲突的概率最低,并发度最高。
  • 行锁:行锁分为共享锁和排它锁,在执行如下语句时会添加行锁。
SQL语句 锁类型 说明
insert 、update 、delete … 排它锁 自动加锁
select … lock in share mode 共享锁 select时通过 lock in share mode 加锁
select … for update 排它锁 select时通过 for update 加锁
select … 不加任何锁

行锁也是在系统数据库中,通过如下sql语句查询:

mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)

下面演示一下行锁加锁过程和查看:

开始事务:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

使用 for update 关键字加锁:
mysql> select * from tb_book where id=1 for update;
+----+--------+-----------------+--------------------------------+
| id | author | name            | content                        |
+----+--------+-----------------+--------------------------------+
|  1 | 金庸   | 倚天屠龙记      | 张无忌的人生成长轨迹           |
+----+--------+-----------------+--------------------------------+
1 row in set (0.00 sec)

查看加锁信息,可以看到加了意向锁和行锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| mydb          | tb_book     | NULL       | TABLE     | IX            | NULL      |
| mydb          | tb_book     | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

回滚事务:
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)

注意的是,加行锁是根据数据的索引查找到记录的主键进行加锁的,如果不能通过索引查找到记录,那么会加表级锁,也就是每一个记录都会加锁。

  • 间隙锁:锁定的是一个范围,对于一索引,当我们执行范围筛选条件时,就会锁定这个范围内的的索引数据,间隙锁不包含范围两端的索引值,也就是这个范围两端都是开区间。
  • 临键锁:相当于是行锁和间隙锁的组合,它锁定的是一个左开右闭的范围区间,对于非唯一索引加行锁时会添加这种临键锁。

下面演示一下间隙锁和临键锁情况,数据库中有一个表tb_book,该表的内容如下:

建表语句:
mysql> show create table tb_book \G
*************************** 1. row ***************************
       Table: tb_book
Create Table: CREATE TABLE `tb_book` (
  `id` int NOT NULL,
  `author` varchar(20) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  `content` varchar(1024) DEFAULT NULL,
  `price` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `author_idx` (`author`),
  KEY `price` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

这个表主键列名是id,有两个索引 author、price 都是非唯一索引,表中的数据如下:
tb_book数据
对于主键索引和price列的普通索引叶子节点结构如下:
索引结构

(1)唯一索引的等值查询,在给不存在的记录加锁时,会优化为间隙锁。比如在主键3~12之间没有数据,当执行一个更新语句的判断条件是:id=8,那么在3~12之间的数据都加锁,这时如果再执行插入语句(id范围在3~12)就会阻塞。

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

mysql> update tb_book set name='大秦帝国' where id=8;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

这里对主键ID小于12的范围加间隙锁:
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| mydb          | tb_book     | NULL       | TABLE     | IX        | NULL      |
| mydb          | tb_book     | PRIMARY    | RECORD    | X,GAP     | 12        |
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

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

(2)普通索引的等值查询,会把等值后的间隙都加上间隙锁,对等值记录加行锁,因为对于非唯一索引,有可能在加锁期间添加与索引值相同的记录。

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

mysql> update tb_book set price=15 where price=15;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

这里对price=15的记录加行锁,对 (15, 20) 区间的记录加间隙锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| mydb          | tb_book     | NULL       | TABLE     | IX            | NULL      |
| mydb          | tb_book     | price      | RECORD    | X             | 15, 2     |
| mydb          | tb_book     | price      | RECORD    | X             | 15, 3     |
| mydb          | tb_book     | PRIMARY    | RECORD    | X,REC_NOT_GAP | 2         |
| mydb          | tb_book     | PRIMARY    | RECORD    | X,REC_NOT_GAP | 3         |
| mydb          | tb_book     | price      | RECORD    | X,GAP         | 20, 12    |
+---------------+-------------+------------+-----------+---------------+-----------+
6 rows in set (0.00 sec)

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

(3)唯一索引的范围操作,会把满足范围条件的区间加上锁。

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

mysql> update tb_book set author='xingo' where id<=12;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

把范围内的记录都加上了行锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| mydb          | tb_book     | NULL       | TABLE     | IX        | NULL      |
| mydb          | tb_book     | PRIMARY    | RECORD    | X         | 1         |
| mydb          | tb_book     | PRIMARY    | RECORD    | X         | 2         |
| mydb          | tb_book     | PRIMARY    | RECORD    | X         | 3         |
| mydb          | tb_book     | PRIMARY    | RECORD    | X         | 12        |
+---------------+-------------+------------+-----------+-----------+-----------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

这里要注意,间隙锁是为了防止其他事务在间隙上插入数据,间隙锁是允许其他事务继续加间隙锁的。

以上就是MySQL锁相关的内容总结,很多东西都是经过多方查找资料整理的,可能有些内容存在偏差,希望大家指正修改,继续提高学习。

聊天学习页面:https://www.mychats.com.cn

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

文章由极客之家整理,本文链接:https://www.bmabk.com/index.php/post/181864.html

(0)
飞熊的头像飞熊bm

相关推荐

发表回复

登录后才能评论
极客之家——专业性很强的中文编程技术网站,欢迎收藏到浏览器,订阅我们!