MySQL锁简介

作者: 只是甲 | 来源:发表于2020-08-04 09:15 被阅读0次

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL 事务

一.MySQL锁概述

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

1.表级锁定（table-level）

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

2.行级锁定（row-level）

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

3.页级锁定（page-level）

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。

使用页级锁定的主要是BerkeleyDB存储引擎。

总的来说，MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

二.InnoDB的八种锁

目前MySQL最常使用且支持事务的存储引擎是InnoDB，此处介绍InnoDB存储引擎的锁

锁类别	描述
Shared and Exclusive Locks	共享锁/排他锁
Intention Locks	意向共享锁/意向排他锁
Record Locks	行锁
Gap Locks	间隙锁
Next-Key Locks	临键锁
Insert Intention Locks	插入意向锁
AUTO-INC Locks	自增锁
Predicate Locks for Spatial Indexes	空间索引使用，本文暂不讨论

2.1 共享锁/排他锁

共享锁:S
排他锁:X

共享锁允许锁的持有者读取一行,其他事务可以获取共享锁读取数据，但是不能获取此条数据的排他锁
排他锁允许锁的持有者更新或删除一行，不允许其他事务获取该行的排他锁，只能等待该事物结束，其他事务才可以获取该行的排他锁

2.2 意向共享锁/意向排他锁

意向共享锁:IS
意向排他锁:IX

IS：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
IX：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁

共享锁/排他锁与意向共享锁/意向排他锁的兼容性关系：

image.png

2.3 行锁

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.

行锁是一个索引记录上的锁。
举例: SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
上面的语句，阻止其他事务 inserting, updating, or deleting 数据行在 t.c1 = 10 这行数据上

A record lock is a lock on an index record. 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.

这句话说明行锁一定是作用在索引上的。

2.4 间隙锁

间隙锁，在索引记录间隙上的锁，或者是第一条索引记录之前、最后一条索引记录之后上的间隙锁

测试案例来自《丁奇45讲》

