區(qū)別于Oracle的MySQL?insert會(huì)阻塞update
更新時(shí)間：2023年03月29日 08:58:46 作者：GreatSQL
這篇文章主要介紹了區(qū)別于Oracle的MySQL?insert會(huì)阻塞update的問(wèn)題解決，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪
正文
- GreatSQL社區(qū)原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請(qǐng)聯(lián)系小編并注明來(lái)源。
- GreatSQL是MySQL的國(guó)產(chǎn)分支版本，使用上與MySQL一致。
- 作者： 王慶勛
- 文章來(lái)源：GreatSQL社區(qū)原創(chuàng)
某銀行客戶在從Oracle遷移到MySQL的開發(fā)中，MySQL在READ-COMMITTED隔離級(jí)別下，出現(xiàn)了insert阻塞update的情況，但同樣的情況下，Oracle的insert則不會(huì)阻塞update。本文通過(guò)復(fù)現(xiàn)該問(wèn)題，分析MySQL的鎖信息，確認(rèn)是MySQL與Oracle在并發(fā)事務(wù)處理上的差異，在進(jìn)行數(shù)據(jù)庫(kù)遷移改造的程序開發(fā)應(yīng)予以關(guān)注。
1.問(wèn)題復(fù)現(xiàn)
1.1.環(huán)境準(zhǔn)備
MySQL版本的8.0.26，隔離級(jí)別是READ-COMMITTED ，測(cè)試表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語(yǔ)句未提交時(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í) |
2.分析原因
2.1.檢查事務(wù)鎖信息
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)
說(shuō)明：通過(guò)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)
說(shuō)明：事務(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)
說(shuō)明：事務(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
說(shuō)明：被阻塞事務(wù)執(zhí)行的sql語(yǔ)句update t set b=0 where a=8，
阻塞事務(wù)執(zhí)行的sql語(yǔ)句是insert into t values(8,8)。
說(shuō)明：
MySQL的隔離級(jí)別是通過(guò)索引上的鎖實(shí)現(xiàn)并發(fā)事務(wù)控制的。在READ-COMMITTED隔離級(jí)別下，session1在執(zhí)行insert語(yǔ)句時(shí)，在主鍵索引上獲取了a=8的行記錄獨(dú)占鎖，以禁止插入相同主鍵的數(shù)據(jù)；session2如果同時(shí)插入相同的主鍵數(shù)據(jù)被阻塞，容易理解（Oracle也同樣阻塞）。出于同樣的原因session2執(zhí)行update時(shí)，由于無(wú)法獲取a=8的行記錄獨(dú)占鎖，同樣也會(huì)被阻塞。
2.2.驗(yàn)證MySQL事務(wù)未提交時(shí)已寫入數(shù)據(jù)文件
驗(yàn)證事務(wù)未提交時(shí)，insert語(yǔ)句已將數(shù)據(jù)寫入數(shù)據(jù)文件，索引數(shù)據(jù)也已生成。
測(cè)試表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萬(wàn)條數(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
說(shuō)明：
MySQL在執(zhí)行insert 語(yǔ)句進(jìn)行數(shù)據(jù)插入，未提交時(shí)，數(shù)據(jù)也已寫入表的聚集索引，輔助索引也已生成。MySQL可以使用未提交數(shù)據(jù)的索引，通過(guò)鎖機(jī)制實(shí)現(xiàn)事務(wù)的并發(fā)控制。
3.Oracle中insert沒(méi)有阻塞update
在Oracle中，創(chuàng)建同樣的測(cè)試表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語(yǔ)句。
| 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 :)
關(guān)于 GreatSQL
GreatSQL是由萬(wàn)里數(shù)據(jù)庫(kù)維護(hù)的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級(jí)應(yīng)用的MySQL分支版本。
相關(guān)鏈接：
以上就是區(qū)別于Oracle的MySQL insert會(huì)阻塞update的詳細(xì)內(nèi)容，更多關(guān)于MySQL insert阻塞update的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
mysql導(dǎo)入導(dǎo)出數(shù)據(jù)中文亂碼解決方法小結(jié)
本文章總結(jié)了mysql導(dǎo)入導(dǎo)出數(shù)據(jù)中文亂碼解決方法，出現(xiàn)中文亂碼一般情況是導(dǎo)入導(dǎo)入時(shí)編碼的設(shè)置問(wèn)題，我們只要把編碼調(diào)整一致即可解決此方法，下面是搜索到的一些方法總結(jié),方便需要的朋友2012-10-10
mysql一對(duì)多關(guān)聯(lián)查詢分頁(yè)錯(cuò)誤問(wèn)題的解決方法
這篇文章主要介紹了mysql一對(duì)多關(guān)聯(lián)查詢分頁(yè)錯(cuò)誤問(wèn)題的解決方法,非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2018-09-09
這篇文章主要介紹了mysql-5.7.28 的Linux安裝，本文通過(guò)圖文并茂的形式給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-10-10
通過(guò)存儲(chǔ)過(guò)程動(dòng)態(tài)創(chuàng)建MySQL對(duì)象的流程步驟
在當(dāng)今數(shù)據(jù)驅(qū)動(dòng)的世界中,高效的數(shù)據(jù)庫(kù)管理至關(guān)重要,本文將展示如何通過(guò)存儲(chǔ)過(guò)程自動(dòng)化地創(chuàng)建各種?MySQL?數(shù)據(jù)庫(kù)對(duì)象,通過(guò)這些方法,我們可以快速響應(yīng)業(yè)務(wù)需求,提高數(shù)據(jù)庫(kù)管理的靈活性和效率,需要的朋友可以參考下2024-10-10
這篇文章主要介紹了MySql Date函數(shù)的相關(guān)知識(shí)，非常不錯(cuò)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2018-06-06
刪除MySQL數(shù)據(jù)庫(kù)的簡(jiǎn)單教程
這篇文章主要介紹了刪除MySQL數(shù)據(jù)庫(kù)的簡(jiǎn)單教程,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下2015-05-05
mysql踩坑之count distinct多列問(wèn)題
這篇文章主要介紹了mysql踩坑之count distinct多列問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-03-03
mysql workbench 設(shè)置外鍵的方法實(shí)現(xiàn)
在MySQL Workbench中設(shè)置外鍵屬性是非常方便的,本文就來(lái)介紹一下mysql workbench 設(shè)置外鍵的方法實(shí)現(xiàn),具有一定能的參考價(jià)值,感興趣的可以了解一下2024-01-01
在下面的練習(xí)中，將創(chuàng)建一個(gè)應(yīng)用程序，它包含的一個(gè)方法能夠計(jì)算一名顧問(wèn)的收費(fèi)金額——假定該顧問(wèn)每天收取固定的費(fèi)用，將根據(jù)工作了多少天來(lái)收費(fèi)。首先要開發(fā)應(yīng)用程序的邏輯，然后利用“生成方法存根向?qū)А眮?lái)寫出這個(gè)邏輯使用的方法。接著，我們將在一個(gè)控制臺(tái)應(yīng)用程序中運(yùn)行方法，以獲得對(duì)該程序的最終印象。最后，我們將使用Visual Studio 2005調(diào)試器來(lái)檢查方法調(diào)用。2008-04-04
limit是MySql的內(nèi)置函數(shù),一般用于查詢表中記錄的條數(shù),作用是用于限制查詢條數(shù),下面這篇文章主要給大家介紹了關(guān)于SQL中l(wèi)imit函數(shù)語(yǔ)法與用法的相關(guān)資料,詳細(xì)講了MySQL Limit執(zhí)行過(guò)程2022-12-12