免费资源部落

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

1.问题复现1.1.环境准备

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

mysql>selectversion();+-----------+|version()|+-----------+|8.0.26|+-----------+1rowinset(0.02sec)mysql>showvariableslike'transaction_isolation';+-----------------------+----------------+|Variable_name|Value|+-----------------------+----------------+|transaction_isolation|READ-COMMITTED|+-----------------------+----------------+1rowinset(0.00sec)mysql>desct;+-------+------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+------+------+-----+---------+-------+|a|int|NO|PRI|NULL|||b|int|YES||NULL||+-------+------+------+-----+---------+-------+2rowsinset(0.01sec)mysql>select*fromt;+---+------+|a|b|+---+------+|7|7|+---+------+1rowinset(0.00sec)1.2. insert阻塞update的操作步骤

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

session1session2插入一条数据(a=8)后未提交。 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values(8,8); Query OK, 1 row affected (0.01 sec)更改数据，条件是a=8,将会被阻塞 mysql> update t set b=0 where a=8; <<挂起，等待innodb_lock_wait_timeout超时2.分析原因2.1.检查事务锁信息mysql&gt;select*frominformation_schema.innodb_trx\G***************************1.row***************************trx_id:3795trx_state:LOCKWAITtrx_started:2022-10-1116:03:38trx_requested_lock_id:139727275779216:52:4:3:139724882995456trx_wait_started:2022-10-1116:03:38trx_weight:2trx_mysql_thread_id:9346trx_query:updatetsetb=0wherea=8trx_operation_state:startingindexreadtrx_tables_in_use:1trx_tables_locked:1trx_lock_structs:2trx_lock_memory_bytes:1128trx_rows_locked:1trx_rows_modified:0trx_concurrency_tickets:0trx_isolation_level:READCOMMITTEDtrx_unique_checks:1trx_foreign_key_checks:1trx_last_foreign_key_error:NULLtrx_adaptive_hash_latched:0trx_adaptive_hash_timeout:0trx_is_read_only:0trx_autocommit_non_locking:0trx_schedule_weight:1***************************2.row***************************trx_id:3790trx_state:RUNNINGtrx_started:2022-10-1116:03:29trx_requested_lock_id:NULLtrx_wait_started:NULLtrx_weight:3trx_mysql_thread_id:9320trx_query:NULLtrx_operation_state:NULLtrx_tables_in_use:0trx_tables_locked:1trx_lock_structs:2trx_lock_memory_bytes:1128trx_rows_locked:1trx_rows_modified:1trx_concurrency_tickets:0trx_isolation_level:READCOMMITTEDtrx_unique_checks:1trx_foreign_key_checks:1trx_last_foreign_key_error:NULLtrx_adaptive_hash_latched:0trx_adaptive_hash_timeout:0trx_is_read_only:0trx_autocommit_non_locking:0trx_schedule_weight:NULL2rowsinset(0.00sec)说明：通过InnoDB的事务表innodb_trx查询到thread_id=9346的事务3795正在等待锁（trx_state:LOCKWAIT）,thread_id=9320的事务3790正在执行（trx_state:RUNNING）。mysql&gt;select*fromperformance_schema.data_locks\G***************************1.row***************************ENGINE:INNODBENGINE_LOCK_ID:139727275779216:1113:139724882998560ENGINE_TRANSACTION_ID:3795THREAD_ID:9441EVENT_ID:5000OBJECT_SCHEMA:testdbOBJECT_NAME:tPARTITION_NAME:NULLSUBPARTITION_NAME:NULLINDEX_NAME:NULLOBJECT_INSTANCE_BEGIN:139724882998560LOCK_TYPE:TABLELOCK_MODE:IXLOCK_STATUS:GRANTEDLOCK_DATA:NULL***************************2.row***************************ENGINE:INNODBENGINE_LOCK_ID:139727275779216:52:4:3:139724882995456ENGINE_TRANSACTION_ID:3795THREAD_ID:9441EVENT_ID:5012OBJECT_SCHEMA:testdbOBJECT_NAME:tPARTITION_NAME:NULLSUBPARTITION_NAME:NULLINDEX_NAME:PRIMARYOBJECT_INSTANCE_BEGIN:139724882995456LOCK_TYPE:RECORDLOCK_MODE:X,REC_NOT_GAPLOCK_STATUS:WAITINGLOCK_DATA:8***************************3.row***************************ENGINE:INNODBENGINE_LOCK_ID:139727275781640:1113:139724883017072ENGINE_TRANSACTION_ID:3790THREAD_ID:9415EVENT_ID:15467OBJECT_SCHEMA:testdbOBJECT_NAME:tPARTITION_NAME:NULLSUBPARTITION_NAME:NULLINDEX_NAME:NULLOBJECT_INSTANCE_BEGIN:139724883017072LOCK_TYPE:TABLELOCK_MODE:IXLOCK_STATUS:GRANTEDLOCK_DATA:NULL***************************4.row***************************ENGINE:INNODBENGINE_LOCK_ID:139727275781640:52:4:3:139724883013968ENGINE_TRANSACTION_ID:3790THREAD_ID:9441EVENT_ID:5007OBJECT_SCHEMA:testdbOBJECT_NAME:tPARTITION_NAME:NULLSUBPARTITION_NAME:NULLINDEX_NAME:PRIMARYOBJECT_INSTANCE_BEGIN:139724883013968LOCK_TYPE:RECORDLOCK_MODE:X,REC_NOT_GAPLOCK_STATUS:GRANTEDLOCK_DATA:84rowsinset(0.00sec)说明：事务3795正在等待LOCK_TYPE:RECORD，LOCK_MODE:X,REC_NOT_GAP，等待的主键值为8；事务3790已获取主键值为8的LOCK_TYPE:RECORD，LOCK_MODE:X,REC_NOT_GAP锁。mysql&gt;select*fromsys.innodb_lock_waits\G***************************1.row***************************wait_started:2022-10-1116:03:38wait_age:00:02:50wait_age_secs:170locked_table:`testdb`.`t`locked_table_schema:testdblocked_table_name:tlocked_table_partition:NULLlocked_table_subpartition:NULLlocked_index:PRIMARYlocked_type:RECORDwaiting_trx_id:3795waiting_trx_started:2022-10-1116:03:38waiting_trx_age:00:02:50waiting_trx_rows_locked:1waiting_trx_rows_modified:0waiting_pid:9346waiting_query:updatetsetb=0wherea=8waiting_lock_id:139727275779216:52:4:3:139724882995456waiting_lock_mode:X,REC_NOT_GAPblocking_trx_id:3790blocking_pid:9320blocking_query:NULLblocking_lock_id:139727275781640:52:4:3:139724883013968blocking_lock_mode:X,REC_NOT_GAPblocking_trx_started:2022-10-1116:03:29blocking_trx_age:00:02:59blocking_trx_rows_locked:1blocking_trx_rows_modified:1sql_kill_blocking_query:KILLQUERY9320sql_kill_blocking_connection:KILL93201rowinset(0.01sec)说明：事务3795等待testdb.t上的rec_not_gap独占锁，事务3790持有该独占锁。mysql&gt;selectdistinct,*fromsys.processlistwhereconn_idin(selecttrx_mysql_thread_idfrominformation_schema.innodb_trx)\G***************************1.row***************************thd_id:9441conn_id:9346user:admin@172.17.128.73db:testdbcommand:Querystate:updatingtime:141current_statement:updatetsetb=0wherea=8statement_latency:2.37minprogress:NULLlock_latency:431.00usrows_examined:0rows_sent:0rows_affected:0tmp_tables:0tmp_disk_tables:0full_scan:NOlast_statement:NULLlast_statement_latency:NULLcurrent_memory:140.15KiBlast_wait:wait/io/table/sql/handlerlast_wait_latency:StillWaitingsource:handler.cc:3250trx_latency:13.30mintrx_state:ACTIVEtrx_autocommit:NOpid:9632program_name:mysql***************************2.row***************************thd_id:9415conn_id:9320user:admin@172.17.128.73db:testdbcommand:Sleepstate:NULLtime:801current_statement:NULLstatement_latency:NULLprogress:NULLlock_latency:288.00usrows_examined:0rows_sent:0rows_affected:1tmp_tables:0tmp_disk_tables:0full_scan:NOlast_statement:insertintotvalues(8,8)last_statement_latency:765.23uscurrent_memory:218.19KiBlast_wait:wait/io/socket/sql/client_connectionlast_wait_latency:StillWaitingsource:viosocket.cc:146trx_latency:13.52mintrx_state:ACTIVEtrx_autocommit:NOpid:9600program_name:mysql说明：被阻塞事务执行的sql语句updatetsetb=0wherea=8，阻塞事务执行的sql语句是insertintotvalues(8,8)。

