前言
最近在忙工作的事，没时间写，加班已老实求放过 (ಥ﹏ಥ)。
今天就随便从案例上看看 INSERT 的加锁情况吧。
这篇不写官方文档的描述，通过例子看现象推导吧，累了。
锁兼容性
X | IX | S | IS | |
|---|---|---|---|---|
X | 冲突 | 冲突 | 冲突 | 冲突 |
IX | 冲突 | 兼容的 | 冲突 | 兼容的 |
S | 冲突 | 冲突 | 兼容的 | 兼容的 |
IS | 冲突 | 兼容的 | 兼容的 | 兼容的 |
show global 设置的隔离级别的作用域
先看隔离级别
root@localhost [(none)] 22:17:34 > show global variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.02 sec)
root@localhost [(none)] 22:17:51 > set global transaction_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)] 22:18:01 > show global variables like 'transaction_isolation';
+-----------------------+------------------+
| Variable_name | Value |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)
全局是 READ-UNCOMMITTED 隔离级别，大家猜猜现有开启事务是用什么隔离级别？
root@localhost [(none)] 22:31:20 > select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 344090
trx_state: RUNNING
trx_started: 2024-11-19 22:23:53
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 9
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 1128
trx_rows_locked: 0
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_schedule_weight: NULL
1 row in set (0.00 sec)
可以看到还是 READ COMMITTED，因为 set global 作用域是后续会话。
RU 隔离级别
insert 不冲突加锁
创建测试表
create table insertlock(id int primary key,info varchar(64));
insert into insertlock values(1,'aa'),(5,'bb'),(10,'cc');
插入一条测试一下：
begin;
insert into insertlock values(3,'dd');
看看该事务加了什么锁
*************************** 1. row ***************************
PCL_ID: 11
THREAD_ID: OS:91852 ID:49 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344091
TRX_ISOLATION_LEVEL: READ UNCOMMITTED
trx_state: RUNNING
trx_started: 2024-11-19 22:40:15
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:01:59
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:01:59
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 1
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、9、TABLE、IX、GRANTED
MDL_details:
ddl、insertlock、9、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
1 row in set (0.01 sec)
里可以看到该事务的锁定对象是 TABLE
、获取的锁类型为 IX
意向排他锁，获取了事务级别的 SHARED_WRITE
共享写锁。
再开一个会话开启事务看看加锁类型：
begin;
insert into insertlock values(4,'dd');
可以看到获取的锁类型，如下所示：
*************************** 1. row ***************************
PCL_ID: 12
THREAD_ID: OS:91969 ID:50 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344102
TRX_ISOLATION_LEVEL: READ UNCOMMITTED
trx_state: RUNNING
trx_started: 2024-11-19 22:43:16
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:00:09
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:00:09
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(4,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 1
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、11、TABLE、IX、GRANTED
MDL_details:
ddl、insertlock、11、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
还是一样的表级别的 IX
锁，和 SHARED_WRITE
锁，可以看到 RU 级别，IX/IX
、SHARED_WRITE/SHARED_WRITE
是共享的。
SHARED_WRITE
是 server 层的元数据锁
为啥？
这是因为 MVCC (多版本并发控制) 的特性。MVCC 允许多个事务同时进行读取和写入操作，而不会互相阻塞。
在 MVCC 下，每个事务会看到一个数据库的一致性快照，而不必等待其他事务完成。这就意味着，当多个事务尝试写入数据时，即使它们获取了共享的写锁，也可以并发地进行写操作，而不会相互阻塞。因此，IX/IX
和 SHARED_WRITE/SHARED_WRITE
可以是共享的。
这种机制提高了数据库的并发性能，同时保证了事务的隔离性和一致性。
insert 冲突加锁
在连接 12 发出：
insert into insertlock values(3,'wda');
查看连接的加锁情况
*************************** 1. row ***************************
PCL_ID: 12
THREAD_ID: OS:91969 ID:50 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344102
TRX_ISOLATION_LEVEL: READ UNCOMMITTED
trx_state: LOCK WAIT
trx_started: 2024-11-19 22:43:16
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:05:34
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 00:00:03
TRX_WAIT_STARTED: 2024-11-19 22:48:47
TRX_WAIT: 00:00:03
PROCESSLIST_STATE: update
PROCESSLIST_INFO: insert into insertlock values(3,'wda')
last_statement: insert into insertlock values(3,'wda')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 2
TRX_OPERATION_STATE: inserting
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、11、TABLE、IX、GRANTED
ddl、insertlock、14、PRIMARY、RECORD、S,REC_NOT_GAP、WAITING、3
MDL_details:
ddl、insertlock、11、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
14、GLOBAL、INTENTION_EXCLUSIVE、STATEMENT、GRANTED、sql_base.cc:3058
*************************** 2. row ***************************
PCL_ID: 11
THREAD_ID: OS:91852 ID:49 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344091
TRX_ISOLATION_LEVEL: READ UNCOMMITTED
trx_state: RUNNING
trx_started: 2024-11-19 22:40:15
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:08:35
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:08:35
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 2
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、9、TABLE、IX、GRANTED
MDL_details:
ddl、insertlock、9、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
*************************** 3. row ***************************
PCL_ID: 11
THREAD_ID: OS:91852 ID:49 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344091
TRX_ISOLATION_LEVEL: READ UNCOMMITTED
trx_state: RUNNING
trx_started: 2024-11-19 22:40:15
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:08:35
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:08:35
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 2
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、14、PRIMARY、RECORD、X,REC_NOT_GAP、GRANTED、3
MDL_details:
14、GLOBAL、INTENTION_EXCLUSIVE、STATEMENT、GRANTED、sql_base.cc:3058
3 rows in set (0.01 sec)
连接 11 第一条为链表中最开始为 insert
添加的 IX
锁和 SHARED_WRITE
锁
lock_details:
ddl、insertlock、9、TABLE、IX、GRANTED
MDL_details:
ddl、insertlock、9、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
第二条是为了阻塞其他事务而对 ID 为 3 加的的主键非间隙记录锁，防止对该主键值做操作。
同时有 server 层的 INTENTION_EXCLUSIVE
，发现其他事务要操作主键值为 3 的记录所以将 SHARED_WRITE
→ INTENTION_EXCLUSIVE
。
INTENTION_EXCLUSIVE
是一种声明型意向锁，表示当前事务对表有即将进行排他写操作的意图。
lock_details:
ddl、insertlock、14、PRIMARY、RECORD、X,REC_NOT_GAP、GRANTED、3
MDL_details:
14、GLOBAL、INTENTION_EXCLUSIVE、STATEMENT、GRANTED、sql_base.cc:3058
连接 12 的加锁情况
lock_details:
ddl、insertlock、11、TABLE、IX、GRANTED
ddl、insertlock、14、PRIMARY、RECORD、S,REC_NOT_GAP、WAITING、3
MDL_details:
ddl、insertlock、11、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
14、GLOBAL、INTENTION_EXCLUSIVE、STATEMENT、GRANTED、sql_base.cc:3058
已授予的 IX
锁和 SHARED_WRITE
锁是最开始 insert
的 id 为 4 的时候授予的。
虽然插入 id 为 3 的记录但是上面一个有一个 X 锁，所以降级为 S 锁，不允许其他事务 insert
，所以 MDL 锁为 INTENTION_EXCLUSIVE
RC 隔离级别
insert 不冲突加锁
先看隔离级别
root@localhost [(none)] 23:26:23 > set global transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)] 23:33:18 > show global variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.01 sec)
全局是 READ-COMMITTED 隔离级别。
重新开 2 个会话
插入一条测试一下：
begin;
insert into insertlock values(3,'dd');
看看该事务加了什么锁
*************************** 1. row ***************************
PCL_ID: 17
THREAD_ID: OS:91969 ID:55 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344113
TRX_ISOLATION_LEVEL: READ COMMITTED
trx_state: RUNNING
trx_started: 2024-11-19 23:35:44
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:00:03
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:00:03
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 1
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、12、TABLE、IX、GRANTED
MDL_details:
ddl、insertlock、12、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
1 row in set (0.00 sec)
与上面相同，获取的是表的 IX 和 SHARED_WRITE 上面这里就不做解释
再开一个连接 开启会话事务看看加锁类型：
begin;
insert into insertlock values(4,'dd');
看看该事务加了什么锁
*************************** 1. row ***************************
PCL_ID: 18
THREAD_ID: OS:91852 ID:56 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344118
TRX_ISOLATION_LEVEL: READ COMMITTED
trx_state: RUNNING
trx_started: 2024-11-19 23:36:30
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:00:02
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:00:02
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(4,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 1
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、8、TABLE、IX、GRANTED
MDL_details:
ddl、insertlock、8、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
还是一样的表级别的 IX
锁，和 SHARED_WRITE
锁。
也是 MVCC 并发性的原因
insert 冲突加锁
下面看看 insert
冲突加什么锁：
在 连接 18 发出：
insert into insertlock values(3,'dd');
我们先看看连接 ID 14 的锁情况
*************************** 1. row ***************************
PCL_ID: 18
THREAD_ID: OS:91852 ID:56 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344118
TRX_ISOLATION_LEVEL: READ COMMITTED
trx_state: LOCK WAIT
trx_started: 2024-11-19 23:36:30
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:00:32
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 00:00:06
TRX_WAIT_STARTED: 2024-11-19 23:36:56
TRX_WAIT: 00:00:06
PROCESSLIST_STATE: update
PROCESSLIST_INFO: insert into insertlock values(3,'dd')
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 2
TRX_OPERATION_STATE: inserting
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、8、TABLE、IX、GRANTED
ddl、insertlock、9、PRIMARY、RECORD、S,REC_NOT_GAP、WAITING、3
MDL_details:
ddl、insertlock、8、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
9、GLOBAL、INTENTION_EXCLUSIVE、STATEMENT、GRANTED、sql_base.cc:3058
*************************** 2. row ***************************
PCL_ID: 17
THREAD_ID: OS:91969 ID:55 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344113
TRX_ISOLATION_LEVEL: READ COMMITTED
trx_state: RUNNING
trx_started: 2024-11-19 23:35:44
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:01:18
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:01:18
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 2
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、12、TABLE、IX、GRANTED
MDL_details:
ddl、insertlock、12、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
*************************** 3. row ***************************
PCL_ID: 17
THREAD_ID: OS:91969 ID:55 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344113
TRX_ISOLATION_LEVEL: READ COMMITTED
trx_state: RUNNING
trx_started: 2024-11-19 23:35:44
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:01:18
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:01:18
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 2
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、9、PRIMARY、RECORD、X,REC_NOT_GAP、GRANTED、3
MDL_details:
9、GLOBAL、INTENTION_EXCLUSIVE、STATEMENT、GRANTED、sql_base.cc:3058
3 rows in set (0.01 sec)
大家自己看看吧，这个刚才解释了，一样的。
RR 隔离级别
看看 RR 级别
set global transaction_isolation='REPEATABLE-READ';
insert 不冲突加锁
还是一样第一个会话插入
begin;
insert into insertlock values(3,'dd');
看看锁，连接 19
*************************** 1. row ***************************
PCL_ID: 19
THREAD_ID: OS:91969 ID:57 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344119
TRX_ISOLATION_LEVEL: REPEATABLE READ
trx_state: RUNNING
trx_started: 2024-11-19 23:39:39
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:00:04
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:00:04
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 1
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、10、TABLE、IX、GRANTED
MDL_details:
ddl、insertlock、10、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
1 row in set (0.01 sec)
再开一个连接 20 开启会话事务看看加锁类型：
begin;
insert into insertlock values(4,'dd');
看看该事务加了什么锁
*************************** 1. row ***************************
PCL_ID: 20
THREAD_ID: OS:91852 ID:58 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344124
TRX_ISOLATION_LEVEL: REPEATABLE READ
trx_state: RUNNING
trx_started: 2024-11-19 23:40:12
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:00:03
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:00:03
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(4,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 1
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、8、TABLE、IX、GRANTED
MDL_details:
ddl、insertlock、8、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
insert 冲突加锁
在连接 20 发出：
insert into insertlock values(3,'dd');
锁的信息也是相同的
*************************** 1. row ***************************
PCL_ID: 20
THREAD_ID: OS:91852 ID:58 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344124
TRX_ISOLATION_LEVEL: REPEATABLE READ
trx_state: LOCK WAIT
trx_started: 2024-11-19 23:40:12
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:00:28
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 00:00:03
TRX_WAIT_STARTED: 2024-11-19 23:40:37
TRX_WAIT: 00:00:03
PROCESSLIST_STATE: update
PROCESSLIST_INFO: insert into insertlock values(3,'dd')
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 2
TRX_OPERATION_STATE: inserting
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、8、TABLE、IX、GRANTED
ddl、insertlock、9、PRIMARY、RECORD、S,REC_NOT_GAP、WAITING、3
MDL_details:
ddl、insertlock、8、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
9、GLOBAL、INTENTION_EXCLUSIVE、STATEMENT、GRANTED、sql_base.cc:3058
*************************** 2. row ***************************
PCL_ID: 19
THREAD_ID: OS:91969 ID:57 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344119
TRX_ISOLATION_LEVEL: REPEATABLE READ
trx_state: RUNNING
trx_started: 2024-11-19 23:39:39
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:01:01
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:01:01
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 2
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、10、TABLE、IX、GRANTED
MDL_details:
ddl、insertlock、10、TABLE、SHARED_WRITE、TRANSACTION、GRANTED、sql_parse.cc:6094
*************************** 3. row ***************************
PCL_ID: 19
THREAD_ID: OS:91969 ID:57 parent_id:N/A
USER: root@localhost
DB: ddl
TRX_ID: 344119
TRX_ISOLATION_LEVEL: REPEATABLE READ
trx_state: RUNNING
trx_started: 2024-11-19 23:39:39
TRX_IS_READ_ONLY: 0
UNIQUE/FOREIGN CHECKS: 1/1
trx: 00:01:01
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 00:01:01
TRX_WAIT_STARTED: NULL
TRX_WAIT: NULL
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
last_statement: insert into insertlock values(3,'dd')
TRX_AUTOCOMMIT_NON_LOCKING: 0
TRX_LOCK_STRUCTS: 2
TRX_OPERATION_STATE: NULL
TRX_TABLES_LOCKED_ROW: 1
ADAPTIVE_HASH_LATCHED_TIMEOUT: 0/0
lock_details:
ddl、insertlock、9、PRIMARY、RECORD、X,REC_NOT_GAP、GRANTED、3
MDL_details:
9、GLOBAL、INTENTION_EXCLUSIVE、STATEMENT、GRANTED、sql_base.cc:3058
3 rows in set (0.01 sec)
总结
SERIALIZABLE 隔离级别也是一样的这里不再赘述。
insert 语句插入表中需要加表级别的 IX
锁和事务级别的 SHARED_WRITE
锁。
为了阻塞其他事务而操作同一个值就会为该主键值加的的主键非间隙记录锁，防止对该主键值做操作。同时有 server 层的 INTENTION_EXCLUSIVE
，发现其他事务要操作主键值为相同的记录所以将 SHARED_WRITE
→ INTENTION_EXCLUSIVE
。
往期回顾
MySQL 临时表空间 - 临时表空间（Temporary Tablespaces 及相关的变量 MySQL/PG 数据脱敏 - data_masking 插件和 postgresql_anonymizer 扩展初体验 MySQL/PG 对事务 DDL 的支持程度 - 从隐式提交聊到DDL事务 ONLINE DDL 收尾篇|如何有效实现 ONLINE - 关于 ONLINE DDL 的最佳实践 MySQL/PG 索引对于排序规则的依赖性 - 排序规则与索引的相关性问题，涉及到索引是否会失效 如何正确杀会话， KILL processlist_id/KILL QUERY？ - 你知道如何正确杀掉活跃会话吗？
感谢阅读！关注我获取更多精彩内容。
文章转载自00后DBA实录，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
