不同于Oracle：MySQL的insert会阻塞update

• GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
• GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
• 作者： 王庆勋
• 文章来源：GreatSQL社区原创

某银行客户在从Oracle迁移到MySQL的开发中，MySQL在READ-COMMITTED隔离级别下，出现了insert阻塞update的情况，但同样的情况下，Oracle的insert则不会阻塞update。本文通过复现该问题，分析MySQL的锁信息，确认是MySQL与Oracle在并发事务处理上的差异，在进行数据库迁移改造的程序开发应予以关注。

1.问题复现

1.1.环境准备

MySQL版本的8.0.26，隔离级别是READ-COMMITTED ，测试表t的字段a为主键。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.02 sec)

mysql> select * from t;
+---+------+
| a | b |
+---+------+
| 7 | 7 |
+---+------+
1 row in set (0.00 sec)

1.2. insert阻塞update的操作步骤

insert语句未提交时，update同样主键的数据会被阻塞。

2.分析原因

2.1.检查事务锁信息

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 3795
trx_state: LOCK WAIT
trx_started: 2022-10-11 16:03:38
trx_requested_lock_id: 139727275779216:52:4:3:139724882995456
trx_wait_started: 2022-10-11 16:03:38
trx_weight: 2
trx_mysql_thread_id: 9346
trx_query: update t set b=0 where a=8
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1128
trx_rows_locked: 1
trx_rows_modified: 0
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: 1
*************************** 2. row ***************************
trx_id: 3790
trx_state: RUNNING
trx_started: 2022-10-11 16:03:29
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 9320
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1128
trx_rows_locked: 1
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
2 rows in set (0.00 sec)

mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2022-10-11 16:03:38
wait_age: 00:02:50
wait_age_secs: 170
locked_table: `testdb`.`t`
locked_table_schema: testdb
locked_table_name: t
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 3795
waiting_trx_started: 2022-10-11 16:03:38
waiting_trx_age: 00:02:50
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 9346
waiting_query: update t set b=0 where a=8
waiting_lock_id: 139727275779216:52:4:3:139724882995456
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 3790
blocking_pid: 9320
blocking_query: NULL
blocking_lock_id: 139727275781640:52:4:3:139724883013968
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2022-10-11 16:03:29
blocking_trx_age: 00:02:59
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 9320
sql_kill_blocking_connection: KILL 9320
1 row in set (0.01 sec)
说明：事务3795等待testdb.t上的rec_not_gap独占锁，事务3790持有该独占锁。
mysql> select distinct,* from sys.processlist where conn_id in (select trx_mysql_thread_id from information_schema.innodb_trx)\G
*************************** 1. row ***************************
thd_id: 9441
conn_id: 9346
user: admin@172.17.128.73
db: testdb
command: Query
state: updating
time: 141
current_statement: update t set b=0 where a=8
statement_latency: 2.37 min
progress: NULL
lock_latency: 431.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 140.15 KiB
last_wait: wait/io/table/sql/handler
last_wait_latency: Still Waiting
source: handler.cc:3250
trx_latency: 13.30 min
trx_state: ACTIVE
trx_autocommit: NO
pid: 9632
program_name: mysql
*************************** 2. row ***************************
thd_id: 9415
conn_id: 9320
user: admin@172.17.128.73
db: testdb
command: Sleep
state: NULL
time: 801
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 288.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 1
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: insert into t values(8,8)
last_statement_latency: 765.23 us
current_memory: 218.19 KiB
last_wait: wait/io/socket/sql/client_connection
last_wait_latency: Still Waiting
source: viosocket.cc:146
trx_latency: 13.52 min
trx_state: ACTIVE
trx_autocommit: NO
pid: 9600
program_name: mysql
说明：被阻塞事务执行的sql语句update t set b=0 where a=8，
阻塞事务执行的sql语句是insert into t values(8,8)。

说明：

MySQL的隔离级别是通过索引上的锁实现并发事务控制的。在READ-COMMITTED隔离级别下，session1在执行insert语句时，在主键索引上获取了a=8的行记录独占锁，以禁止插入相同主键的数据；session2如果同时插入相同的主键数据被阻塞，容易理解（Oracle也同样阻塞）。出于同样的原因session2执行update时，由于无法获取a=8的行记录独占锁，同样也会被阻塞。

2.2.验证MySQL事务未提交时已写入数据文件

验证事务未提交时，insert语句已将数据写入数据文件，索引数据也已生成。

测试表test1
mysql> CREATE TABLE `test1` (
-> `id` int NOT NULL AUTO_INCREMENT,
-> `k` int NOT NULL DEFAULT '0',
-> `c` char(120) NOT NULL DEFAULT '',
-> `pad` char(60) NOT NULL DEFAULT '',
-> PRIMARY KEY (`id`),
-> KEY `k_1` (`k`)
-> ) ;
Query OK, 0 rows affected (0.07 sec)

检查数据文件的大小
[root@host73 testdb]# ll *test1.ibd
-rw-r----- 1 greatdb greatdb 30408704 Oct 11 15:12 sbtest1.ibd
-rw-r----- 1 greatdb greatdb 33554432 Oct 11 22:24 test1.ibd

说明：

MySQL在执行insert 语句进行数据插入，未提交时，数据也已写入表的聚集索引，辅助索引也已生成。MySQL可以使用未提交数据的索引，通过锁机制实现事务的并发控制。

3.Oracle中insert没有阻塞update

在Oracle中，创建同样的测试表t，执行同样的insert和update，但insert不会阻塞update。

CREATE TABLE t (
a int NOT NULL PRIMARY KEY ,
b int DEFAULT NULL
);
insert into t values(7,7);
commit;

执行相同的insert和update语句。