MySQL insert会阻塞update怎么解决

这篇文章主要介绍“MySQL insert会阻塞update怎么解决”，在日常操作中，相信很多人在MySQL insert会阻塞update怎么解决问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL insert会阻塞update怎么解决”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

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分支版本。

到此，关于“MySQL insert会阻塞update怎么解决”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注编程网网站，小编会继续努力为大家带来更多实用的文章！