MySQL中的锁

于 2022-06-15 22:24:31 发布
阅读量129 收藏
点赞数
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/medouyoulo/article/details/125305912
版权

锁

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。数据库的数据中就是一种资源，用锁来保数据并发访问的一致性、有效性。锁冲突也是影响数据库并发访问性能的一个重要因素。

查看数据库中的锁

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks

全局锁

对整个数据库实例加锁，加锁后整个实例就处于只读状态。后续的DML写语句，DDL语句，以及更新操作的事务提交语句都将被阻塞。

典型的使用场景：全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

语法

加锁

flush tables with read lock; 

> mysqldump -h 192.168.0.1 -uroot -p1234 dbname > db.sql  #在终端备份数据库，演示用

解锁

unlock tables;

特点

数据库加全局锁存在以下问题

  1. 如果在主库上备份，那么在备份期间都不能执行更新，业务停摆
  2. 如果从库上备份，那么在备份期间不能执行主库同步过来的二进制日志（binlog），会导致主从延迟

在InnoDB引擎中，全库备份可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份

mysqldump --single-transaction -uroot -p123456 dbname > db2.sql

表级锁

每次操作锁住整张表。锁的粒度大，发生冲突的概率最高，并发度最低。应用在InnoDB、MyISAM中。

表级锁分为三类：

  1. 表所
  2. 元数据锁（meta data lock）
  3. 意向锁

表锁

表锁分为两类：

  1. 表共享读锁 read lock 阻塞所有线程的写
  2. 表共享写锁 write lock 阻塞其他线程的读和写
语法

加锁

lock tables tb1 read;  -- 为tb1添加读锁
lock tables tb2 write  -- 为tb2添加写锁

解锁

unlock tables;

或者客户端断开连接，也能解锁

元数据锁 meta data lock, MDL

MDL加锁是系统自动控制，无法显式使用，在访问一张表的时候会自动加上。MDL锁主要维护元数据表的一致性，在表上有活动事务的时候，不可以对元数据执行写入操作。为了避免DML与DDL的冲突，保证读写的正确性。

当对一张表执行DDL(curd)时，加MDL读锁；当对表结构进行变更操作的时候，加MDL写锁。

SQL语句锁类型说明
selectSHARED_READ与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert、update、delete、select…for updateSHARED_WRITE与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table…EXCLUSIVE与其他MDL都互斥

结论

  • 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
  • 读锁与写锁之间、写锁与写锁之间是互斥的，保证 DML 和 DDL、DDL 和 DDL 顺序执行

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能长时间读阻塞。

意向锁

意向锁用来解决行锁与表锁的冲突问题。

意向锁类型SQL语句互斥情况
意向共享锁(IS)select … lock in share mode与表锁中共享锁(read)兼容，与表锁中排他锁(write)互斥
意向排他锁(IX)insert、update、select… for update与表锁中的共享锁(read)和排他锁(write)互斥。意向锁之间不会互斥。
案例

意向共享锁IS 与表锁的兼容情况

-- 线程A
-- 为该行添加行锁
-- select ... in share mode 为tb_user添加意向共享锁
>begin;
>select * from tb_user where id=2 lock in share mode;

-- 线程B
-- 为tb_user表添加读锁。表锁中的读锁与意向共享锁IS兼容
>begin;
>lock tables tb_user read;
Query OK 

> unlock tables;
Query OK 

-- tb_user表添加写锁，将会阻塞。表锁中的写锁与意向共享锁IS互斥
>lock tables tb_user write;
-- 阻塞，直到线程A中的事务提交

意向排他锁IX 与表锁的兼容情况

-- 线程A
-- 为该行添加行锁
-- udate.. 为tb_user添加意向排他锁
>begin;
>update tb_user set name='liaoqi' where id=2;

-- 线程B
-- 为tb_user表添加读锁或写锁，将会阻塞
>begin;
>lock tables tb_user read;
-- 阻塞,直到线程A中的事务提交

行级锁

行级锁，每次操作都会锁住对应的数据，锁的粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB的数据是基于索引组织的。行锁是通过对索引上的索引项加锁来实现的，而不是对记录加锁。

