【MySQL】锁

最新推荐文章于 2024-08-21 16:45:54 发布

郝少康

最新推荐文章于 2024-08-21 16:45:54 发布

阅读量925

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/haoshaokang_/article/details/129723366

版权

锁

这篇文章来聊聊MySQL中的各种锁，以及这些锁的使用场景

全局锁

当开启全局锁之后，整个数据库会进入只读状态，这时候如果有其他线程（包括当前线程）执行以下操作，都会被阻塞。

执行insert，update，delete等修改数据的操作
执行alter table ，drop table等修改表结构的操作

开启全局锁命令：flush tables with read lock

关闭全局锁命令：unlock tables

全局锁的应用场景:全局锁主要应用于全库数据备份。

假如全库数据备份前不加全局锁，会出现什么情况呢?

假如现在有用户购买商品这样一个场景，不加全局锁进行数据备份，就可能会出现以下这种情况：
1.先备份了用户表，
2.用户购买了商品，
3.又备份了商品表。
也就是用户购买商品在备份用户表和备份商品表之间发生了。那备份的结果就是用户表中用户余额并没有减少，而商品表中的商品数量减少了。如果后续用备份结果恢复数据库，那就等于用户白嫖了一件商品。所以如果加上全局锁，再进行备份，因为不能写数据，只能读数据。就不会出现上面的情况

使用全局锁的问题：

但是全局锁也有一个大问题，就是如果数据库的数据量比较大，就会造成备份的时间比较长，那也就会造成业务的停滞时间比较长。因为全局锁期间只能读数据，不能写数据。

对于InnoDB存储引擎有一个比较好的解决办法，就是在可重复读隔离级别下，开启事务，进行全库数据备份。开启事务会创建read view，那么整个事务执行期间，也就是数据备份期间，看到的数据和事务启动时看到的数据是一致的。这样并不影响其他线程写数据。但是对于MyISAM存储引擎，它不支持事务，所以就没办法用这种方式数据备份

表级锁

MySQL中表级别的锁有：表锁，元数据锁（MDL），意向锁，AUTO-INC锁

表锁

对student表加表锁的命令：

//共享表锁，也就是读锁
lock tables student read;
//独占表锁，也就是写锁
lock tables student write;

释放当前会话的所有表锁：

unlock tables;

使用命令会释放当前会话的所有表锁，此外如果会话退出，也会释放所有表锁。

当对student加上共享表锁后，当前线程以及其他线程都只能读，不能写，当前线程尝试写会直接报错，其他线程尝试写会阻塞，等当前线程释放锁之后，才能写成功。

当对student加上独占表锁后，当前线程可以读，也可以写，其他线程不能读，不能写。其他线程尝试读或者写都会阻塞，直到当前线程释放独占表锁

元数据锁（MDL）

MDL不需要显示使用，因为当对表进行操作的时候会自动加MDL锁

对一张表进行CRUD操作，加的是MDL读锁，执行完CRUD自动释放锁
对一张表做结构变更操作的时候，加的是MDL写锁，执行完语句自动释放锁

当前线程如果在执行CRUD操作（加MDL读锁）期间，如果其他线程尝试更改表结构（申请MDL写锁）会被阻塞，直到执行完CRUD操作（释放MDL读锁），如果尝试执行CRUD操作，则不会被阻塞，读-读不冲突。

当前线程如果在执行更改表结构操作（加MDL写锁）期间，如果其他线程尝试执行CRUD操作（申请加MDL读锁）则会被阻塞，直到当前线程执行完更改表结构操作。

如果在事务中执行CRUD，或者做更改表结构操作，那MDL的释放是在事务提交后自动释放的。

接下来看一个问题：

线程A开启了事务（但是一直不提交），然后执行select语句，此时就会对表加上MDL读锁
线程B尝试更改表结构，会发生读-写冲突，会被阻塞
线程C尝试select，但是会被阻塞

当线程B被阻塞后，后续如果有其他线程尝试执行CRUD都会被阻塞，按理来说，当前表加的是MDL读锁，如果有其他线程尝试读不应该会阻塞，因为读-读不冲突，但是实际情况是会阻塞，原因是：

事情MDL锁的操作会形成一个优先级队列，在这个队列中，写锁优先级的获取高于读锁，所以必须得等写锁被获取后，然后释放了写锁，线程才能获取读锁。所以，由于线程A中的事务一直不提交，导致线程B写锁一直获取不到，进而导致其他线程的CRUD被阻塞。

所以在变更表结构的时候，可以提前看看数据库中的长事务（长时间未提交的事务）是否对表加了MDL读锁

意向锁

在InnoDB引擎中，在对表中记录加锁（也就是加行级锁）之前，要先对表加意向锁：

在对表中记录加共享锁之前，要对表先加上意向共享锁
在对表中记录加独占锁之前，要对表先加上意向独占锁

意向共享锁和意向独占锁是表级锁，不会和行级的独占锁和共享锁发生冲突的。

AUTO-INC锁

表中的主键通常会设置成自增的，那如何保证在多线程环境下，主键自增不会发生线程安全问题呢？

如果两个线程同时插入一条语句，同时判断当前自增主键的最大值是5，然后就会插入两条主键是6的记录，这就发生了线程安全问题，为了解决这种问题，就需要用到AUTO-INC锁。

AUTO-INC锁是一种特殊的表锁机制，在事务中用到时，会等插入语句执行完就立即释放锁，而不是等事务提交后才释放锁。

当一个事务持有AUTO-INC锁时，其他事务如果想要插入语句，就会被阻塞，因此解决了上面提到的线程安全问题。

行级锁

InnoDB引擎是支持行级锁的，而MyISAM不支持行级锁

共享锁（S锁）满足读读共享，读写互斥。独占锁满足读写互斥，写写互斥。

Record Lock

Record Lock称为记录锁，锁住的是一条记录。记录锁有S型记录锁和X型记录锁之分。

当一个事务对一条记录加了S型记录锁，其他事务也可以继续对该纪录加 S 型记录锁，因为读读不互斥。但是不能对该记录加X型锁。因为读写互斥

当一个事务对一条记录加了X型记录锁，其他事务不可以继续对该纪录加 S 型记录锁，因为读写互斥。也不能对该记录加X型锁。因为写写互斥

Gap Lock

Gap Lock称为间隙锁，只存在于可重复读隔离级别下，目的是解决可重复读隔离级别下的幻读现象。
如果表中有一个id范围是（3，5）的间隙锁，那么其他事务就没法插入id=4的这条记录了，这样就解决了幻读。

间隙锁存在X型间隙锁和S型间隙锁，但是间隙锁之间是兼容的，并不会存在互斥关系，两个事务可以同时持有包含共同间隙范围的间隙锁。

如果一个事务A加了（15,20]范围的X型的next-key lock，则事务B也是可以在(15,20)的范围内加X型的间隙锁的(因为相当于在(15,20)范围内加了两个间隙锁，间隙锁是可兼容的），但是事务B不能加（15,20]范围内的X型的next-key lock，X型的临键锁和X型的临键锁是不兼容的。