不同于Oracle：MySQL的insert会阻塞update

某银行客户在从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 inset(0.02 sec)

mysql> show variables like'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row inset(0.00 sec)

mysql>desc t;
+-------+------+------+-----+---------+-------+
| Field | Type |Null| Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a     |int| NO   | PRI |NULL||
| b     |int| YES  ||NULL||
+-------+------+------+-----+---------+-------+
2 rows inset(0.01 sec)

mysql>select*from t;
+---+------+
| a | b    |
+---+------+
|7|7|
+---+------+
1 row inset(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-1116:03:38
     trx_requested_lock_id:139727275779216:52:4:3:139724882995456
          trx_wait_started:2022-10-1116:03:38
                trx_weight:2
       trx_mysql_thread_id:9346
                 trx_query:update t set b=0where 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-1116: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 inset(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 inset(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-1116: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-1116: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=0where 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-1116: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 inset(0.01 sec)
说明：事务3795等待testdb.t上的rec_not_gap独占锁，事务3790持有该独占锁。
mysql>selectdistinct,*from sys.processlistwhere 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=0where 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:insertinto 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=0where 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>CREATETABLE `test1` (
->   `id` intNOTNULL AUTO_INCREMENT,
->   `k` intNOTNULL DEFAULT '0',
->   `c` char(120)NOTNULL DEFAULT '',
->   `pad` char(60)NOTNULL 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>insertinto 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-1122:14:50
    Update_time:NULL
     Check_time:NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum:NULL
 Create_options:
        Comment:
1 rows inset(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-1122:22:36
    Update_time:NULL
     Check_time:NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum:NULL
 Create_options:
        Comment:
1 row inset(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。

CREATETABLE t (
  a intNOTNULL PRIMARY KEY ,
  b int DEFAULT NULL
);
insertinto 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.

文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/303733.html<

不同于Oracle：MySQL的insert会阻塞update

1.问题复现

1.1.环境准备

1.2. insert阻塞update的操作步骤

2.分析原因

2.1.检查事务锁信息

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

3.Oracle中insert没有阻塞update

相关推荐

个人主题怎么制作？

社群营销管理关键是什么？

香港公司网站备案需要什么材料？

如何企业上云推广

PS设计搜索框的实用技巧有哪些？

发表回复