MySQL InnoDB存储引擎学习

数据库:物理操作系统文件或其它形式文件类型的集合.
数据库实例:由数据库后台进程/线程以及一个共享内存区组成.
数据库实例才是真正用来操作数据库文件的.

MySQL数据库架构

InnoDB存储引擎介绍

MySQL5.5.5后默认的存储引擎

InnoDB内存

InnoDB存储引擎内存:

  1. 缓冲池(buffer pool)
    1. 索引页
    2. 数据页
    3. undo页
    4. 插入缓存(insert buffer)
    5. 自适应哈希索引(adaptive hash index)
    6. InnoDB存储的锁信息(lock info)
    7. 数据字典信息(data dictionary)
  2. 重做日志缓冲池(redo log buffer)
  3. 额外内存池(additional memory pool)
    InnoDB存储引擎内存结构

缓冲池是占用内存最多的部分,用来存放各种数据的缓存.InnoDB将数据库文件按页读取到缓冲池,再按最近最少使用(LRU)算法保留缓冲区的缓存数据.数据库的修改总是先修改缓冲池中的页,修改完后该页即为脏页,再按一定频率将脏页刷新到文件中.
重做日志缓存将重做日志信息放在该缓存区,按照一定频率将其刷新到重做日志文件.

查看内存池配置大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_log_buffer_size';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_additional_mem_pool_size';
+---------------------------------+---------+
| Variable_name | Value |
+---------------------------------+---------+
| innodb_additional_mem_pool_size | 8388608 |
+---------------------------------+---------+
1 row in set (0.00 sec)

通过show engine innodb status\G查看缓冲池的使用情况(并不是实时的,而是过去某段时间的)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> show engine innodb status\G
... ...
Per second averages calculated from the last 52 seconds 过去53秒内的数据库状态
... ...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 21773263
Buffer pool size 8192 缓冲帧(buffer frame) 每个buffer frame为16K
Free buffers 0 当前空闲的buffer frame
Database pages 8151 已经使用的缓冲帧
Old database pages 2988
Modified db pages 0 脏页的数量
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9725418, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6612986, created 56449, written 624295
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8151, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
... ...

InnoDB线程

InnoDB采用多线程模型.默认情况下,InnoDB存储引擎的后台线程有四类:若干IO Thread, 一个Master Thread, 一个Lock监控线程, 一个错误监控线程.可以通过show engine innodb status\G查看InnoDB的线程状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> show engine innodb status\G
*************************** 1. row ***************************
... ...
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
6614275 OS file reads, 661776 OS file writes, 44313 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
... ...

IO Thread分为insert buffer threadlog threadread threadwrite thread

  1. Master Thread将缓冲池的数据异步刷新到磁盘,保证数据一致性(刷新脏页、合并插入缓存、undo页回收)

    1
    2
    3
    4
    5
    6
    mysql> show engine innodb status\G
    *************************** 1. row ***************************
    ... ...
    srv_master_thread loops: 2084267 1_second, 2084267 sleeps, 190202 10_second, 182347 background, 182347 flush
    srv_master_thread log flush and writes: 2082683
    ... ...
  2. IO Thread负责IO请求的回调处理

  3. Purge Thread事物提交后的undo log不再需要,由Purge Thread负责回收已经使用并分配的undo page
  4. Page Cleaner ThreadInnoDB 1.2.x版本引入,将脏页的刷新操作放在这个线程中进行处理,减轻Master Thread的工作,减少用户查询线程的阻塞,提高InnoDB的性能

InnoDB关键特性

插入缓存提高性能,两次写提高可靠性

插入缓存

主键是行唯一的标识符,应用程序中行记录的插入一般是按照主键递增的顺序进行插入,因此,插入聚集索引一般是顺序的,不需要磁盘的随机读取,可以很快完成,但表上可能会同时存在多个非聚集索引非唯一的索引,在插入时数据页的存放按照主键的递增顺序存放,但对于非聚集索引,叶子节点的插入可能不再是顺序的,需要离散的访问非聚集索引页,插入性能降低.
InnoDB使用插入缓存的方式,对非聚集索引的插入或更新操作,不是每一次直接插入索引页,而是先判断非聚集索引页是否在缓存池中,如果在,直接插入,如果不在先放入一个插入缓冲区,再以一定频率执行插入缓冲和非聚集索引页子几点的合并操作.
插入缓存的使用需满足两个条件:

  1. 索引是非聚集索引
  2. 索引不是唯一索引
