autocommit 隔离级别 next lock gap lock 事务隔离级别和锁
实践:
1、
SHOW VARIABLES LIKE "%transaction%";
transaction_isolation REPEATABLE-READ
小结:
autocommit 隔离级别
MySQL 事务隔离级别和锁
事务特性和 InnoDB 锁
事务及其特性
数据库事务(简称:事务)是数据库管理系统执行过程中的一个逻辑单位,由一个有限的数据库操作序列构成。事务的使用是数据库管理系统区别文件系统的重要特征之一。
事务拥有四个重要的特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability),人们习惯称之为 ACID 特性。下面我逐一对其进行解释。
- 原子性(Atomicity)
事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。例如,如果一个事务需要新增 100 条记录,但是在新增了 10 条记录之后就失败了,那么数据库将回滚对这 10 条新增的记录。也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位。
- 一致性(Consistency)
指事务将数据库从一种状态转变为另一种一致的的状态。事务开始前和结束后,数据库的完整性约束没有被破坏。例如工号带有唯一属性,如果经过一个修改工号的事务后,工号变的非唯一了,则表明一致性遭到了破坏。
- 隔离性(Isolation)
要求每个读写事务的对象对其他事务的操作对象能互相分离,即该事务提交前对其他事务不可见。 也可以理解为多个事务并发访问时,事务之间是隔离的,一个事务不应该影响其它事务运行效果。这指的是在并发环境中,当不同的事务同时操纵相同的数据时,每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。例如一个用户在更新自己的个人信息的同时,是不能看到系统管理员也在更新该用户的个人信息(此时更新事务还未提交)。
注:MySQL 通过锁机制来保证事务的隔离性。
- 持久性(Durability)
事务一旦提交,则其结果就是永久性的。即使发生宕机的故障,数据库也能将数据恢复,也就是说事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。这只是从事务本身的角度来保证,排除 RDBMS(关系型数据库管理系统,例如 Oracle、MySQL 等)本身发生的故障。
注:MySQL 使用
redo log
来保证事务的持久性。
事务的隔离级别
SQL 标准定义的四种隔离级别被 ANSI(美国国家标准学会)和 ISO/IEC(国际标准)采用,每种级别对事务的处理能力会有不同程度的影响。
我们分别对四种隔离级别从并发程度由高到低进行描述,并用代码进行演示,数据库环境为 MySQL 5.7。
READ UNCOMMITTED(读未提交)
该隔离级别的事务会读到其它未提交事务的数据,此现象也称之为脏读。
- 准备两个终端,在此命名为 mysql 终端 1 和 mysql 终端 2,再准备一张测试表
test
,写入一条测试数据并调整隔离级别为READ UNCOMMITTED
,任意一个终端执行即可。12345SET @@session.transaction_isolation = 'READ-UNCOMMITTED';
create database test;
use test;
create table test(id int primary key);
insert into test(id) values(1);
- 登录 mysql 终端 1,开启一个事务,将 ID 为
1
的记录更新为2
。123begin;
update test set id = 2 where id = 1;
select * from test; -- 此时看到一条ID为2的记录
- 登录 mysql 终端 2,开启一个事务后查看表中的数据。
123
use test;
begin;
select * from test; -- 此时看到一条 ID 为 2 的记录
最后一步读取到了 mysql 终端 1 中未提交的事务(没有 commit 提交动作),即产生了脏读,大部分业务场景都不允许脏读出现,但是此隔离级别下数据库的并发是最好的。
READ COMMITTED(读提交)
一个事务可以读取另一个已提交的事务,多次读取会造成不一样的结果,此现象称为不可重复读问题,Oracle 和 SQL Server 的默认隔离级别。
- 准备两个终端,在此命名为 mysql 终端 1 和 mysql 终端 2,再准备一张测试表
test
,写入一条测试数据并调整隔离级别为READ COMMITTED
,任意一个终端执行即可。12345SET @@session.transaction_isolation = 'READ-COMMITTED';
create database test;
use test;
create table test(id int primary key);
insert into test(id) values(1);
- 登录 mysql 终端 1,开启一个事务,将 ID 为
1
的记录更新为2
,并确认记录数变更过来。123begin;
update test set id = 2 where id = 1;
select * from test; -- 此时看到一条记录为 2
- 登录 mysql 终端 2,开启一个事务后,查看表中的数据。
123
use test;
begin;
select * from test; -- 此时看一条 ID 为 1 的记录
- 登录 mysql 终端 1,提交事务。
1
commit;
- 切换到 mysql 终端 2。
1
select * from test; -- 此时看到一条 ID 为 2 的记录
mysql 终端 2 在开启了一个事务之后,在第一次读取 test
表(此时 mysql 终端 1 的事务还未提交)时 ID 为 1
,在第二次读取 test
表(此时 mysql 终端 1 的事务已经提交)时 ID 已经变为 2
,说明在此隔离级别下已经读取到已提交的事务。
REPEATABLE READ(可重复读)
该隔离级别是 MySQL 默认的隔离级别,在同一个事务里,select
的结果是事务开始时时间点的状态,因此,同样的 select
操作读到的结果会是一致的,但是,会有幻读现象。MySQL 的 InnoDB 引擎可以通过 next-key locks
机制(参考下文"行锁的算法"一节)来避免幻读。
- 准备两个终端,在此命名为 mysql 终端 1 和 mysql 终端 2,准备一张测试表 test 并调整隔离级别为
REPEATABLE READ
,任意一个终端执行即可。1234SET @@session.transaction_isolation = 'REPEATABLE-READ';
create database test;
use test;
create table test(id int primary key,name varchar(20));
- 登录 mysql 终端 1,开启一个事务。
12
begin;
select * from test; -- 无记录
- 登录 mysql 终端 2,开启一个事务。
12
begin;
select * from test; -- 无记录
- 切换到 mysql 终端 1,增加一条记录并提交。
12
insert into test(id,name) values(1,'a');
commit;
- 切换到 msyql 终端 2。
1
select * from test; --此时查询还是无记录
通过这一步可以证明,在该隔离级别下已经读取不到别的已提交的事务,如果想看到 mysql 终端 1 提交的事务,在 mysql 终端 2 将当前事务提交后再次查询就可以读取到 mysql 终端 1 提交的事务。我们接着实验,看看在该隔离级别下是否会存在别的问题。
- 此时接着在 mysql 终端 2 插入一条数据。
1
insert into test(id,name) values(1,'b'); -- 此时报主键冲突的错误
也许到这里您心里可能会有疑问,明明在第 5 步没有数据,为什么在这里会报错呢?其实这就是该隔离级别下可能产生的问题,MySQL 称之为幻读。注意我在这里强调的是 MySQL 数据库,Oracle 数据库对于幻读的定义可能有所不同。
SERIALIZABLE(序列化)
在该隔离级别下事务都是串行顺序执行的,MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁,从而避免了脏读、不可重读复读和幻读问题。
- 准备两个终端,在此命名为 mysql 终端 1 和 mysql 终端 2,分别登入 mysql,准备一张测试表 test 并调整隔离级别为
SERIALIZABLE
,任意一个终端执行即可。1234SET @@session.transaction_isolation = 'SERIALIZABLE';
create database test;
use test;
create table test(id int primary key);
- 登录 mysql 终端 1,开启一个事务,并写入一条数据。
12
begin;
insert into test(id) values(1);
- 登录 mysql 终端 2,开启一个事务。
12
begin;
select * from test; -- 此时会一直卡住
- 立马切换到 mysql 终端 1,提交事务。
1
commit;
一旦事务提交,msyql 终端 2 会立马返回 ID 为 1 的记录,否则会一直卡住,直到超时,其中超时参数是由 innodb_lock_wait_timeout
控制。由于每条 select
语句都会加锁,所以该隔离级别的数据库并发能力最弱,但是有些资料表明该结论也不一定对,如果感兴趣,您可以自行做个压力测试。
表 1 总结了各个隔离级别下产生的一些问题。
表 1. 各个隔离级别下产生的一些问题
MySQL 中的锁
锁也是数据库管理系统区别文件系统的重要特征之一。锁机制使得在对数据库进行并发访问时,可以保障数据的完整性和一致性。对于锁的实现,各个数据库厂商的实现方法都会有所不同。本文讨论 MySQL 中的 InnoDB 引擎的锁。
锁的类型
InnoDB 实现了两种类型的行级锁:
- 共享锁(也称为 S 锁):允许事务读取一行数据。
可以使用 SQL 语句
select * from tableName where … lock in share mode;
手动加 S 锁。 - 独占锁(也称为 X 锁):允许事务删除或更新一行数据。
可以使用 SQL 语句
select * from tableName where … for update
; 手动加 X 锁。
S 锁和 S 锁是兼容的,X 锁和其它锁都不兼容,举个例子,事务 T1 获取了一个行 r1 的 S 锁,另外事务 T2 可以立即获得行 r1 的 S 锁,此时 T1 和 T2 共同获得行 r1 的 S 锁,此种情况称为锁兼容,但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁,则必须等待 T1 对行 r 锁的释放,此种情况也成为锁冲突。
为了实现多粒度的锁机制,InnoDB 还有两种内部使用的意向锁,由 InnoDB 自动添加,且都是表级别的锁。
- 意向共享锁(IS):事务即将给表中的各个行设置共享锁,事务给数据行加 S 锁前必须获得该表的 IS 锁。
- 意向排他锁(IX):事务即将给表中的各个行设置排他锁,事务给数据行加 X 锁前必须获得该表 IX 锁。
意向锁的主要目的是为了使得行锁和表锁共存。表 2 列出了行级锁和表级意向锁的兼容性。
表 2. 行级锁和表级意向锁的兼容性
行锁的算法
InnoDB 存储引擎使用三种行锁的算法用来满足相关事务隔离级别的要求。
- Record Locks
该锁为索引记录上的锁,如果表中没有定义索引,InnoDB 会默认为该表创建一个隐藏的聚簇索引,并使用该索引锁定记录。
- Gap Locks
该锁会锁定一个范围,但是不括记录本身。可以通过修改隔离级别为
READ COMMITTED
或者配置innodb_locks_unsafe_for_binlog
参数为ON
。 - Next-key Locks
该锁就是 Record Locks 和 Gap Locks 的组合,即锁定一个范围并且锁定该记录本身。InnoDB 使用 Next-key Locks 解决幻读问题。需要注意的是,如果索引有唯一属性,则 InnnoDB 会自动将 Next-key Locks 降级为 Record Locks。举个例子,如果一个索引有 1, 3, 5 三个值,则该索引锁定的区间为
(-∞,1], (1,3], (3,5], (5,+ ∞)
。
死锁
死锁是指两个或两个以上的进程在执行过程中,由于竞争资源或者由于彼此通信而造成的一种阻塞的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。
InnoDB 引擎采取的是 wait-for graph
等待图的方法来自动检测死锁,如果发现死锁会自动回滚一个事务。
下面我们通过一个示例来了解死锁。
- 准备两个终端,在此命名为 mysql 终端 1 和 mysql 终端 2,分别登入 mysql,再准备一张测试表
test
写入两条测试数据,并调整隔离级别为SERIALIZABLE
,任意一个终端执行即可。12345SET @@session.transaction_isolation = 'REPEATABLE-READ';
create database test;
use test;
create table test(id int primary key);
insert into test(id) values(1),(2);
- 登录 mysql 终端 1,开启一个事务,手动给 ID 为
1
的记录加 X 锁。12begin;
select * from test where id = 1 for update;
- 登录 mysql 终端 2,开启一个事务,手动给 ID 为
2
的记录加 X 锁。12begin;
select * from test where id = 2 for update;
- 切换到 mysql 终端 1,手动给 ID 为
2
的记录加 X 锁,此时会一直卡住,因为此时在等待第 3 步中 X 锁的释放,直到超时,超时时间由innodb_lock_wait_timeout
控制。1select * from test where id = 2 for update;
- 在锁超时前立刻切换到 mysql 终端 2,手动给 ID 为
1
的记录加 X 锁,此时又会等待第 2 步中 X 所的释放,两个终端都在等待资源的释放,所以 InnoDB 引擎会立马检测到死锁产生,自动回滚一个事务,以防止死锁一直占用资源。12select * from test where id = 1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
此时,通过
show engine innodb status\G
命令可以看到LATEST DETECTED DEADLOCK
相关信息,即表明有死锁发生;或者通过配置innodb_print_all_deadlocks
(MySQL 5.6.2 版本开始提供)参数为ON
将死锁相关信息打印到 MySQL 的错误日志。
锁的优化建议
锁如果利用不好,会给业务造成大量的卡顿现象,在了解了锁相关的一些知识点后,我们可以有意识的去避免锁带来的一些问题。
- 合理设计索引,让 InnoDB 在索引键上面加锁的时候尽可能准确,尽可能的缩小锁定范围,避免造成不必要的锁定而影响其他 Query 的执行。
- 尽可能减少基于范围的数据检索过滤条件,避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
- 尽量控制事务的大小,减少锁定的资源量和锁定时间长度。
- 在业务环境允许的情况下,尽量使用较低级别的事务隔离,以减少 MySQL 因为实现事务隔离级别所带来的附加成本。
结束语
通过阅读本文,可以让您对数据库的事务还有事务的隔离级别有个基本的了解,同时也介绍了 MySQL 中 InnoDB 引擎中一些锁相关的知识,从而可以让您利用关系型数据库系统设计一个更为健壮的业务模型。
https://baike.baidu.com/item/隔离级别
https://baike.baidu.com/item/幻读/153502?fr=aladdin
例如第一个事务对一个表中的数据进行了修改,比如这种修改涉及到表中的“全部数据行”。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入“一行新数据”。那么,以后就会发生操作第一个事务的用户发现表中还存在没有修改的数据行,就好象发生了幻觉一样.一般解决幻读的方法是增加范围锁RangeS,锁定检索范围为只读,这样就避免了幻读。
MySQL的InnoDB的幻读问题_jackbillow_新浪博客 http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html
事务是恢复和并发控制的基本单位
https://baike.baidu.com/item/数据库事务/9744607
事务控制语句:
-
BEGIN或START TRANSACTION;显式地开启一个事务;
-
COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改成为永久性的;
-
ROLLBACK;有可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
-
SAVEPOINT identifier;SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT;
-
RELEASE SAVEPOINT identifier;删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
-
ROLLBACK TO identifier;把事务回滚到标记点;
-
SET TRANSACTION;用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
MYSQL 事务处理主要有两种方法:
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表
Query OK, 0 rows affected (0.04 sec)
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
mysql> select * from runoob_transaction_test;
+------+
| id |
+------+
| 5 |
| 6 |
+------+
2 rows in set (0.01 sec)
mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
mysql> rollback; # 回滚
Query OK, 0 rows affected (0.00 sec)
mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入
+------+
| id |
+------+
| 5 |
| 6 |
+------+
2 rows in set (0.01 sec)
mysql>
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
This section describes lock types used by InnoDB
.
InnoDB
implements standard row-level locking where there are two types of locks, shared (S
) locks and exclusive (X
) 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 anS
lock can be granted immediately. As a result, bothT1
andT2
hold anS
lock onr
. -
A request by
T2
for anX
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
.
InnoDB
supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES ... WRITE
takes an exclusive lock (an X
lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB
uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:
-
An intention shared lock (
IS
) indicates that a transaction intends to set a shared lock on individual rows in a table. -
An intention exclusive lock (
IX
) indicates that a transaction intends to set an exclusive lock on individual rows in a table.
For example, SELECT ... FOR SHARE
sets an IS
lock, and SELECT ... FOR UPDATE
sets an IX
lock.
The intention locking protocol is as follows:
-
Before a transaction can acquire a shared lock on a row in a table, it must first acquire an
IS
lock or stronger on the table. -
Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an
IX
lock on the table.
Table-level lock type compatibility is summarized in the following 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.
Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE
). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.
Transaction data for an intention lock appears similar to the following in SHOW ENGINE INNODB STATUS
and InnoDB monitor output:
TABLE LOCK table `test`.`t` trx id 10080 lock mode IX
A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;
prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1
is 10
.
Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB
creates a hidden clustered index and uses this index for record locking. See Section 15.6.2.1, “Clustered and Secondary Indexes”.
Transaction data for a record lock appears similar to the following in SHOW ENGINE INNODB STATUS
and InnoDB monitor output:
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;
A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
prevents other transactions from inserting a value of 15
into column t.c1
, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.
A gap might span a single index value, multiple index values, or even be empty.
Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.
Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id
column has a unique index, the following statement uses only an index-record lock for the row having id
value 100 and it does not matter whether other sessions insert rows in the preceding gap:
SELECT * FROM child WHERE id = 100;
If id
is not indexed or has a nonunique index, the statement does lock the preceding gap.
It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.
Gap locks in InnoDB
are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.
Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED
. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.
There are also other effects of using the READ COMMITTED
isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE
condition. For UPDATE
statements, InnoDB
does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE
condition of the UPDATE
.
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
InnoDB
performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R
in an index, another session cannot insert a new index record in the gap immediately before R
in the index order.
Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.
By default, InnoDB
operates in REPEATABLE READ
transaction isolation level. In this case, InnoDB
uses next-key locks for searches and index scans, which prevents phantom rows (see Section 15.7.4, “Phantom Rows”).
Transaction data for a next-key lock appears similar to the following in SHOW ENGINE INNODB STATUS
and InnoDB monitor output:
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;
An insert intention lock is a type of gap lock set by INSERT
operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
The following example demonstrates a transaction taking an insert intention lock prior to obtaining an exclusive lock on the inserted record. The example involves two clients, A and B.
Client A creates a table containing two index records (90 and 102) and then starts a transaction that places an exclusive lock on index records with an ID greater than 100. The exclusive lock includes a gap lock before record 102:
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id |
+-----+
| 102 |
+-----+
Client B begins a transaction to insert a record into the gap. The transaction takes an insert intention lock while it waits to obtain an exclusive lock.
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);
Transaction data for an insert intention lock appears similar to the following in SHOW ENGINE INNODB STATUS
and InnoDB monitor output:
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 000000002215; asc " ;;
2: len 7; hex 9000000172011c; asc r ;;...
An AUTO-INC
lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT
columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.
The innodb_autoinc_lock_mode
configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.
For more information, see Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.
InnoDB
supports SPATIAL
indexing of columns containing spatial columns (see Section 11.5.9, “Optimizing Spatial Analysis”).
To handle locking for operations involving SPATIAL
indexes, next-key locking does not work well to support REPEATABLE READ
or SERIALIZABLE
transaction isolation levels. There is no absolute ordering concept in multidimensional data, so it is not clear which is the “next” key.
To enable support of isolation levels for tables with SPATIAL
indexes, InnoDB
uses predicate locks. A SPATIAL
index contains minimum bounding rectangle (MBR) values, so InnoDB
enforces consistent read on the index by setting a predicate lock on the MBR value used for a query. Other transactions cannot insert or modify a row that would match the query condition.