【数据库】 InnoDB的锁机制

最新推荐文章于 2024-08-11 13:37:35 发布
最新推荐文章于 2024-08-11 13:37:35 发布
阅读量201 收藏
点赞数
分类专栏： MySQL

本文内容：

  • Innodb锁机制
回顾

数据库使用锁是为了支持更好的并发，提供数据的完整性和一致性。InnoDB是一个支持行锁的存储引擎，锁的类型有：共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）。为了提供更好的并发，InnoDB提供了非锁定读：不需要等待访问行上的锁释放，读取行的一个快照。该方法是通过InnoDB的一个特性：MVCC来实现的。

Innodb 的行锁是在有索引的情况下

锁算法

InnoDB有三种行锁的算法：

1、Record Lock：单个行记录上的锁。

2、Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

3、Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身 对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

Record Lock 总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置索引，那么这时会使用隐式的主键来进行锁定。

测试一：默认RR隔离级别
root@localhost : test 10:56:10>create table t(a int,key idx_a(a))engine =innodb;
Query OK, 0 rows affected (0.20 sec)

root@localhost : test 10:56:13>insert into t values(1),(3),(5),(8),(11);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@localhost : test 10:56:15>select * from t;
+------+
| a    |
+------+
|    1 |
|    3 |
|    5 |
|    8 |
|   11 |
+------+
rows in set (0.00 sec)

section A:

root@localhost : test 10:56:27>start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 10:56:29>select * from t where a = 8 for update;
+------+
| a    |
+------+
|    8 |
+------+
row in set (0.00 sec)


section B:
root@localhost : test 10:54:50>begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 10:56:51>select * from t;
+------+
| a    |
+------+
|    1 |
|    3 |
|    5 |
|    8 |
|   11 |
+------+
rows in set (0.00 sec)

root@localhost : test 10:56:54>insert into t values(2);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 10:57:01>insert into t values(4);
Query OK, 1 row affected (0.00 sec)

++++++++++
root@localhost : test 10:57:04>insert into t values(5);

root@localhost : test 10:57:11>insert into t values(7);

root@localhost : test 10:57:15>insert into t values(9);

root@localhost : test 10:57:33>insert into t values(10);
++++++++++
上面全被锁住，阻塞住了

root@localhost : test 10:57:39>insert into t values(11);
Query OK, 1 row affected (0.00 sec)

分析：

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围（GAP）。上面索引值有1，3，5，8，11，其记录的GAP的区间如下：是一个左开右闭的空间（原因是默认主键的有序自增的特性，结合后面的例子说明）

（-∞,1]，(1,3]，(3,5]，(5,8]，(8,11]，(11,+∞）

该SQL语句锁定的范围是（5,8]，下个键值范围是（8,11]，所以插入5~11之间的值的时候都会被锁定，要求等待。即：插入5，6，7，8，9，10 会被锁住。插入非这个范围内的值都正常。

因为例子里没有主键，所以要用隐藏的ROWID来代替，数据根据Rowid进行排序。而Rowid是有一定顺序的（自增），所以其中11可以被写入，5不能被写入

测试一：默认RR隔离级别（有主键的情况，但操作并没有使用主键索引，仍使用Next-Key Lock）（可与最后一个测试对比）
会话1：
01:43:07>create table t(id int,name varchar(10),key idx_id(id),primary key(name))engine =innodb;
Query OK, 0 rows affected (0.02 sec)

01:43:11>insert into t values(1,'a'),(3,'c'),(5,'e'),(8,'g'),(11,'j');                                                                               
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

01:44:03>select @@global.tx_isolation, @@tx_isolation;                                                                                                 +-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
row in set (0.01 sec)

01:44:58>select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | c    |
|    5 | e    |
|    8 | g    |
|   11 | j    |
+------+------+
rows in set (0.00 sec)

01:45:07>start transaction;              

01:45:09>delete from t where id=8;
Query OK, 1 row affected (0.01 sec)


会话2：
01:50:38>select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
row in set (0.01 sec)

01:50:48>start transaction; 

01:50:51>select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | c    |
|    5 | e    |
|    8 | g    |
|   11 | j    |
+------+------+
rows in set (0.01 sec)

01:51:35>insert into t(id,name) values(6,'f');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

01:53:32>insert into t(id,name) values(5,'e1');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

01:53:41>insert into t(id,name) values(7,'h');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

01:54:43>insert into t(id,name) values(8,'gg');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

01:55:10>insert into t(id,name) values(9,'k');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

01:55:23>insert into t(id,name) values(10,'p');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

01:55:33>insert into t(id,name) values(11,'iz');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

#########上面看到 id：5，6，7，8，9，10，11都被锁了。

#########下面看到 id：5，11 还是可以插入的
01:54:33>insert into t(id,name) values(5,'cz');
Query OK, 1 row affected (0.01 sec)

01:55:59>insert into t(id,name) values(11,'ja');
Query OK, 1 row affected (0.01 sec)

分析：因为会话1已经对id=8的记录加了一个X锁，由于是RR隔离级别，INNODB要防止幻读需要加GAP锁：即id=5（8的左边），id=11（8的右边）之间需要加间隙锁（GAP）。这样[5,e]和[8,g]，[8,g]和[11,j]之间的数据都要被锁。上面测试已经验证了这一点，根据索引的有序性，数据按照主键（name）排序，后面写入的[5,cz]（[5,e]的左边）和[11,ja]（[11,j]的右边）不属于上面的范围从而可以写入。

继续：插入超时失败后，会怎么样？

超时时间的参数：innodb_lock_wait_timeout ，默认是50秒。
超时是否回滚参数：innodb_rollback_on_timeout 默认是OFF。

section A:

root@localhost : test 04:48:51>start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 04:48:53>select * from t where a = 8 for update;
+------+
| a    |
+------+
|    8 |
+------+
row in set (0.01 sec)


section B:

root@localhost : test 04:49:04>start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 04:49:07>insert into t values(12);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 04:49:13>insert into t values(10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : test 04:50:06>select * from t;
+------+
| a    |
+------+
|    1 |
|    3 |
|    5 |
|    8 |
|   11 |
|   12 |
+------+
rows in set (0.00 sec)

经过测试，不会回滚超时引发的异常，当参数innodb_rollback_on_timeout 设置成ON时，则可以回滚，会把插进去的12回滚掉。

默认情况下，InnoDB存储引擎不会回滚超时引发的异常，除死锁外。

测试二：当查询的索引有唯一属性
root@localhost : test 04:58:49>create table t(a int primary key)engine =innodb;
Query OK, 0 rows affected (0.19 sec)

root@localhost : test 04:59:02>insert into t values(1),(3),(5),(8),(11);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@localhost : test 04:59:10>select * from t;
+----+
| a  |
+----+
|  1 |
|  3 |
|  5 |
|  8 |
| 11 |
+----+
rows in set (0.00 sec)

section A:

root@localhost : test 04:59:30>start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 04:59:33>select * from t where a = 8 for update;
+---+
| a |
+---+
| 8 |
+---+
row in set (0.00 sec)

section B:

root@localhost : test 04:58:41>start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 04:59:45>insert into t values(6);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 05:00:05>insert into t values(7);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 05:00:08>insert into t values(9);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 05:00:10>insert into t values(10);
Query OK, 1 row affected (0.00 sec)

问题：

为什么section B上面的插入语句可以正常，和测试一不一样？

分析：

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围，按照这个方法是会和第一次测试结果一样。但是，当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。（亲测，使用了组合索引，其中包含主键索引，也会降级）

注意：通过主键或者唯一索引来锁定不存在的值，也会产生GAP锁定。

关闭gap lock

1：把事务隔离级别改成：Read Committed
2：修改参数：innodb_locks_unsafe_for_binlog 设置为1。

qqqqq1993qqqqq
关注
专栏目录
mysql事务与锁机制（存储引擎和锁、MyISAM锁机制、InnoDB锁机制、Next-Key锁、Dead-Lock）.docx
07-24
#### 三、InnoDB锁机制 **1、事务基础概念** 事务是数据库操作的基本单位，确保数据的一致性和完整性。InnoDB支持ACID特性（原子性、一致性、隔离性、持久性），并通过多种锁机制来实现这些特性。 **2、锁机制...
解析数据库锁协议和InnoDB的锁机制(全面解析行级锁、表级锁、排他锁、共享锁、悲观锁、乐观锁等常用锁)
12-14
数据库通过锁以及锁协议来进行并发控制，解决并发事务带来的问题，本篇博文主要是解析数据库的锁协议和Mysql的默认存储引擎InnoDB的锁机制。 如果对事务隔离级别以及并发事务带来的问题不熟悉可以翻阅我的另外一篇...
数据库-----InnoDB锁机制
qq_34266804的博客
01-17 148
锁机制是事务实现不同的隔离级别所必需的。 1.共享锁 共享锁，又称为读锁，可以查看但无法修改和删除的一种数据锁。 共享锁的锁粒度是行或者元组（多个行）。 2.排他锁         排他锁又称为写锁((eXclusive lock,简记为X锁))，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上...
MySQL-高级-12 mysql锁
weixin_45483328的博客
05-10 1140
表锁、行锁、页锁
Mysql InnoDB 的锁机制
凡夫编程
11-24 1151
处理数据并发问题时，绝不能想当然，搞清楚数据库是什么版本存储引擎？事务隔离级别是什么？读取操作加锁方式是什么？这些信息搞清楚，才是分析并发问题的第一步。
MySQL数据库InnoDB锁
weixin_46831212的博客
05-28 860
前言 当我们在讨论MySQL数据库InnoDB的锁时，总会想到表锁、行锁、悲观锁、乐观锁等等。如果没有进行深入了解，我们很难知道它们之间关系是什么，有什么作用。本文主要是对InnoDB的锁之间关系和作用进行讲解。 一、锁是什么？ 当并发访问数据库时有可能会导致用户修改和读取数据不一致性问题，为了解决这些问题，目前InnoDB存储引擎提供了两种技术解决方案：多版本控制（MVCC）和基于锁的并发控制（LBCC）。 二、使用步骤 1.引入库 代码如下（示例）： import numpy as np import
数据库中的锁机制
Android系统优化
10-15 3792
二、MySQL大致可归纳为以下四种锁： 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高。 页面锁：开锁和加锁时间界于表锁和行锁之间，会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。 间隙锁：锁住满足索引条件检索的区间，其他事务插入该区间内的数据时会失败，解决了幻读的问题。 MyISAM存储引擎：表级锁 InnoDB存储引擎：既支持行级锁...
InnoDB锁机制
z3551906947的博客
08-04 723
定义：锁是一种同步机制，用来控制对共享资源的并发访问，防止数据竞争和不一致。作用：确保在某一时刻，只有一个事务能够修改数据，维护数据的完整性和一致性。死锁Deadlock：指两个或多个事务在执行过程中，因争夺资源而造成的一种僵局，这些事务相互等待对方持有的资源，导致无法继续执行。
数据库锁机制
swadian2008的博客
04-04 4085
目录 1、表锁和行锁 2、间隙锁(Gap Lock)和临键锁(Next-key Locks) 3、锁机制分析 数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。 锁是计算机协调多个进程或线程并发访问某一资源的机制。 在数据库中，除了传统的计
MySQL InnoDB锁机制分享
Leeon的博客
04-01 1575
写在前面：在设计新零售供应链wms（仓库管理系统）库存模块时，为了防止并发情况对库存的影响，查阅了一些资料，对InnoDB锁机制有了更全面的了解，在此做出分享，如有疏漏望不吝指正，愿共同进步！（此篇为1.0版本，后续随理解深入，会逐步迭代完善~） 博客搬家：https://segmentfault.com/a/1190000014133576 一、为什么要加锁 锁机制用于管理对共享...
MySQL InnoDB锁机制深度剖析（乐观锁、悲观锁、共享锁、排它锁、行锁、表 锁、死锁）
qq_51263863的博客
08-11 1053
乐观锁适用于读多写少的场景，可以提高并发性能，但需要在应用层处理冲突。悲观锁适用于写多读少的场景，可以确保数据一致性，但可能影响并发性能。共享锁和排他锁是InnoDB实现并发控制的基础，理解它们有助于我们选择合适的查询方式。行锁提供了细粒度的并发控制，但要注意索引对行锁的影响。表锁在某些情况下是必要的，但应该谨慎使用，因为它会显著降低并发性。死锁是并发系统中的常见问题，需要通过合理的锁策略和事务设计来避免。间隙锁解决了幻读问题，但也可能导致某些操作被阻塞，需要在隔离性和并发性之间权衡。意向锁。
MySQL(三)：详解Innodb锁机制
健身变秃，coding变强
06-22 2603
1、前言 可以通过检查Innodb_row_lock状态变量来分析行锁的争夺情况： mysql> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 |
数据库中锁机制的学习
03-23
数据库中的锁机制是确保数据一致性、并发控制和事务隔离的重要机制。在多用户环境中，当多个用户同时访问和操作同一份数据时，可能会引发数据冲突和不一致。为了解决这些问题，数据库系统引入了锁来协调并发操作。...
基于Java的每日乐享新闻后台管理系统源码
10-04
本项目是一款基于Java技术的每日乐享新闻后台管理系统源码，总计包含172个文件，涵盖59个XML配置文件、28个Java源文件、26个JavaScript文件、16个CSS样式文件，以及少量其他资源文件。系统主要分为用户管理、稿件管理和系统管理三大功能模块，旨在对新闻系统中的新闻内容进行高效的数据维护。
小虎音乐-微信小程序.zip
10-04
小虎音乐-微信小程序
厦门华厦学院在辽宁2020-2024各专业最低录取分数及位次表.pdf
10-04
那些年，与你同分同位次的同学都去了哪里？全国各大学在辽宁2020-2024年各专业最低录取分数及录取位次数据，高考志愿必备参考数据
PytorchBert-LSTM-CRF命名实体识别源码+笔记+视频讲解PytorchBert-LSTM-CRF命名实体识别
最新发布
10-04
PytorchBert-LSTM-CRF命名实体识别源码+笔记+视频讲解PytorchBert-LSTM-CRF命名实体识别
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值