不同於Oracle：MySQL的insert會阻塞update

来源:https://www.cnblogs.com/greatsql/archive/2023/03/28/17264312.html
-Advertisement-
Play Games

GreatSQL社區原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。 GreatSQL是MySQL的國產分支版本，使用上與MySQL一致。 作者： 王慶勛 文章來源：GreatSQL社區原創 某銀行客戶在從Oracle遷移到MySQL的開發中，MySQL在READ-COMMITTED隔離級別 ...


  • 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同樣主鍵的數據會被阻塞。

session1 session2
插入一條數據(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> 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的事務表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: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)
說明：事務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 9320
sql_kill_blocking_connection: KILL 9320
1 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: [email protected]
                    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: [email protected]
                    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語句已將數據寫入數據文件，索引數據也已生成。

測試表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)

開啟一個事務，插入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_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)

更新統計信息
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)

檢查數據文件的大小
[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語句。

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

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 讀SQL進階教程筆記01_CASE表達式
    1. 概述 1.1. SQL-92標準裡加入的最有用的特性 1.2. 寫法 1.2.1. 簡單CASE表達式 CASE sex WHEN '1' THEN ’男’ WHEN '2' THEN ’女’ ELSE ’其他’ END 1.2.1.1. 寫法簡單，但能實現的事情比較有限 1.2.2. 搜索C ...
  • TiDB SQL調優案例之避免TiFlash幫倒忙
    背景 早上收到某系統的告警tidb節點掛掉無法訪問，情況十萬火急。登錄中控機查了一下display信息，4個TiDB、Prometheus、Grafana全掛了，某台機器hang死無法連接，經過快速重啟後集群恢復，經排查後是昨天上線的某個SQL導致頻繁OOM。 於是開始亡羊補牢，來一波近期慢SQL巡 ...
  • 超詳細【入門精講】數據倉庫原理&實戰 一步一步搭建數據倉庫 內附相應實驗代碼和鏡像數據和腳本
    超詳細【入門精講】數據倉庫原理&實戰 一步一步搭建數據倉庫 內附相應實驗代碼和鏡像數據和腳本，參考B站up主哈嘍鵬程視頻撰寫而成，感謝！！！ ...
  • MySQL學習筆記-存儲引擎
    存儲引擎 一. MySQL體繫結構 MySQL Server 連接層：連接的處理、認證授權、安全方案、檢查是否超過最大連接數等。 服務層：SQL介面、解析器、查詢優化器、緩存 引擎層：引擎是數據存儲和提取的方式，引擎層有許多引擎可供使用，也可以自定義引擎。索引是在存儲引擎層實現的。 存儲層：存儲數據 ...
  • MySQL事務還沒提交，Canal就能讀到消息了？
    ##【問題描述】 開發有天碰到一個很奇怪的問題，他的場景是這樣子的： 通過Canal來訂閱MySQL的binlog, 當捕獲到有數據變化時，回到資料庫，反查該數據的明細，然後做進一步處理。 有一次，他碰到一個詭異的現象： 1. Canal收到消息，有一條主鍵id=31019319的數據插入 2. 1 ...
  • sql server相關學習sql語句
    sql腳本 表結構設置 點擊查看代碼 if exists(select * from sys.objects where name='Department' and type='U') drop table Department create table Department ( --id iden ...
  • 從數倉發展史淺析數倉未來技術趨勢
    摘要：華為雲EI DTSE技術佈道師/華為雲數倉GaussDB(DWS)首席架構師曾凱，針對數據倉庫的起源、演進過程、未來技術發展趨勢，與開發者和伙伴朋友們展開交流互動，幫助開發者快速瞭解數據倉庫相關信息與能力。 本文分享自華為雲社區《直播回顧 | 從數倉發展史淺析數倉未來技術趨勢》，作者：胡辣湯。 ...
  • 數倉如何進行表級控制analyze？
    摘要： 介紹如何設置採樣大小和表級控制analyze。 本文分享自華為雲社區《GaussDB(DWS) 如何表級控制analyze》，作者：leapdb。 一、控制採樣大小 【設置全局採樣大小】 通過參數default_statistics_target設置全局預設採樣大小。 a.default_s ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類