mysql中锁机制的介绍

壹个人的火锅

于 2024-05-26 08:16:27 发布

阅读量959

点赞数 25

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/m0_63512120/article/details/139204504

版权

数据库锁

全局锁

锁定数据库中的所有表。

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

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

缺点：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟。

添加全局锁

flush tables with read lock;

释放全局锁

unlock tables;

表级锁

每次操作锁住整张表。锁定粒度大，发生所冲突的概率最高，并发量最低。

大概分三类：表锁、元数据锁、意向锁、 AUTO-INC 锁

加锁：

lock tables 表名  read/write

释放锁：

unlock tables / 客户端断开连接

**表锁：**表共享读锁（read lock），表独占写锁（write lock）。

表共享读锁：所有客户端只能读，不能写入。

表独占写锁：加锁的客户端可以对表进行读操作和写操作，其他客户端对表不能读不能写。

**元数据锁（MDL）：**在访问一个表的时候，系统自动就加上了这个锁。
该锁的主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。
也就是说，这个表有正在进行中的事务，那此时我们不能修改这个表的结构。

当对一个表进行增删改查的时候，自动加上读锁（共享）；

当对表结构进行变更操作的时候，自动加上写锁（排他）。

查看元数据锁：

select object type,obiect schema,object name,lock type,lock duration from performance schema.metadata locks;

实例：

线程 A 启动一个事务，执行了一个 select 语句，然后该表就自动加上了 MDL读锁。

线程 B 也启动了一个事务，也执行一个 select 语句，此时线程不会阻塞，因为读读操作不冲突。

线程 C 也启动了一个事务，但是该事务用来修改表字段，因为上面的事务没提交 MDL 读锁还在占用，所以此时线程 C 就无法申请MDL写锁，线程就会被阻塞了。

那当线程C阻塞后，后面如果还有对表执行select语句，就都会被阻塞，此时如果请求量大的时候，数据库线程就会爆满。

这里为什么线程C申请不到MDL写锁后，后面的所有申请读锁操作会被阻塞？

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

意向锁： 为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

意向共享锁(IS)：由语句select… lock in share mode 添加。与表锁共享锁兼容，与表锁排它锁互斥。

意向排他锁(IX)：由insert、update、delete、select…for update 添加。与表锁共享锁和排它锁都互斥。意向锁之间不互斥。

查看意向锁及行锁的加锁情况

select object_schema,abject_name,jindex_name,lock_type,lock_mode,lock_data 
from performance_schema.data_locks;

AUTO-INC 锁：

行级锁

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

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

行锁(Record Lock)：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

间隙锁(Gap Lock)：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。

临键锁(Next-Key Lock)：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

行锁