不同于 Oracle：MySQL 的 insert 会阻塞 update
作者：GreatSQL
- 2023-03-28 福建
本文字数：6383 字
阅读完需：约 21 分钟
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> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | READ-COMMITTED |+-----------------------+----------------+1 row in set (0.00 sec)
mysql> desc t;+-------+------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------+------+-----+---------+-------+| a | int | NO | PRI | NULL | || b | int | YES | | NULL | |+-------+------+------+-----+---------+-------+2 rows in set (0.01 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: 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-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: 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 in set (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 in set (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-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 9320sql_kill_blocking_connection: KILL 93201 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: 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: 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 语句已将数据写入数据文件，索引数据也已生成。
测试表test1mysql> 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)
开启一个事务，插入10万条数据。mysql> begin;Query OK, 0 rows affected (0.00 sec)
mysql> insert into 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-11 22:14:50 Update_time: NULL Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 rows in set (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-11 22:22:36 Update_time: NULL Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (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。
CREATE TABLE t ( a int NOT NULL PRIMARY KEY , b int DEFAULT NULL);insert into t values(7,7);commit;
复制代码
执行相同的 insert 和 update 语句。
Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL 是由万里数据库维护的 MySQL 分支，专注于提升 MGR 可靠性及性能，支持 InnoDB 并行查询特性，是适用于金融级应用的 MySQL 分支版本。
相关链接： GreatSQL社区 Gitee GitHub Bilibili
GreatSQL 社区：
社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html
技术交流群：
微信：扫码添加
GreatSQL社区助手微信好友，发送验证信息加群。
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【GreatSQL】的原创文章。
原文链接:【http://xie.infoq.cn/article/702d48f625c359fc3f1c1b9e9】。
本文遵守【CC-BY 4.0】协议，转载请保留原文出处及本版权声明。
GreatSQL
关注
GreatSQL社区 2023-01-31 加入
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL
评论