说明：

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

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

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

测试表test1mysql&gt;CREATETABLE`test1`(-&gt;`id`intNOTNULLAUTO_INCREMENT,-&gt;`k`intNOTNULLDEFAULT'0',-&gt;`c`char(120)NOTNULLDEFAULT'',-&gt;`pad`char(60)NOTNULLDEFAULT'',-&gt;PRIMARYKEY(`id`),-&gt;KEY`k_1`(`k`)-&gt;);QueryOK,0rowsaffected(0.07sec)开启一个事务，插入10万条数据。mysql&gt;begin;QueryOK,0rowsaffected(0.00sec)mysql&gt;insertintotest1select*fromsbtest1;QueryOK,100000rowsaffected(1.44sec)Records:100000Duplicates:0Warnings:0检查表的data_length和index_lengthmysql&gt;showtablestatuswherenamelike'test1'\G***************************1.row***************************Name:test1Engine:InnoDBVersion:10Row_format:DynamicRows:98712Avg_row_length:228Data_length:22593536Max_data_length:0Index_length:2637824Data_free:4194304Auto_increment:100001Create_time:2022-10-1122:14:50Update_time:NULLCheck_time:NULLCollation:utf8mb4_0900_ai_ciChecksum:NULLCreate_options:Comment:1rowsinset(0.01sec)回滚insert操作mysql&gt;rollback;QueryOK,0rowsaffected(1.35sec)更新统计信息mysql&gt;analyzetabletest1;再次检查表的data_length和index_lengthmysql&gt;showtablestatuswherenamelike'test1'\G***************************1.row***************************Name:test1Engine:InnoDBVersion:10Row_format:DynamicRows:0Avg_row_length:0Data_length:16384Max_data_length:0Index_length:16384Data_free:29360128Auto_increment:100001Create_time:2022-10-1122:22:36Update_time:NULLCheck_time:NULLCollation:utf8mb4_0900_ai_ciChecksum:NULLCreate_options:Comment:1rowinset(0.00sec)检查数据文件的大小[root@host73testdb]#ll*test1.ibd-rw-r-----1greatdbgreatdb30408704Oct1115:12sbtest1.ibd-rw-r-----1greatdbgreatdb33554432Oct1122:24test1.ibd

说明：

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

3.Oracle中insert没有阻塞update

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

CREATETABLEt(aintNOTNULLPRIMARYKEY,bintDEFAULTNULL);insertintotvalues(7,7);commit;

执行相同的insert和update语句。

session1session2SQL> insert into t values(8,8);1 row created.SQL> update t set b=0 where a=8;0 rows updated.---

Enjoy GreatSQL

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。

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