行级锁的分类

行级锁类型特点支持隔离级别
行锁 Record lock锁定单个记录的锁，防止其他事务对此行进行update和delete。RC 、RR
间隙锁Gap lock锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙insert，产生幻读RR
临建锁Next-key lock行锁和间隙锁组合，同时锁住数据，并锁柱前面的间隙GapRR

行锁

行锁分为两类

  1. 共享锁 S: 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

    （兼容其他事务的S，排斥其他事务的X）

  2. 排他锁 X：允许排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

    （排斥其他事务的S和X)

SQL行锁类型说明
insert排他锁 X自动加锁
update排他锁 X自动加锁
delete排他锁 X自动加锁
select(正常)不加任何锁
select … lock in share mode共享锁需手动在select语句之后声明in share mode
selet … for update排他锁 X需手动在select语句之后声明for update

默认情况下，InnoDB在Repeatable Read事务级别运行，使用临界锁next-key搜索和索引扫描，以防止幻读

创建一张表stu，拥有字段 id (主键/唯一索引), name , age（普通索引），并插入以下数据

idnameage
1java1
3php3
7ruby7
11python11
15go15
17vue17

  1. 针对唯一索引 unique 检索时，对已存在的记录进行等值匹配，将自动优化为行锁

    begin;
select * from stu where id = 1 lock in share mode;
/*加锁情况:
  stu: IS
  id为 1 的行： Reocrd lock (S)  【其他事务也可为id为1的行添加Record lock (S)】
*/

    begin;
update stu set name ='C++' where id = 3;
/*加锁情况：
  stu: IX
  id为3的行: Recored lock (X) 【其他事务不能id为3的行添加Record lock (S/X)】
*/

  2. InnoDB的行锁是针对索引加的锁，不通过索引检索数据时，InnoDB将对表中的所有记录加锁。此时就会升级为表锁

    begin;
update stu set name ='C#' where name="java";
/*加锁情况：
stu：IX  【name字段没有索引，升级为表锁】
*/

间隙锁和临界锁

默认情况下，InnoDB在Repeatable Read事务级别运行，使用临界锁next-key搜索和索引扫描，以防止幻读

  1. 索引上的等值查询（唯一索引 unique），给不存在的记录加锁时，优化为间隙锁 gap lock

    begin;
update stu set age=10 where id = 8;
/*加锁情况：
 stu: IX
 id (7，11)之间的间隙：Gap lock  
 */

  2. 索引上的等值查询（普通索引 【非唯一】），向右遍历最后一个值不满足查询需求临界锁，next-key lock退化为gap lock

    -- 已为age字段建立索引
begin;
select * from stu where age = 3 lock in share mode;
/*加锁情况
stu: IS
age在(3, 7)之间的行的间隙：Gap lock
*/

  3. 索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止

    begin;
select * from stu where id>=11 lock in share mode;
/*加锁情况
stu: IS
id>=11的行: next-key lock 
【即id为11、25的行Record lock + (11,25) (25,+∞)之间间隙的Gap lock】
*/

总结

  1. 不使用索引查询，用表锁

  2. 唯一索引上的等值查询，如果记录存在，用Record lock，锁住该行

  3. 唯一索引上的等值查询，如果记录不存在，用Gap lock，锁住（last， next）之间的间隙

  4. 唯一索引上的范围查询，用Next-key lock 锁住范围之间的行和间隙

  5. 普通索引上的等值查询，用Gap lock，锁住（next，this）和（this，next）之间的间隙

