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

某银行客户在从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 inset(0.02 sec)mysql> show variables like'transaction_isolation';+-----------------------+----------------+| Variable_name         | Value          |+-----------------------+----------------+| transaction_isolation | READ-COMMITTED |+-----------------------+----------------+1 row inset(0.00 sec)mysql>desc t;+-------+------+------+-----+---------+-------+| Field | Type |Null| Key | Default | Extra |+-------+------+------+-----+---------+-------+| a     |int| NO   | PRI |NULL||| b     |int| YES  ||NULL||+-------+------+------+-----+---------+-------+2 rows inset(0.01 sec)mysql>select*from t;+---+------+| a | b    |+---+------+|7|7|+---+------+1 row inset(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-1116:03:38     trx_requested_lock_id:139727275779216:52:4:3:139724882995456          trx_wait_started:2022-10-1116:03:38                trx_weight:2       trx_mysql_thread_id:9346                 trx_query:update t set b=0where 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:1trx_last_foreign_key_error:NULL trx_adaptive_hash_latched:0 trx_adaptive_hash_timeout:0          trx_is_read_only:0trx_autocommit_non_locking:0       trx_schedule_weight:1***************************2. row ***************************                    trx_id:3790                 trx_state: RUNNING               trx_started:2022-10-1116: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:1trx_last_foreign_key_error:NULL trx_adaptive_hash_latched:0 trx_adaptive_hash_timeout:0          trx_is_read_only:0trx_autocommit_non_locking:0       trx_schedule_weight:NULL2 rows inset(0.00 sec)说明：通过InnoDB的事务表innodb_trx查询到thread_id=9346的事务3795正在等待锁（trx_state: LOCK WAIT）,thread_id=9320的事务3790正在执行（trx_state: RUNNING）。mysql>select*from performance_schema.data_locks\G***************************1. row ***************************               ENGINE: INNODB       ENGINE_LOCK_ID:139727275779216:1113:139724882998560ENGINE_TRANSACTION_ID:3795            THREAD_ID:9441             EVENT_ID:5000        OBJECT_SCHEMA: testdb          OBJECT_NAME: t       PARTITION_NAME:NULL    SUBPARTITION_NAME:NULL           INDEX_NAME:NULLOBJECT_INSTANCE_BEGIN:139724882998560            LOCK_TYPE:TABLE            LOCK_MODE: IX          LOCK_STATUS: GRANTED            LOCK_DATA:NULL***************************2. row ***************************               ENGINE: INNODB       ENGINE_LOCK_ID:139727275779216:52:4:3:139724882995456ENGINE_TRANSACTION_ID:3795            THREAD_ID:9441             EVENT_ID:5012        OBJECT_SCHEMA: testdb          OBJECT_NAME: t       PARTITION_NAME:NULL    SUBPARTITION_NAME:NULL           INDEX_NAME: PRIMARYOBJECT_INSTANCE_BEGIN:139724882995456            LOCK_TYPE: RECORD            LOCK_MODE: X,REC_NOT_GAP          LOCK_STATUS: WAITING            LOCK_DATA:8***************************3. row ***************************               ENGINE: INNODB       ENGINE_LOCK_ID:139727275781640:1113:139724883017072ENGINE_TRANSACTION_ID:3790            THREAD_ID:9415             EVENT_ID:15467        OBJECT_SCHEMA: testdb          OBJECT_NAME: t       PARTITION_NAME:NULL    SUBPARTITION_NAME:NULL           INDEX_NAME:NULLOBJECT_INSTANCE_BEGIN:139724883017072            LOCK_TYPE:TABLE            LOCK_MODE: IX          LOCK_STATUS: GRANTED            LOCK_DATA:NULL***************************4. row ***************************               ENGINE: INNODB       ENGINE_LOCK_ID:139727275781640:52:4:3:139724883013968ENGINE_TRANSACTION_ID:3790            THREAD_ID:9441             EVENT_ID:5007        OBJECT_SCHEMA: testdb          OBJECT_NAME: t       PARTITION_NAME:NULL    SUBPARTITION_NAME:NULL           INDEX_NAME: PRIMARYOBJECT_INSTANCE_BEGIN:139724883013968            LOCK_TYPE: RECORD            LOCK_MODE: X,REC_NOT_GAP          LOCK_STATUS: GRANTED            LOCK_DATA:84 rows inset(0.00 sec)说明：事务3795正在等待LOCK_TYPE: RECORD，LOCK_MODE:X,REC_NOT_GAP，等待的主键值为8；事务3790已获取主键值为8的LOCK_TYPE: RECORD，LOCK_MODE:X,REC_NOT_GAP锁。mysql>select*from sys.innodb_lock_waits\G***************************1. row ***************************                wait_started:2022-10-1116: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-1116: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=0where 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-1116:03:29            blocking_trx_age:00:02:59    blocking_trx_rows_locked:1  blocking_trx_rows_modified:1     sql_kill_blocking_query: KILL QUERY 9320sql_kill_blocking_connection: KILL 93201 row inset(0.01 sec)说明：事务3795等待testdb.t上的rec_not_gap独占锁，事务3790持有该独占锁。mysql>selectdistinct,*from sys.processlistwhere 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: updatingtime:141     current_statement:update t set b=0where 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:NULLlast_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:NULLtime: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:insertinto 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=0where 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语句已将数据写入数据文件，索引数据也已生成。

测试表test1mysql>CREATETABLE `test1` (->   `id` intNOTNULL AUTO_INCREMENT,->   `k` intNOTNULL DEFAULT '0',->   `c` char(120)NOTNULL DEFAULT '',->   `pad` char(60)NOTNULL DEFAULT '',->   PRIMARY KEY (`id`),->   KEY `k_1` (`k`)->);Query OK,0 rows affected (0.07 sec)开启一个事务，插入10万条数据。mysql>begin;Query OK,0 rows affected (0.00 sec)mysql>insertinto test1 select*from sbtest1;Query OK,100000 rows affected (1.44 sec)Records:100000  Duplicates:0  Warnings:0检查表的data_length和index_lengthmysql> show table status where name like'test1'\G***************************1. row ***************************           Name: test1         Engine: InnoDB        Version:10     Row_format: Dynamic           Rows:98712 Avg_row_length:228    Data_length:22593536Max_data_length:0   Index_length:2637824      Data_free:4194304 Auto_increment:100001    Create_time:2022-10-1122:14:50    Update_time:NULL     Check_time:NULL      Collation: utf8mb4_0900_ai_ci       Checksum:NULL Create_options:        Comment:1 rows inset(0.01 sec)回滚insert操作mysql> rollback;Query OK,0 rows affected (1.35 sec)更新统计信息mysql> analyze table test1;再次检查表的data_length和index_lengthmysql> show table status where name like'test1'\G ***************************1. row ***************************           Name: test1         Engine: InnoDB        Version:10     Row_format: Dynamic           Rows:0 Avg_row_length:0    Data_length:16384Max_data_length:0   Index_length:16384      Data_free:29360128 Auto_increment:100001    Create_time:2022-10-1122:22:36    Update_time:NULL     Check_time:NULL      Collation: utf8mb4_0900_ai_ci       Checksum:NULL Create_options:        Comment:1 row inset(0.00 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。

CREATETABLE t (  a intNOTNULL PRIMARY KEY ,  b int DEFAULT NULL);insertinto t values(7,7);commit;

执行相同的insert和update语句。