MySQL简单锁知识

概述

前言

事务提交，锁都会释放掉

MyISAM 只支持到表级锁

InnoDB 可以支持到行级锁

介绍

 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

为什么需要锁

事物在并发场景下会发生读-读、读-写、写-写三种冲突，而冲突会导致脏读、不可重复读、幻读以及更新丢失等一些问题

为了保证数据的完整性和一致性，需要使用锁来支持对共享资源的并发访问，结合多版本并发控制在很多情况下避免了加锁操作

锁的分类

按照锁的粒度，可以分为以下三种锁

全局锁：锁住数据库所有的表

表级锁：每次操作锁住整张表

行级锁：每次操作锁住对应的行数据

全局锁

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

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

不加锁在进行数据库备份的时候，很可能出现数据不一致的情况

在全局锁期间，除了DQL语句，其他语句都不能执行

#加全局锁，mysql环境下
flush tables with read lock;


#保存数据库的备份
#不能在数据库条件下执行，要在命令行条件下执行
mysqldump -uroot -p1234 表名 > 文件.sql

#解锁，mysql环境下
unlock tables;

 缺点 

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

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

解决备份方案

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

#备份
mysqldump --single-transaction -uroot -p1234 表名 > 表名.sql

表级锁

介绍

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

分类

对于表级锁，主要可以分为以下三类

1.表锁

2.元数据锁(meta data lock, MDL)

3.意向锁

1.表锁

加了表锁的客户端，加锁后，开启一个事务，就等于解锁

语法

#加锁
lock tables 表名 read/write

#释放锁
unlock tables /客户端断开连接

1.表共享读锁(read lock)

2.表独占写锁(write lock)

2.元数据锁(meta data lock, MDL)

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

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)当对表结构进行变更操作的时候，加MDL写锁(排他)。

select... for update 的意思就是把查询出来的数据加上锁，等事务自动提交后锁才被释放。

 

简单来说就是有读写数据操作的事务未提交的情况下不能修改表结构（alter table）

3.意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

事务在获取行级 S 锁之前，必须获取其对应表的 IS 或 IX 锁
事务在获取行级 X 锁之前，必须获取其对应表的 IX 锁

不重要：另外测试发现意向锁和元数据锁的是同一种锁，理解为同时产生，同时消失，但是意向锁可能为空（元数据锁系统创建不可能为空）

1.意向锁不是行锁，他仅仅代表这个表里面的被加上了共享/排他锁，双重锁

2.DML语句和for update会给表加上意向锁，同时会给索引添加行锁

读读兼容，读写互斥，写写互斥

• 共享锁(读锁) S：对同一行的操作读不阻塞，阻塞写
• 排它锁(写锁) X：对同一行的操作读写都会阻塞
• 意向共享锁 IS：一个事物想要加S锁时必须先获得该表的IS
• 意向排它锁 IX：一个事物想要加X锁时必须先获得该表的IX
• 

 看IS,IX和S,X，判断意向锁和表锁的关系

简单理解可以看下面的

(20条消息) 意向锁到底是什么_延迟满足的博客-CSDN博客_意向锁

行级锁

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

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:

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

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

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

共享锁(读锁) S：对同一行的操作读不阻塞，阻塞写

排它锁(写锁) X：对同一行的操作读写都会阻塞

共享锁是避免排他锁的，只读防止修改

 

1.行锁

 默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

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

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

2.间隙锁

临键锁  = 行锁 + 间隙锁 

第一种情况

第二种情况

第三种情况

 

73. 进阶-锁-行级锁-间隙锁&临键锁1_哔哩哔哩_bilibili