锁是为了解决并发访问某一个资源的解决机制,在某一个时刻,只能有一个获取到锁的进程或线程才能访问该资源。
在MySQL中,按照锁的粒度可以划分为如下:
- 全局锁:锁住整个数据库中的所有表,这时候其他连接只能读取数据库中的数据,不能对数据库进行修改操作,该类型的锁常用于对数据库执行备份操作时,具体命令和过程如下:
给数据库加全局锁:
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$
- 表级锁:锁住整张表,这种锁粒度大,发生锁冲突概率高,并发度低。
对于表级锁,可以分为:
- 表共享读锁和表独占写锁。对于加了读锁的表,所有连接都只能读取表中的数据,不能对表进行更新操作;对于加了写锁的表,当前连接可以读取表中的数据也可以更新表中的数据,其他客户端既不能读取表中的数据也不能更新表中的数据。
加锁(后面跟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)
- 行级锁:锁住表中的某一行数据。锁粒度最小,发生锁冲突的概率最低,并发度最高。
- 行锁:行锁分为共享锁和排它锁,在执行如下语句时会添加行锁。
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 都是非唯一索引,表中的数据如下:
对于主键索引和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