溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“MySQL insert會(huì)阻塞update怎么解決”，在日常操作中，相信很多人在MySQL insert會(huì)阻塞update怎么解決問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對(duì)大家解答”MySQL insert會(huì)阻塞update怎么解決”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！
MySQL版本的8.0.26，隔離級(jí)別是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)
insert語句未提交時(shí)，update同樣主鍵的數(shù)據(jù)會(huì)被阻塞。
| session1 | session2 |
|---|---|
| 插入一條數(shù)據(jù)(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) | |
| 更改數(shù)據(jù)，條件是a=8,將會(huì)被阻塞 mysql> update t set b=0 where a=8; <<掛起，等待innodb_lock_wait_timeout超時(shí) |
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) 說明：通過InnoDB的事務(wù)表innodb_trx查詢到thread_id=9346的事務(wù)3795正在等待鎖（trx_state: LOCK WAIT）, thread_id=9320的事務(wù)3790正在執(zhí)行（trx_state: RUNNING）。 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139727275779216:1113:139724882998560 ENGINE_TRANSACTION_ID: 3795 THREAD_ID: 9441 EVENT_ID: 5000 OBJECT_SCHEMA: testdb OBJECT_NAME: t PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_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:139724882995456 ENGINE_TRANSACTION_ID: 3795 THREAD_ID: 9441 EVENT_ID: 5012 OBJECT_SCHEMA: testdb OBJECT_NAME: t PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_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:139724883017072 ENGINE_TRANSACTION_ID: 3790 THREAD_ID: 9415 EVENT_ID: 15467 OBJECT_SCHEMA: testdb OBJECT_NAME: t PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_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:139724883013968 ENGINE_TRANSACTION_ID: 3790 THREAD_ID: 9441 EVENT_ID: 5007 OBJECT_SCHEMA: testdb OBJECT_NAME: t PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139724883013968 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 8 4 rows in set (0.00 sec) 說明：事務(wù)3795正在等待LOCK_TYPE: RECORD，LOCK_MODE:X,REC_NOT_GAP，等待的主鍵值為8； 事務(wù)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 9320 sql_kill_blocking_connection: KILL 9320 1 row in set (0.01 sec) 說明：事務(wù)3795等待testdb.t上的rec_not_gap獨(dú)占鎖，事務(wù)3790持有該獨(dú)占鎖。 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 說明：被阻塞事務(wù)執(zhí)行的sql語句update t set b=0 where a=8， 阻塞事務(wù)執(zhí)行的sql語句是insert into t values(8,8)。
說明：
MySQL的隔離級(jí)別是通過索引上的鎖實(shí)現(xiàn)并發(fā)事務(wù)控制的。在READ-COMMITTED隔離級(jí)別下，session1在執(zhí)行insert語句時(shí)，在主鍵索引上獲取了a=8的行記錄獨(dú)占鎖，以禁止插入相同主鍵的數(shù)據(jù)；session2如果同時(shí)插入相同的主鍵數(shù)據(jù)被阻塞，容易理解（Oracle也同樣阻塞）。出于同樣的原因session2執(zhí)行update時(shí)，由于無法獲取a=8的行記錄獨(dú)占鎖，同樣也會(huì)被阻塞。
驗(yàn)證事務(wù)未提交時(shí)，insert語句已將數(shù)據(jù)寫入數(shù)據(jù)文件，索引數(shù)據(jù)也已生成。
測試表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) 開啟一個(gè)事務(wù)，插入10萬條數(shù)據(jù)。 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_length mysql> 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: 22593536 Max_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) 更新統(tǒng)計(jì)信息 mysql> analyze table test1; 再次檢查表的data_length和index_length mysql> 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: 16384 Max_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) 檢查數(shù)據(jù)文件的大小 [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在執(zhí)行insert 語句進(jìn)行數(shù)據(jù)插入，未提交時(shí)，數(shù)據(jù)也已寫入表的聚集索引，輔助索引也已生成。MySQL可以使用未提交數(shù)據(jù)的索引，通過鎖機(jī)制實(shí)現(xiàn)事務(wù)的并發(fā)控制。
在Oracle中，創(chuàng)建同樣的測試表t，執(zhí)行同樣的insert和update，但insert不會(huì)阻塞update。
CREATE TABLE t ( a int NOT NULL PRIMARY KEY , b int DEFAULT NULL ); insert into t values(7,7); commit;
執(zhí)行相同的insert和update語句。
| session1 | session2 |
|---|---|
| SQL> insert into t values(8,8); | |
| 1 row created. | SQL> update t set b=0 where a=8;0 rows updated. |
| --- |
Enjoy GreatSQL :)
GreatSQL是由萬里數(shù)據(jù)庫維護(hù)的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級(jí)應(yīng)用的MySQL分支版本。
到此，關(guān)于“MySQL insert會(huì)阻塞update怎么解決”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