1
2
3
4
5
6
7
8
9
10
mysql> show engine innodb status\G
... ...
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7, 201 merges
merged operations:
insert 1846, delete mark 2126, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
... ...

两次写

当数据库宕机时,可能发生数据库正在写一个页面,而该页面只写了一部分(比如16K,只写了前4K的情况),称为部分写失效
发生写失效时,需要通过这个页的副本来还原该页,再进行重做.
InnoDB两次写

自适应哈希索引

当InnoDB注意到索引值使用非常频繁便会自动在内存中再建立一个哈希索引

1
2
3
4
5
mysql> show engine innodb status\G
... ...
Hash table size 276707, node heap has 41 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
... ...

数据库和表的建立

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE DATABASE case_test_db;

USE case_test_db;

CREATE TABLE t_user(
Fid int PRIMARY KEY COMMENT '用户ID',
Fname VARCHAR(255) NOT NULL COMMENT '姓名',
Famount bigint NOT NULL DEFAULT 0 COMMENT '金额',
Fcreate_time DATETIME NOT NULL COMMENT '创建时间',
Fmodify_time DATETIME NOT NULL COMMENT '修改时间',
INDEX idx_mdftime(Fmodify_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表' ;

INSERT INTO t_user
(Fid, Fname, Famount, Fcreate_time, Fmodify_time)
VALUES(10000, "用户1", 0, "2019-06-18 15:55:00", "2019-06-18 15:55:00"),
(10001, "用户2", 0, "2019-06-18 15:55:04", "2019-06-18 15:55:04"),
(10002, "用户3", 0, "2019-06-18 15:55:05", "2019-06-18 15:55:05");

SELECT Fid, Fname, Famount, Fcreate_time, Fmodify_time FROM case_test_db.t_user;

索引

索引使用B+树或哈希表建立.
InnoDB不能显式的指定哈希索引,InnoDB引擎自适应哈希索引,哈希索引只适用于字典查找,如SELECT Famount FROM t_user WHERE Fid=10001;.

聚集索引

InnoDB存储引擎类型的表,表中数据按照主键顺序存放.聚集索引就是按照表的主键构建一颗B+树,并在叶子节点存放行的全部数据.
InnoDB存储引起类型的表,有且仅有一个聚齐索引.
当使用聚齐索引对表中数据进行检索时,可以直接获得聚集索引所对应的整条行记录数据所在的页.

非聚齐索引(辅助聚集索引)

非聚齐索引叶子节点不包含行的全部数据,只包含一个用于查找行数据的书签,在InnoDB中这个标签就是数据行的主键,因此通过非聚齐索引只能查找到数据行的主键,再通过主键查找数据行所在的数据页,比聚集索引多了一次操作.

按功能的索引

  1. 普通索引 最基础的索引类型
  2. 唯一索引 在普通索引的基础上加入唯一性限制,索引列的所有值只能出现一次
  3. 全文索引 MySQL 5.6.24在InnoDB加入全文索引,但对中文支持不友好,MySQL 5.7.6提供支持中文的内置的ngram解析器
  4. 空间索引 MySQL 5.7开始支持空间索引

事务

事务的属性(ACID)

  1. 原子性(Atomic) 一个事务中的SQL,要么全部成功,要么全部失败,不存在部分成功的情况
  2. 一致性(Consistency) 一个事务使系统从一个一致状态转移到另外一个一致状态
  3. 隔离性(Isolation) 并发事务互相影响的程度
  4. 持久性(Durability) 一旦事务提交,对数据的修改是永久的,及时系统故障也不会数据丢失

隔离性

事务并发存在的问题

数据库测试前,将两个Serssion的隔离级别置为Read Uncommitted

1
set tx_isolation = 'read-uncommitted';

更新丢失(Lost Update)

同时有两个事务对同一条数据库记录进行操作,事务A读取数据库数据后,数据被事务B进行了更新,事务A基于最初读取的值进行运算并将运算结果写回到数据库,导致事务B的更新被覆盖,导致更新丢失的问题

事务A 事务B
START TRANSACTION; START TRANSACTION;
SELECT Famount FROM t_user WHERE Fid=10001; 值为0 SELECT Famount FROM t_user WHERE Fid=10001; 值为0
运算 对Famount加100 UPDATE t_user SET Famount=1 WHERE Fid=10001;Famount加1
UPDATE t_user SET Famount=100 WHERE Fid=10001; COMMIT;
COMMIT; -
SELECT Famount FROM t_user WHERE Fid=10001; 值为100 SELECT Famount FROM t_user WHERE Fid=10001; 值为100

脏读(Dirty Reads)

事务A在事务的过程中修改了一条记录,在A提交前,该记录处于中间状态,如果事务B访问该记录,读到了这个中间状态,可能出现数据不一致的状态,叫做脏读.

事务A 事务B
START TRANSACTION; -
SELECT Famount FROM t_user WHERE Fid=10001; 值为100 -
运算 对Famount加100 -
UPDATE t_user SET Famount=200 WHERE Fid=10001; START TRANSACTION;
运算 对Famount再加100 SELECT Famount FROM t_user WHERE Fid=10001; 此时读到事务AFamount字段的中间状态200
UPDATE t_user SET Famount=300 WHERE Fid=10001; 运算 对Famount加20
COMMIT; UPDATE t_user SET Famount=220 WHERE Fid=10001;
- COMMIT;
SELECT Famount FROM t_user WHERE Fid=10001; 值为220 SELECT Famount FROM t_user WHERE Fid=10001; 值为220

使用InnoDB引擎避免脏读

  1. 通过共享锁或排他锁避免脏读,对于所有更新操作,在读取数据库记录前都获得该记录的排他锁获取共享锁
  2. 通过InnoDB的事务隔离级别避免脏读

不可重复读(Non-Repeatable Reads)

事务A读取某条记录后,记录被事务B进行修改,事务A再次读取该记录,该记录的数据和事务A初次读取的记录不一致.

事务A 事务B
START TRANSACTION; -
SELECT Famount FROM t_user WHERE Fid=10001; 值为220 START TRANSACTION;
- UPDATE t_user SET Famount=100 WHERE Fid=10001;
- COMMIT;
SELECT Famount FROM t_user WHERE Fid=10001; 值为100 -
读取到的数据和初次读取的数据不一致 -
ROLLBACK; -

和脏读的区别:脏读是读到事务中间过程的临时数据,可能会被事务再次修改或回滚.不可重复读是由于两个读取时间点的间隙,数据被其它事务修改而导致的数据不一致.
可以通过设置InnoDB的事务隔离级别避免不可重复读

幻读(Phantom Reads)

幻读发生在多条记录的查询中.当事务A用同样的查询条件读取之间某个时间点查询过的数据时,读取到了被其它事务在其两次读取中间插入的新数据(或者少了被其它事务删除的记录).称为幻读
幻读和不可重复读本质一样,都是在两次读取间隙由于其它事务的操作导致的记录数据变更,区别在于不可重复度针对单条记录,幻读针对一个结果集而言.

事务A 事务B
START TRANSACTION; -
SELECT Fid FROM t_user WHERE Fid>10001; 读到10002 START TRANSACTION;
- INSERT INTO t_user (Fid, Fname, Famount, Fcreate_time, Fmodify_time) VALUES(10003, "用户4", 0, "2019-06-18 15:55:00", "2019-06-18 15:55:00");
- COMMIT;
SELECT Fid FROM t_user WHERE Fid>10001; 读到10002、10003
读取到的结果集和初次读取的结果集不一致 -
ROLLBACK; -

隔离级别

事务并发问题不能单靠数据库事务控制来解决,需要结合InnoDB的锁机制来解决.防止出现事务并发问题是应用层需要解决的问题,不能单靠事务机制
更新丢失需要完全避免,更新丢失会导致严重的数据不一致性,数据库记录表现出随机性、不可预测性
脏读、不可重复读、幻读是数据库读一致性问题,需要由数据库提供一定的事务隔离机制解决.
数据库实现事务隔离的方式:

  1. 读取数据前对数据加锁,阻止其它事务对其进行修改
  2. 通过数据库机制生成一个时间点的一致性数据快照,从用户角度看,数据库提供了一个数据的多个版本,数据库多版本并发控制(MultiVersion Concurrency Control)或多版本数据库
隔离级别 读一致性 脏读 不可重复读 幻读 读加锁
未提交读(Read Uncommitted) 最低级别
已提交读(Read Committed) 语句级别
可重复读(Repeatable Read) 事务级别
串行化(Serializable) 最高级别,事务级

未提交读(Read Uncommitted)

最低的事务隔离级别,事务B可能读取到事务A未提交的中间状态数据

已提交读(Read Committed)

事务B可能读取到事务开始时到读取操作点之间被事务A提交的修改
可避免脏读的发生

可重复读(Repeatable Read)

MySQL InnoDB的默认隔离模式,事务B只能读到事务开始时的数据,及时数据已经被事务A修改
可避免脏读和不可重复读

串行化(Serializable)

最高的事务隔离级别,避免脏读、不可重复读、幻读.

如下示例演示在不同隔离级别下,事务A读到Famount的值的不同

事务A 事务B 未提交读 已提交读 可重复读 串行化
START TRANSACTION; START TRANSACTION; - - - -
SELECT Famount FROM t_user WHERE Fid=10001; - 100 100 100 100
- UPDATE t_user SET Famount=200 WHERE Fid=10001; - - - -
SELECT Famount FROM t_user WHERE Fid=10001; - 200 100 100 100
- COMMIT; - - - -
SELECT Famount FROM t_user WHERE Fid=10001; - 200 200 100 100
COMMIT; - - - - -
SELECT Famount FROM t_user WHERE Fid=10001; - 200 200 200 200

隔离级别为串行化时,事务B执行UPDATE操作的时候,提交事务会被锁住,直到事务A提交后,事务B才可以继续进行.

读未提交:别人改数据的事务尚未提交,我在我的事务中也能读到.
读已提交:别人改数据的事务已经提交,我在我的事务中才能读到.
可重复读:别人改数据的事务已经提交,我在我的事务中也不去读.
串行:我的事务尚未提交,别人就别想改数据.
四种隔离级别,并行性能依次降低,安全性依次提高

隔离级别的实现

隔离级别是通过视图(read-view)来实现的并发版本控制(MVCC),不同隔离级别创建视图的时间点不同.

  1. 未提交读 不创建视图,直接返回记录上的最新值
  2. 已提交读 给每条SQL创建视图,在每个SQL语句开始执行时创建视图,隔离作用域仅限该条SQL.
  3. 可重复读 每个事务创建一个视图,整个事务存在期间都用该视图
  4. 串行化 直接加锁,不创建视图
    视图可以理解为数据副本,每次创建视图时,将当前已持久化数据创建副本,后续直接从副本读取,达到数据隔离效果.

InnoDB隔离级别的查看/修改

  1. 隔离级别的查看

    1
    2
    3
    4
    5
    6
    7
    mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set (0.00 sec)
  2. 隔离级别的设置

    1. 全局
      1. 修改配置文件,重启数据库
      2. set global transaction isolation level repeatable read;
    2. 当前Session
      1. set tx_isolation = 'repeatable-read';
      2. set session transaction isolation level repeatable read;
    3. 下一个事务set transaction isolation level repeatable read;

事务控制

1
2
3
4
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

在InnoDB中,任何SQL语句都在一个事务当中. 如果在没有显式的调用开始事务的命令,MySQL会自动加上开始事务命令,再执行SQL语句.
MySQL默认使用自动提交模式,即一条SQL语句执行完毕,自动COMMIT.当MySQL显式执行开始事务的命令后,会自动设置为非自动提交模式,ROLLBACKCOMMENT后恢复到START TRANSACTION之前的事务提交模式.

事务的开始

当执行START TRANSACTION;BEGIN;后事务并没有真正的开始,当事务真正的执行操作后才会生成事务ID,事务才真正开始.
如果想START TRANSACTION;后就真正开启事务,需要以START TRANSACTION WITH CONSISTENT SNAPSHOT;开始事务

数据库测试前,将两个Serssion的隔离级别置为Repeatable Read, set tx_isolation = 'repeatable-read';

事务A 事务B
SELECT Famount FROM t_user WHERE Fid=10001; 值为200 -
START TRANSACTION; START TRANSACTION;
- UPDATE t_user SET Famount=300 WHERE Fid=10001;
- COMMIT;
SELECT Famount FROM t_user WHERE Fid=10001; 值为300 -
COMMIT; -

复原Famount的值为200UPDATE t_user SET Famount=200 WHERE Fid=10001;

事务A 事务B
SELECT Famount FROM t_user WHERE Fid=10001; 值为200 -
START TRANSACTION WITH CONSISTENT SNAPSHOT; START TRANSACTION;
- UPDATE t_user SET Famount=300 WHERE Fid=10001;
- COMMIT;
SELECT Famount FROM t_user WHERE Fid=10001; 值为200 -
COMMIT; -

事务的实现

事务的隔离性由锁和MVCC实现,原子性、一致性、持久性通过数据库的redo和undo来完成.

redo

在InnoDB存储引擎中,事务日志通过重做(redo)日志文件和InnoDB存储引擎的日志缓冲(InnoDB Log Buffer)来实现.当开始一个事务时,会记录该事务的LSN(Log Sequence Number, 日志序列号);当事务执行时,会往InnoDB存储引擎的日志缓冲里插入事务日志;当事务提交时,必须将InnoDB存储引擎的日志缓冲写入磁盘.即写数据前,需要先写日志,称为预写日志方式(Write-Ahead Logging, WAL).InnoDB通过WAL方式保证事务的完整性,导致磁盘存储的数据页和内存缓冲池中的页不同步,对于内存缓冲池中页的修改,先是写入重做日志文件,然后再写入磁盘.

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> show engine innodb status\G
... ...
---
LOG
---
Log sequence number 592623868976 当前LSN
Log flushed up to 592623868976 刷新到重做日志文件的LSN
Last checkpoint at 592623868976 刷新到磁盘的LSN
0 pending log writes, 0 pending chkp writes
19876 log i/o's done, 0.00 log i/o's/second
----------------------
... ...

undo

事务的撤销使用undo,undo存放在数据库内部的一个特殊段中,称为undo段(undo segment),undo段存放在共享表空间中.数据库undo只是进行逻辑上的恢复,恢复后的数据结构可能可原先不同.

乐观锁:总认为没有别人会对资源进行修改,当回写时再对初次拿到的资源和当前资源进行判断,如果没有发生变化就写回,否则进行重试,整个过程并没有对数据加锁.
悲观锁:在获取资源前对资源加锁,确保同一时刻只有有限的线程可以访问该资源,其它想获取改资源的操作进入等待状态,直到该进程完成对资源的操作并释放锁,其它线程再重新争夺锁.

InnoDB使用的都是悲观锁

锁的种类

对数据的操作包括读和写,InnoDB实现锁时,对读和写使用不同的锁.
InnoDB实现了标准的行级锁,即共享锁(Shared Lock)和互斥锁(Exclusive Lock).

  1. 共享锁(读锁 S Lock):允许事务对一条数据进行读取.
  2. 互斥锁(写锁 X Lock):允许事务对一条数据进行删除/更新.

锁的粒度

InnoDB除了支持行锁,还支持表锁,称为意向锁(Intention Lock).意向锁针对读和写也分为两种:

  1. 意向共享锁(IS Lock):事务想要获得一个表中某几行的共享锁
  2. 意向排他锁(IX Lock):事务想要获得一个表中某几行的互斥锁
    由于InnoDB支持行锁,所以意向锁并不会阻塞除全表扫之外的任何请求,意向锁主要是为了表示有人请求锁定表中的某一行数据.

    单单使用行锁,一个请求对全表进行操作时需要判断表中所有行的锁定状态,效率太差.引入意向锁后,当有人使用行锁对表中某行进行修改前,先对表添加意向锁互斥锁(IX),在对记录添加互斥锁(X),当有人对全表进行操作时,不需要再判断表中所有行的锁定状态,只需要等待意向锁被释放即可.

锁的兼容

- IS Lock IX Lock S Lock X Lock
IS Lock ×
IX Lock × ×
S Lock × ×
X Lock × × × ×

锁的算法

InnoDB的锁实际上是对索引进行加锁.
InnoDB的三种锁的算法设计:

  • Record Lock(记录锁): 单个行记录上的锁
  • Gap Lock(间隙锁): 锁定一个范围,但不包含记录本身 间隙锁也分为共享锁和互斥锁,但间隙共享锁和间隙互斥锁并不互斥,不同的事务可以同时持有一段相同范围的共享锁和互斥锁,唯一阻止的是其它事务在范围中添加/删除纪录
  • Next-Key Lock(): Gap Lock + Record Lock,锁定一个范围,并且锁定记录本身

加锁语句

  1. LOCK IN SHARED MODE用于给记录加共享锁
  2. FOR UPDATE用于给记录加互斥锁

不同SQL语句在各种隔离级别下的锁方式

- 条件 未提交读 已提交读 可重复读 串行化
SELECT 唯一索引 不加锁 一致性读 一致性读 共享锁
SELECT 范围索引 不加锁 一致性读 一致性读 间隙共享锁
UPDATE 唯一索引 互斥锁 互斥锁 互斥锁 互斥锁
UPDATE 范围索引 互斥锁 间隙互斥锁 间隙互斥锁 间隙互斥锁
INSERT - 互斥锁 互斥锁 互斥锁 互斥锁
REPLACE 无键冲突 互斥锁 互斥锁 互斥锁 互斥锁
REPLACE 键冲突 间隙互斥锁 间隙互斥锁 间隙互斥锁 间隙互斥锁
DELETE 唯一索引 互斥锁 互斥锁 互斥锁 互斥锁
DELETE 范围索引 间隙互斥锁 间隙互斥锁 间隙互斥锁 间隙互斥锁
SELECT LOCK IN SHARE MODE 唯一索引 共享锁 共享锁 共享锁 共享锁
SELECT LOCK IN SHARE MODE 范围索引 共享锁 共享锁 间隙共享锁 间隙共享锁
SELECT FOR UPDATE 唯一索引 互斥锁 互斥锁 互斥锁 互斥锁
SELECT FOR UPDATE 范围索引 互斥锁 共享锁 间隙互斥锁 间隙互斥锁

死锁

两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源.
InnoDB有一个专门的锁监控线程,该线程负责查看可能的死锁问题,并自动告知用户.
InnoDB在发现死锁后,会马上回滚一个事务(持有最少行级互斥锁的事务),该事物其占用的资源被释放,另一个事务即可获得该资源,并继续向下执行.
死锁的四个必要条件:互斥,不可剥夺,请求和保持,循环等待

事务A 事务B
START TRANSACTION; START TRANSACTION;
SELECT Famount FROM t_user WHERE Fid=10001 FOR UPDATE;
- SELECT Famount FROM t_user WHERE Fid=10002 FOR UPDATE;
SELECT Famount FROM t_user WHERE Fid=10002 FOR UPDATE; 阻塞 等待10002的释放 -
- SELECT Famount FROM t_user WHERE Fid=10001 FOR UPDATE;
得到10002的锁 执行完毕 发现死锁 自动回滚该事务ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
COMMIT; -

参考资料