浅析mysql的锁

最新推荐文章于 2024-08-06 15:20:26 发布

_翚_

最新推荐文章于 2024-08-06 15:20:26 发布

阅读量286

点赞数

分类专栏： mysql 文章标签： mysql 锁

本文链接：https://blog.csdn.net/juzhenxing/article/details/102765648

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySQL锁概述

不同的存储引擎支持不同的锁机制.

锁分为表级锁、页面锁和行级锁。
行级锁: 开销大，加锁慢; 会出现死锁; 锁定粒度最小，发生锁冲突的概率最低，并发度也最高。反之
仅从锁的角度来说:表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理(OLTP)系统。

On-Line Transaction Processing联机事务处理过程(OLTP)，也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。

存储引擎分类

MyISAM
只支持表锁, 支持全文索引, 不支持外键和事务. 适用于读多写少的场景, 比如 select count(1).
InnoDB
支持表锁和行锁, 支持外键和事务, 并支持类似于 Oracle 的非锁定读, 不支持全文索引, 但是可以借助sphinx插件实现. 适用于并发比较高的场景.
在此呼吁, 为了我们的身心健康, 请使用InnoDB.

MyISAM存储引擎

表锁

查询表级锁征用情况

show status like 'table%'

在这里插入图片描述

表锁兼容性：读共享，写独占
如何加表锁？
MyISAM在执行查询语句( SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁.

lock table film_text write;
unlock tables;
// 允许事务A查询时， 事务B插入
lock table film_text read local;
// 给表的别名也要加锁
lock table film_text as f read;

在这里插入图片描述

在用LOCKTABLES给表显式加表锁时，必须同时取得所有涉及表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表;同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。在自动加锁的情况下也是如此, MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁的原因。
并发插入
当concurrent insert 设置为0时，不允许并发插入。
当concurrent_ insert 设置为1时，如果MyISAM表中没有空洞(即表的中间没有被删除的行), MyISAM允许在一一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
当 concurrent_ insert 设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

// auto等价于1
show global variables like 'concurrent_insert'
// 整理空间碎片
OPTIMIZE table film_text

在这里插入图片描述

myisam的锁调度
默认写进程先获得锁，可能造成读进程的饥饿问题

// 通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
// 通过执行命令SET LOW_ PRIORITY_ UPDATES=1,使该连接发出的更新请求优先级降低。
// 通过指定 INSERT、UPDATE、 DELETE语句的LOW_ PRIORITY属性，降低该语句的优先级。
// 折中办法，调节max_write_lock_count参数，该值表示当处理了4294967295个写请求， 接下来会处理读请求
// 避免慢查询， 防止写进程饿死
show global variables like 'max_write_lock_count';

在这里插入图片描述

InnoDB存储引擎

背景知识

事务及其ACID属性

原子性, 通过回滚日志(undo log)来实现. undo log用于记录数据被修改前的信息, 然后在发生错误时才可以回滚。
隔离性, 通过lbcc(基于锁的并发控制)和mvcc(多版本并发控制)实现.
持久性, 通过重做日志(redo log)来实现. redo log是用来恢复数据的,用于保障已提交事务的持久化特性.
一致性, 终极目标. 通过原子性，持久性和隔离性来实现的

并发事务处理带来的问题

更新丢失, 分为回滚丢失和覆盖丢失. 回滚丢失, SQL92没有定义这种现象，标准定义的所有隔离界别都不允许第一类丢失更新发生。覆盖丢失, 可以通过悲观锁或者乐观锁来解决.
回滚丢失

覆盖丢失
脏读, 事务A更新了一条记录,还没提交,事务B就可以读取到变更后的记录.
不可重复读,在事务1内，读取了一个数据，事务1还没有结束时，事务2修改或删除了这个数据，并提交。紧接着，事务1又读这个数据。由于事务2的修改，那么事务1两次读到的的数据可能是不一样的.
幻读,指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。

事务隔离级别

防止更新丢失应该是应用的责任
读已提交的可以防止脏读, 会产生不可重复读和幻读
可重复读可以防止脏读和不可重复读, 会产生幻读(mysql不会)
序列化可以防止脏读, 不可重复度和幻读.
数据库的事务隔离越严格,并发副作用越小，但付出的代价也就越大
应用可以根据自己的业务逻辑要求,通过选择不同的隔离级别来平衡“隔离”与“并发”的矛盾。

获取InnoDB行锁争用情况

// 分析系统上的行锁的争夺情况
show STATUS like 'innodb_row_lock%'

// 通过查询information_schema数据库中相关的表来查看锁情况
select *
from information_schema.innodb_locks;

// 通过设置InnoDBMonitors来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。
// 默认情况下每15秒会向日志中记录监控的内容, 分析完记得关闭
create table innodb_monitor(a int)engine=innodb;

InnoDB的行锁模式及加锁方法

分类

行排他锁

是什么?
当一个事务对一条记录进行写操作时，会阻塞其他事务对同一行的读写操作，当该锁释放后，才会执行其他事务的读写操作。
测试用例:

CREATE TABLE `hang_suo_test` (
  `id` tinyint(3) unsigned NOT NULL DEFAULT '0' PRIMARY KEY,
  `name` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 事务A
insert into hang_suo_test(id, name) values(1, 'name1');
update hang_suo_test set name = 'name11' where id = 1;

# 事务B
update hang_suo_test set name = 'name111' where id = 1;
update hang_suo_test set name = 'name22' where id = 2;

# 显示加排他锁, 其他事物可以查询不能加锁
SELECT *
from film_text
where film_id = 1 for UPDATE

# 显示加共享锁
SELECT *
from film_text
where film_id = 1 lock in share MODE

行共享锁

是什么?
当一个事务读取一条记录的时候，不会阻塞其他事务对同一记录的读请求，但会阻塞对其的写请求。当读锁释放后，才会执行其他事务的写操作。
为什么?
用SELECT … IN SHARE MODE获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

InnoDB 行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。

InnoDB行锁分为3种情形。

Record lock: 对索引项加锁。
Gap lock: 对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。(一般存在于可重复读级别, 读已提交的只有用于外键约束检查和重复键检查时会存在)
Next-key lock: 前两种的组合，对记录及其前面的间隙加锁

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样!
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的, 警惕隐式转换

临键锁

是什么?
当使用范围条件索引数据时，InnoDB会对符合条件的数据索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这就是间隙锁.
为什么?
一方面是为了防止幻读，以满足相关隔离级别的要求;另一方面，是为了满足其恢复和复制的需要。
如何判断?
加锁的基本单位是Next-key lock(临键锁);
查找过程中访问到的对象才会加锁;
索引上的等值查询，给唯一索引加锁的时候，Next-key lock退化成行锁;
索引上的等值查询，向右遍历时最后一个值不满足等值条件的时候，Next-key lock退化成间隙锁.
测试用例

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t2 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

# 事务A
update t2 set d = d + 1 where id = 7;
# 事务B
insert into t2 values(8,8,8); 
# 事务C
update t2 set d = d + 1 where id = 10;

# 根据规则1，加锁单位是Next-key lock，sessionA加锁范围是(5,10]；
# 根据规则4，这是一个等值查询(id=7)，且id=10不满足查询条件，next-key lock 退化成间隙锁，加锁范围变成(5,10)
# 所以事务B会被阻塞，事务C可以执行成功.

恢复和复制的需要，对InnoDB锁机制的影响

MySQL5.6支持3种日志格式，即基于语句的日志格式SBL、基于行的日志格式RBL和混合格式。
支持4种复制模式。

基于SQL语句的复制SBR:这也是MySQL最早支持的复制模式。
基于行数据的复制RBR:这是MySQL 5.1以后开始支持的复制模式，主要优点是支持对非安全SQL的复制。
混合复制模式:对安全的SQL语句采用基于SQL语句的复制模式，对于非安全的SQL语句采用居于行的复制模式。
使用全局事务ID (GTIDs)的复制:主要是解决主从自动同步一致问题。

对基于语句日志格式(SBL)的恢复和复制而言，由于MySQL的BINLOG是按照事务提交的先后顺序记录的，因此要正确恢复或复制数据，就必须满足:在一个事务未提交前，其他并发事务不能插人满足其锁定条件的任何记录，也就是不允许出现幻读。这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到Next-Key锁的原因，比如在用范围条件更新记录时，无论在Read Committed或是Repeatable Read隔离级别下，InnoDB都要使用Next-Key锁，但这并不是隔离级别要求的
设置系统变量innodb_locks_unsafe_for_binlog 的值为“on”后，InnoDB不再对source_tab加锁.
如果应用中一定要用这种SQL(into … select)来实现业务逻辑，又不希望对源表的并发更新产生影响，可以采取以下3种措施。

采取上面示例中的做法，将innodb_locks_unsafe_ for_binlog的值设置为“on”，强制MySQL使用多版本数据一致性读。但付出的代价是可能无法用BINLOG正确地恢复或复制数据，因此，不推荐使用这种方式。
通过使用“select * from source_tab … Into oufile”和"load data oufile”语句组合来间接实现，采用这种方式MySQL不会给source_tab加锁。
使用基于行的BINLOG格式和基于行数据的复制。

InnoDB在不同隔离级别下的一致性读及锁的差异

实际上,通过优化事务逻辑,大部分应用使用Read Committed隔离级别就足够了。对于一些确实需要更高隔离级别的事务，可以通过在程序中动态改变隔离级别的方式满足需求。

表锁

什么时候使用?

事务需要更新大部分或全部数据,表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

什么情况下会锁表?
多数是查询不走索引, 使用自增主键(与innodb_autoinc_lock_mode配置有关)和修改表结构等.
分类

意向共享锁(IS)
是什么?
事务打算加行共享锁的时候, 会先获取该表的意向共享锁.
为什么?
如果没有意向共享锁, 当某个事务想申请表级别写锁的时候, 表级别写锁和行读锁互斥, 那么它需要遍历表中的所有记录才能确定是否可以加表级别写锁.
怎么做?
意向共享锁和意向排他锁是数据库主动加的, 不需要我们手动处理.
意向排他锁(IX)
是什么?
事务打算加行排他锁的时候, 会先获取该表的意向排他锁.
为什么? 同理
怎么做?
对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给数据集加排他锁。
测试用例:

CREATE TABLE `biao_suo_test` (
  `id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `name` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 事务A
update biao_suo_test set name = 'name4'where id = 4;
# 事务B
update biao_suo_test set name = 'name4'where id = 5;

元数据锁
是什么?
当对一个表进行增删改查的时候, 会对表加上MDL读锁; 当对一个表的表结构进行修改的时候, 会对表加上MDL写锁.
为什么?
为了保证在并发条件下, DDL(数据定义语句)和DML(Data Manipulation Language数据操作语句-增删改语句)操作的一致性.
自增锁
注意事项有哪些?
插入新行时, 如果id指定为0、null或者未指定值时，那么就把当前的AUTO_INCREMENT值当作新自增值，如果指定了具体值则使用具体值；
在MySQL5.7之前的版本AUTO_INCREMENT=2是保存在内存中的，每次重启后在第一次打开表的时候会去计算这个表的最大id, 而在MySQL8的版本将自增id保存在了redolog里面，就有了持久性.
为什么自增主键会不连续?
因为事务的回滚, 不会将自增主键值回滚.

在InnoDB下，使用表锁要注意以下两点。

使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB 存储引擎层管理的，而是由其上一层的MySQL Server 负责的，仅当autocommit=0、innodb_ table_locks=1(默认设置)时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁;否则，InnoDB 将无法自动检测并处理这种死锁。
在用LOCK TABLES对InnoDB表加锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。

关于死锁

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法。

在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
前面讲过，在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插人一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插人成功，另一个线程会出现锁等待，当第Ⅰ个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个共享锁!这时如果有第3个线程又来申请排他锁，会等待，此时第二个线程来申请排它锁会出现死锁！！！

尽管通过上面介绍的设计和SQL优化等措施，可以大大减少死锁，但死锁很难完全避免。因此，在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。
如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。

小技巧

遇到死锁如何解决?

通过多终端模拟并发事务，复现死锁；
通过show engine innodb status; 可以查看事务与锁的信息；
通过explain可以查看执行计划.

如何避免死锁?

给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁;
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;
改变事务隔离级别;
innodb_lock_wait_timeout这个参数可以控制死锁等待超时的时间，默认值为50s.
尽量控制事务的大小，减少锁定的资源量和锁定时间。
尽量使用较低级别的事务隔离，减少MySQL因为事务隔离带来的成本。
精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会;
尽量用相等条件访问数据，这样可以避免Next-Key锁对并发插入的影响;
不要申请超过实际需要的锁级别;除非必须，查询时不要显示加锁;
一些特定的事务，可以使用表锁来提高处理速度或减少发生死锁的几率。

_翚_

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
浅析mysql的锁

存储引擎分类MyISAM只支持表锁, 支持全文索引, 不支持外键和事务. 适用于读多写少的场景, 比如 select count(1).InnoDB支持表锁和行锁, 支持外键和事务, 并支持类似于 Oracle 的非锁定读, 不支持全文索引, 但是可以借助sphinx插件实现. 适用于并发比较高的场景.在此呼吁, 为了我们的身心健康, 请使用InnoDB.InnoDB存储引擎表锁...
复制链接

扫一扫

专栏目录