何卫涛 发表于 2023-3-28 11:45:01

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


[*]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同样主键的数据会被阻塞。
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;select * from performance_schema.data_locks\G*************************** 1. row ***************************               ENGINE: INNODB       ENGINE_LOCK_ID: 139727275779216:1113:139724882998560ENGINE_TRANSACTION_ID: 3795            THREAD_ID: 9441             EVENT_ID: 5000      OBJECT_SCHEMA: testdb          OBJECT_NAME: t       PARTITION_NAME: NULL    SUBPARTITION_NAME: NULL         INDEX_NAME: NULLOBJECT_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:139724882995456ENGINE_TRANSACTION_ID: 3795            THREAD_ID: 9441             EVENT_ID: 5012      OBJECT_SCHEMA: testdb          OBJECT_NAME: t       PARTITION_NAME: NULL    SUBPARTITION_NAME: NULL         INDEX_NAME: PRIMARYOBJECT_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:139724883017072ENGINE_TRANSACTION_ID: 3790            THREAD_ID: 9415             EVENT_ID: 15467      OBJECT_SCHEMA: testdb          OBJECT_NAME: t       PARTITION_NAME: NULL    SUBPARTITION_NAME: NULL         INDEX_NAME: NULLOBJECT_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:139724883013968ENGINE_TRANSACTION_ID: 3790            THREAD_ID: 9441             EVENT_ID: 5007      OBJECT_SCHEMA: testdb          OBJECT_NAME: t       PARTITION_NAME: NULL    SUBPARTITION_NAME: NULL         INDEX_NAME: PRIMARYOBJECT_INSTANCE_BEGIN: 139724883013968            LOCK_TYPE: RECORD            LOCK_MODE: X,REC_NOT_GAP          LOCK_STATUS: GRANTED            LOCK_DATA: 84 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: 1blocking_trx_rows_modified: 1   sql_kill_blocking_query: KILL QUERY 9320sql_kill_blocking_connection: KILL 93201 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: 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: NULLlast_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说明:被阻塞事务执行的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语句已将数据写入数据文件,索引数据也已生成。
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: 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
说明:被阻塞事务执行的sql语句update t set b=0 where a=8,
阻塞事务执行的sql语句是insert into t values(8,8)。说明:
MySQL在执行insert 语句进行数据插入,未提交时,数据也已写入表的聚集索引,辅助索引也已生成。MySQL可以使用未提交数据的索引,通过锁机制实现事务的并发控制。
3.Oracle中insert没有阻塞update

在Oracle中,创建同样的测试表t,执行同样的insert和update,但insert不会阻塞update。
测试表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: 100000Duplicates: 0Warnings: 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)

检查数据文件的大小
# 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执行相同的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
来源:https://www.cnblogs.com/greatsql/p/17264312.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!
页: [1]
查看完整版本: 不同于Oracle:MySQL的insert会阻塞update