create table t (
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 t values (0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

对列d的锁定

session A	session B	描述
begin; select * from t where d = 5 for update;
	mysql> insert into t values (1,1,5); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into t values (100,100,100); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into t values (-1,-1,-1); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	insert 全部失败了

因为列d没有索引，select * from t where d = 5 for update; 的时候会一行一行的查找，一行一行的加锁，直到锁定全表，那么这个锁什么时候释放呢，一直要到事务结束的时候才释放，也就是这条语句相当于 select * from t for update的效果。

对列c的锁定

session A	session B	描述
begin; select * from t where c = 5 for update;
	mysql> insert into t values (4,5,5); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into t values (4,4,4); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> mysql> insert into t values (6,6,6); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	这些insert都失败了
	mysql> insert into t values (11,11,11); Query OK, 1 row affected (0.01 sec)	这个insert成功了
	mysql> insert into t values (6,0,6); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into t values (6,10,6); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	这两个临界点的insert 也都失败了
	mysql> insert into t values (2,11,2); Query OK, 1 row affected (0.01 sec) mysql> insert into t values (4,11,4); Query OK, 1 row affected (0.01 sec) mysql> insert into t values (6,11,6); Query OK, 1 row affected (0.01 sec)	只会影响C列的非等值insert a、d列不受影响

insert into t values (4,5,5);
上面这个insert语句失败，可以理解，因为session A锁住了 c=5的这一行，为了避免幻读的出现，insert c=5是会失败的。
insert into t values (4,4,4); insert into t values (6,6,6);
上面2个语句为什么又失败了呢?
insert into t values (11,11,11);
上面这个insert为什么又成功了呢?

行锁只会给行加锁
也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录

InnoDB只好引入新的锁间隙锁 Gap Lock 开区间

表t 插入了6个记录，这就产生了7个间隙

image.png

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录"

image.png

select * from t where c=5 for update 其实限制了 c的值0-10的insert 包含0和10

InnoDB使用间隙锁的目的：

防止幻读，以满足相关隔离级别的要求；
满足恢复和复制的需要：
MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点：

一是 MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。
二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

2.5 临键锁(next key lock)

临键锁，索引记录锁以及索引记录之间的间隙锁，二者的组合锁

间隙锁和行锁合称为 next-key lock
每个 next-key lock是前开后闭区间

上例中的表t
select * froim t for update
形成了7个next-key lock
(-∞,0}、(0,5】、(5,10】、(10,15】、(15,20】、(20,25】、(25,+supermum）

+supermum为什么不是∞,因为是后闭的空间，可以理解为一个不存在的最大值

2.6 插入意向锁

意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。与间隙锁的另一个非常重要的差别是：尽管插入意向锁也属于间隙锁，但两个事务却不能在同一时间内一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

session A	session B	描述
CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; INSERT INTO child (id) values (90),(102);
START TRANSACTION; SELECT * FROM child WHERE id > 100 FOR UPDATE;
	START TRANSACTION; mysql> INSERT INTO child (id) VALUES (101); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-06-26 16:25:35 0x59d4 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 53 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 89 srv_active, 0 srv_shutdown, 107334 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 12
OS WAIT ARRAY INFO: signal count 10
RW-shared spins 4, rounds 4, OS waits 0
RW-excl spins 21, rounds 100, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 1.00 RW-shared, 4.76 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 22279
Purge done for trx's n:o < 22277 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281475316584688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281475316582144, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 22278, ACTIVE 337 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 14, OS thread handle 17448, query id 296 localhost ::1 root update
INSERT INTO child (id) VALUES (101)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 114 page no 4 n bits 72 index PRIMARY of table `test1`.`child` trx id 22278 lock_mode X locks gap before rec in
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 000000005700; asc     W ;;
 2: len 7; hex 82000000d6011d; asc        ;;

------------------
---TRANSACTION 22277, ACTIVE 351 sec
2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 13, OS thread handle 11248, query id 287 localhost ::1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1082 OS file reads, 2147 OS file writes, 1090 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 0.02 non-hash searches/s
---
LOG
---
Log sequence number          64214586
Log buffer assigned up to    64214586
Log buffer completed up to   64214586
Log written up to            64214586
Log flushed up to            64214586
Added dirty pages up to      64214586
Pages flushed up to          64214586
Last checkpoint at           64214586
602 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 471528
Buffer pool size   8192
Free buffers       6950
Database pages     1238
Old database pages 465
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1049, created 189, written 1214
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: 1238, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=21004, Main thread ID=0000000000001F54 , state=sleeping
Number of rows inserted 47, updated 6, deleted 3, read 289
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 136, updated 419, deleted 51, read 5155
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

2.7 自增锁

自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。

三.InnoDB加锁方法

意向锁是 InnoDB 自动加的，不需用户干预。
对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；
对于普通 SELECT 语句，InnoDB 不会加任何锁；
事务可以通过以下语句显式给记录集加共享锁或排他锁：
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。
但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。
其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

3.1 隐式锁

InnoDB在事务执行过程中，使用两阶段锁协议：
随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁
锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

3.2 显式锁定

select ... lock in share mode //共享锁 
select ... for update //排他锁

select for update：

在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。

select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

select lock in share mode ：in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。select *** lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了 S 锁。

性能影响：
select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。
select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

四.死锁

死锁产生原因:
1.产生回路
两个或两个以上的事务执行过程中，分别持有一把锁，然后加另一把锁(AB-BA),产生死锁。
2.加锁顺序不一致
两个或两个以上的事务并发执行(同一时刻)，因争夺锁资源而造成的一种相互等待，产生死锁。

测试sql:

create table t1(id int primary key,name varchar(20));
insert into t1 values (1,'AAAAA'),(2,'BBBBB');

session A	session B	描述
begin; update t1 set name = 'DDDDD' where id = 2;
	begin; update t1 set name = 'DDDDD' where id = 2;
update t1 set name = 'ddddd' where id = 2;		被锁住
	update t1 set name = 'CCCCC' where id = 1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction	session A被锁住的执行成功，此条语句直接报死锁，整个事务被回滚了

减少死锁:
1.自动死锁检测，优先回滚小事务
2.超时设置(参数innodb_lock_wait_timeout)
3.尽快提交事务，小事务不容易发生死锁
4.加for update、lock in share mode读锁时，最好降低事务隔离级别，例如用RC级别，降低死锁发生概率
5.事务中设计多个表，或者涉及多行记录时，每个事务的操作顺序都要保持一致，降低死锁概率，最好用存储过程固话
6.通过索引等方式优化SQL效率，降低死锁发生概率（减小扫描/锁范围，降低概率）

五.MDL锁

MySQL5.5版本引入了MDL锁(metadata lock),用于解决或者保证DDL操作与DML操作之间的一致性。
在mysqldump的时候不能做DDL操作，会提示waiting for table metadata lock;做DDL操作没办法保护事务，因此引入了meta data lock

session A	session B	描述
begin; select * from t1;
	begin; drop table t1;	被锁住

如果没有MDL锁的保护，那么回话B可以直接执行DDL操作，这样会导致回话A出错。
MySQL5.5 版本引入了MDL锁就是保护这种情况的发生，由于回话A开启了查询，那么获得了MDL锁，锁的模式为SHARED_READ,回话B要执行DDL，则需获得X锁，两者互斥，所以回话B会等待。

MDL锁实在Server中实现。

六.MySQL与锁相关的系统表及视图

6.1 show engine innodb status

测试案例通MDL锁的测试案例

-- 查看InnoDB存储引擎的运行状况
show engine innodb status\G

image.png

6.2 information_schema.innodb_locks

测试案例:

create table t1(id int,name varchar(100));
insert into t1 values (1,'abc'),(2,'def');

session A	session B	描述
begin; update t1 set name = 'ghi' where id = 1;
	begin; update t1 set name = 'ddd' where id = 2;	被锁住了

MySQL 8.0 开始information_schema.innodb_locks 改为 performance_schema.data_locks

mysql> select * from data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 339873184:1176:300057464
ENGINE_TRANSACTION_ID: 22371
            THREAD_ID: 60
             EVENT_ID: 13
        OBJECT_SCHEMA: test1
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 300057464
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 339873184:118:4:2:300054680
ENGINE_TRANSACTION_ID: 22371
            THREAD_ID: 60
             EVENT_ID: 13
        OBJECT_SCHEMA: test1
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 300054680
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: WAITING
            LOCK_DATA: 0x00000000C60A
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 339872336:1176:300052488
ENGINE_TRANSACTION_ID: 22370
            THREAD_ID: 59
             EVENT_ID: 32
        OBJECT_SCHEMA: test1
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 300052488
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 339872336:118:4:1:300049704
ENGINE_TRANSACTION_ID: 22370
            THREAD_ID: 59
             EVENT_ID: 32
        OBJECT_SCHEMA: test1
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 300049704
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 339872336:118:4:2:300049704
ENGINE_TRANSACTION_ID: 22370
            THREAD_ID: 59
             EVENT_ID: 32
        OBJECT_SCHEMA: test1
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 300049704
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x00000000C60A
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 339872336:118:4:3:300049704
ENGINE_TRANSACTION_ID: 22370
            THREAD_ID: 59
             EVENT_ID: 32
        OBJECT_SCHEMA: test1
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 300049704
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 0x00000000C60B
6 rows in set (0.00 sec)

MySQL 8.0之前
information_schema 数据库是mysql自带的，保存着关于MySQL服务器所维护的所有其他数据库的信息。其中innodb_locks表，记录了事务请求但是还没获得的锁，即等待获得的锁。

列名	列描述
lock_id	锁的id，由锁住的空间id编号、页编号、行编号组成
lock_trx_id	锁的事务id。
lock_mode	锁的模式。S[,GAP], X[,GAP], IS[,GAP], IX[,GAP]
lock_type	锁的类型，表锁还是行锁
lock_table	要加锁的表。
lock_index	锁住的索引。
lock_space	innodb存储引擎表空间的id号码
lock_page	被锁住的页的数量，如果是表锁，则为null值。
lock_rec	被锁住的行的数量，如果表锁，则为null值。
lock_data	被锁住的行的主键值，如果表锁，则为null值。

MySQL 8.0
Performance_Schema.data_locks

列名	列描述
ENGINE	持有锁或请求锁的存储引擎
ENGINE_LOCK_ID	存储引擎持有锁的id，唯一值，由存储引擎id及锁id组成
ENGINE_TRANSACTION_ID	事务id，INFORMATION_SCHEMA INNODB_TRX 表trx_id
THREAD_ID	thread id,Performance Schema threads 表 THREAD_ID
EVENT_ID	Performance Schema持有的事件id，thread_id 和event_id 组成
OBJECT_SCHEMA	schema名
OBJECT_NAME	表名
PARTITION_NAME	分区表分区名
SUBPARTITION_NAME	子分区表子分区名
INDEX_NAME	索引名
OBJECT_INSTANCE_BEGIN	内存里锁的地址
LOCK_TYPE	锁的类型，表锁还是行锁
LOCK_MODE	锁的模式。S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, and UNKNOWN
LOCK_STATUS	锁状态，GRANTED表示已获取到锁，WAITING表示请求锁中
LOCK_DATA	锁的数据，对于innodb而言，行锁会显示行的地址，其它显示为null

参考文档:

1.https://dev.mysql.com/doc/refman/8.0/en/
2.丁奇45讲
3.https://blog.csdn.net/Saintyyu/article/details/91269087
4.https://zhuanlan.zhihu.com/p/29150809
5.https://www.cnblogs.com/lhdz_bj/p/10522284.html
6.https://www.cnblogs.com/frankyou/p/9582587.html

MySQL锁简介

一.MySQL锁概述

1.表级锁定（table-level）

2.行级锁定（row-level）

3.页级锁定（page-level）

二.InnoDB的八种锁

2.1 共享锁/排他锁

2.2 意向共享锁/意向排他锁

2.3 行锁

2.4 间隙锁

2.5 临键锁(next key lock)

2.6 插入意向锁

2.7 自增锁

三.InnoDB加锁方法

3.1 隐式锁

3.2 显式锁定

四.死锁

五.MDL锁

六.MySQL与锁相关的系统表及视图

6.1 show engine innodb status

6.2 information_schema.innodb_locks

参考文档:

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL