文档章节

InnoDB Lock Modes

秋风醉了
 秋风醉了
发布于 2015/11/13 19:39
字数 909
阅读 149
收藏 0

3 月,跳不动了?>>>

 InnoDB Lock Modes

http://dev.mysql.com/doc/refman/5.7/en/innodb-lock-modes.html

InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks. 

For information about record, gap, and next-key lock types, see Section 14.2.2.4, “InnoDB Record, Gap, and Next-Key Locks”.

A shared (S) lock permits the transaction that holds the lock to read a row.

An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:

  • A request by T2 for an S lock can be granted(准许同意) immediately. As a result, both T1 and T2 hold an S lock on r.

  • A request by T2 for an X lock cannot be granted immediately.

If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.


Intention Locks

Additionally, InnoDB supports multiple granularity(间隔尺寸,粒度) locking which permits coexistence(共存,并立) of record locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table locks in InnoDB that indicate which type of lock (shared or exclusive) a transaction will require later for a row in that table. There are two types of intention locks used in InnoDB (assume that transaction T has requested a lock of the indicated type on table t):

Intention shared (IS): Transaction T intends to set S locks on individual rows in table t.

Intention exclusive (IX): Transaction T intends to set X locks on those rows.

For example, SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IX lock.


The intention locking protocol is as follows:

Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t.

Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.

These rules can be conveniently summarized by means of the following lock type compatibility matrix(相容性矩阵).


X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

A lock is granted to a requesting transaction if it is compatible(兼容的,相容的) with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.


Thus, intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.


Deadlock Example

The following example illustrates how an error can occur when a lock request would cause a deadlock. The example involves two clients, A and B.

First, client A creates a table containing one row, and then begins a transaction. Within the transaction, A obtains an S lock on the row by selecting it in share mode:

CREATE TABLE t (i INT) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1);

client A,

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Next, client B begins a transaction and attempts to delete the row from the table:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

The delete operation requires an X lock. The lock cannot be granted because it is incompatible with the S lock that client A holds, so the request goes on the queue of lock requests for the row and client B blocks.

Finally, client A also attempts to delete the row from the table:

mysql> DELETE FROM t WHERE i = 1;
Query OK, 1 row affected (0.00 sec)

while client B,

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlock occurs here because client A needs an X lock to delete the row. However, that lock request cannot be granted because client B already has a request for an X lock and is waiting for client A to release its S lock. Nor can the S lock held by A be upgraded(升级的,提升的) to an X lock because of the prior(优先的; 占先的; 在…之前) request by B for an X lock. As a result, InnoDB generates an error for one of the clients and releases its locks. The client returns this error:

ERROR 1213 (40001): Deadlock found when trying to get lock;try restarting transaction

At that point, the lock request for the other client can be granted and it deletes the row from the table.

==============END==============

© 著作权归作者所有

秋风醉了
粉丝 253
博文 532
码字总数 405557
作品 0
朝阳
程序员
私信 提问
加载中

评论(0)

InnoDB Record, Gap, and Next-Key Locks

InnoDB Record, Gap, and Next-Key Locks http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html InnoDB has several types of record-level locks including record lock......

秋风醉了
2015/11/13
103
0
MySQL 5.6.17/5.5.37 发布

MySQL产品线更新.5.6.17/5.5.37 2014-03-27 之前版本2013-01-31的5.6.16/5.5.36主要是Bug修正。5.1还是5.1.73。 完全改进: MySQL 5.6.17 改进记录 (2014-03-27) 新特性和各种改进 Incompat...

fei
2014/03/28
5.6K
6
mysql参数Innodb_row_lock_current_waits异常

数据库巡检时遇到Innodb_row_lock_current_waits参数值异常情况,如下: mysql> show status like "Innodb_row_lock%"; +-------------------------------+----------------------+ | Varia......

fofo成长记
2017/02/28
1.8K
0
MySQL 5.5 InnoDB 锁状态解析

目录 目录 一 引子 二 表结构说明 三 INNODB 锁等待模拟 3.1 创建测试表,录入测试数据 3.2 模拟锁等待 3.3 再次模拟锁等待 3.4 查询锁等待 3.4.1 直接查看 innodblockwaits 表 3.4.2 innodb...

java_龙
2018/11/30
51
0
mysql 启动错误日志,导致网站缓慢

InnoDB: The InnoDB memory heap is disabled InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Mutexes and rw_locks use GCC a......

rianbow
2018/09/21
248
1

没有更多内容

加载失败,请刷新页面

加载更多

mapbox

Mapbox是一个可以跨行业使用的开发平台,我们可以利用它对地图进行创建和定制,以解决地图、数据和空间分析等问题。 Leaflet 轻量 WebGIS 前端类库 Leaflet 是一个为建设移动设备友好的互动地...

东东笔记
19分钟前
32
0
看你有多色游戏案例

看你有多色游戏案例 游戏规则 这款游戏的玩法就是找出所有风格中颜色比较淡的,随着游戏进行后面的方块会越来越多, 这个游戏主要是考验玩家的眼力和注意力, 游戏截图 部分代码 mian.html ...

板栗z丶
48分钟前
23
0
【SpringBoot MQ 系列】RabbitListener 消费基本使用姿势介绍

【MQ 系列】RabbitListener 消费基本使用姿势介绍 之前介绍了 rabbitmq 的消息发送姿势,既然有发送,当然就得有消费者,在 SpringBoot 环境下,消费可以说比较简单了,借助@RabbitListener...

小灰灰Blog
49分钟前
29
0
罗永浩回应做主播带货赚钱还债:主播赚的不是脏钱

  罗永浩的抖音带货生涯,将于 4 月 1 日晚 20 点开始。   虽然老罗对自己带货能力,信心满满,但也有粉丝对其表达了对他直播带货感到失望。   今日,罗永浩发布微博回应称, 不应该感...

水果黄瓜
50分钟前
18
0
二维码传输文件

这是我大概在5,6年前写的东西,当时种种原因,删除了,现在有空闲时间,补发一下。 二维码是现在非常常用的一种信息传播载体,通过智能手机,可以方便快捷的传输小容量信息,是否可以通过二...

豆豆爹地
58分钟前
20
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部