medouyoulo
关注
MySQL中的LOCK和UNLOCK
weixin_45958024的博客
01-03 997
MySQL中的LOCK和UNLOCK 今天在学习MySQL的备份的时候，通过源码获得一串代码 LOCK TABLES `fb_user` WRITE; /*!40000 ALTER TABLE `fb_user` DISABLE KEYS */; INSERT INTO `fb_user` VALUES (1,018,'coding','w','dennis'),(2,018,'coding','...
MySQL中锁使用及原理
chengxuyuan_2012的专栏
04-02 2043
当我们向一个由AUTO_INCREMENT关键字的主键插入值的时候，每条语句都要对这个表锁进行竞争，这样的并发潜力其实是很低下的，所以InnoDB通过innodb_autoinc_lock_mode的不同取值来提供不同的锁定机制，来显著提高SQL语句的可伸缩性和性能。这也意味着在高并发的情况下，可能会出现自增值的重复或者不连续的情况。表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。
​mysqldump --lock-tables​ 导出 锁库 已解决
PHP代码的博客
04-14 1309
mysqldump有一个参数--lock-tables，以前对这个参数也没有详细了解过，直到上次有个网友问“参数lock-tables 是一次性锁定当前库的所有表，还是锁定当前导出表？“，之前一直以为只是锁定当前导出表，后面看了参数说明后， -l, --lock-tablesLock all tables for read. (Defaults to on; use --skip-lock-tables to disable.) 自己也不怎么确认了，当时就测试了一下。准备一个稍微大一点的库，如...
mysql lock table阻塞读_MySQL的lock tables和unlock tables的用法【数据库锁表】
weixin_30047059的博客
01-19 949
早就听说lock tables和unlock tables这两个命令，从字面也大体知道，前者的作用是锁定表，后者的作用是解除锁定。但是具体如何用，怎么用，不太清楚。今天详细研究了下，总算搞明白了2者的用法。lock tables 命令是为当前线程锁定表.这里有2种类型的锁定，一种是读锁定，用命令 lock tables tablename read;另外一种是写锁定，用命令lock tables ...
mysql flush tables with read lock_flush tables with read lock和lock tables users write区别
weixin_42241165的博客
02-17 330
执行flush tables with read lock以后所有数据库的表都处于锁定状态(库级锁)，不能删除表，不能修改数据，加数据等操作，一般这种操作是数据库在线备份的时候用到的，这个时候数据库不能写，但是读操作能够正常执行lock tables users read(表级锁);这个操作知识针对具体的单张表,执行这个操作，可以共享读操作，在其他会话中也可以读这张表，但不能写入mysql>...
MySQL的lock tables和unlock tables的用法
红尘道，红尘练心
05-30 878
早就听说lock tables和unlock tables这两个命令，从字面也大体知道，前者的作用是锁定表，后者的作用是解除锁定。但是具体如何用，怎么用，不太清楚。今天详细研究了下，总算搞明白了2者的用法。lock tables 命令是为当前线程锁定表.这里有2种类型的锁定，一种是读锁定，用命令 lock tables tablename read;另外一种是写锁定，用命令lock...
MySQL中的悲观锁与乐观锁
09-14
在MySQL数据库中，悲观锁（Pessimistic Lock）和乐观锁（Optimistic Lock）是两种常见的并发控制机制，它们用于解决多用户环境下同一资源的并发访问问题。这两种锁各有特点，适用于不同的业务场景。 首先，悲观锁...
MySQL中的行级锁、表级锁、页级锁
09-10
行级锁是MySQL中最细粒度的锁，它只锁定操作的特定行。这减少了数据冲突的可能性，提高了并发性能。行级锁分为共享锁（Read Lock）和排他锁（Write Lock）。共享锁允许其他事务读取锁定的行，而排他锁则阻止其他事务...
mysql中锁Name_locked为0_Mysql 图解锁机制
weixin_34608787的博客
02-03 2357
MySQL 锁机制MySQL锁: 锁是计算机协调多个进程或线程并发访问某一资源的机制. 在数据库中, 数据是一种供许多用户共享的资源. 因此需要借助锁来解决数据并发访问的一致性、有效性MySQL锁的分类:从对数据操作的类型分: 读锁、写锁读锁(共享锁): 针对同一份数据, 多个读操作可以同时进行而不会相互影响写锁(排他锁): 当前写操作没有完成前, 它会阻断其他写锁和读锁从对数据操作的粒度分: 表...
mysql 锁机制及mysql中的锁介绍.pdf
10-17
系统介绍mysql锁机制
MySQL权限篇之LOCK TABLES及元数据锁
热门推荐
Qiuht的技术博客
03-29 1万+
mysql lock table Metadata Locking
mysql的lock tables与unlock tables的用法
lpp_dd的博客
09-12 2572
1、在对mysql数据库中的表进行操作时可以通过添加当前线程下的表的锁实现当前线程下的数据安全。 2、添加只读锁(read) 如果一个线程获得在一个表上的read锁，那么该线程和所有其他线程只能从表中读数据，不能进行任何写操作。 -- lock tables read 与 write select log_id from SYS_SYSTEMLOG; -- 将sys_systemlog表进行
mysql lock write_MySQL_写锁_lock tables tableName write
weixin_35934012的博客
01-19 2282
pre.环境准备1.建立两个表S,T，并插入一些数据--创建表Screate table S(d int) engine=innodb;insert into S values (3);--创建表Tcreate table T(c int) engine=innodb;insert into S values (10);2.打开两个客户端连接一.写锁1.对锁定表进行查看，修改在session1中对...
MySQL_写锁_lock tables tableName write
weixin_30333885的博客
03-30 1739
pre.环境准备 　　1.建立两个表S,T，并插入一些数据 1 --创建表S 2 create table S(d int) engine=innodb; 3 insert into S values (3); 4 5 --创建表T 6 create table T(c int) engine=innodb; 7 insert into S values (10);...
MySQL锁定状态查看命令
在路上
04-08 5814
1 show processlist; SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。如果有线程在update或者insert 某个表，此时进程的status为updating
MySQL锁表lock tables和unlock tables
enough_br的专栏
07-12 4075
如果你的mysql用的是myIsam存储引擎，就可以通过手动锁表来保证读得数据是可靠的。如果用的是InnoDB就不需要这么做。 lock tables 命令是为当前线程锁定表.这里有2种类型的锁定，一种是读锁定，用命令 lock tables tablename read;另外一种是写锁定，用命令lock tables tablename write.下边分别介绍： 1. lock tab
mysql data_locks_Mysql事物与Metadata lock 问题
weixin_28752743的博客
01-27 507
环境说明：MySQL 5.6.16OS:Linux RedHat 6.2 64bit1.问题描述目前新上一个使用MySQL数据库项目，在数据库中，每隔5分钟做truncate某个表操作，经常出现metadata lock锁等待，导致后面的对这个表的所有操作(包括读)全部metadata lock等待。严重影响了数据库运行。且metadata lock锁等待不同于普通的行级锁，等待超时时间默认为36...
MySQL 中锁的分类
最新发布
07-17
在MySQL中，锁（Locking）用于控制并发访问数据库资源以避免数据一致性问题。MySQL支持多种类型的锁，这些锁可以分为以下几类： 1. **共享锁（Shared Locks, S）**: - 允许读取数据，但阻止其他事务对同一行进行写操作。如果多用户同时请求共享锁，则所有请求都会立即获得锁。 ```sql SELECT * FROM table WHERE id = 1; -- 获取S锁 ``` 2. **排他锁（Exclusive Locks, X）**: - 只允许一个事务独占一行，阻止其他事务进行读取或写入操作。 ```sql INSERT INTO table VALUES (1, 'value'); -- 获取X锁 UPDATE table SET column = 'new_value' WHERE id = 1; -- 获取X锁 DELETE FROM table WHERE id = 1; -- 获取X锁 ``` 3. **意向锁（InnoDB Only, IX）**: - InnoDB存储引擎特有的，用于锁定表级，允许事务锁定整个表以便在其范围内进行插入或删除操作。 ```sql LOCK TABLES table WRITE; -- 获取IX锁 ``` 4. **行级乐观锁（Row-Level Optimistic Locking, ROWX）**: - MySQL的默认行为是行级锁定，但可以通过`SELECT ... FOR UPDATE`语句实现乐观锁，它会检查行的版本号是否与先前读取时一致。 5. **死锁（Deadlocks）**: - 当两个或更多的事务因等待对方释放资源而互相阻塞时，就会发生死锁。 6. **自旋锁（Spin Locks）**: - 这不是MySQL的标准锁机制，而是某些库或优化策略中使用的高级概念，它让进程在获取锁失败时循环尝试，直到成功。 了解这些锁的类型有助于管理和优化并发性能，尤其是在高并发环境下。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值