发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

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

雅强

6

主题

6

帖子

18

积分

新手上路

Rank: 1

积分
18
显示全部楼层

  • 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为主键。
  1. mysql> select version();
  2. +-----------+
  3. | version() |
  4. +-----------+
  5. | 8.0.26    |
  6. +-----------+
  7. 1 row in set (0.02 sec)
  9. mysql> show variables like 'transaction_isolation';
  10. +-----------------------+----------------+
  11. | Variable_name         | Value          |
  12. +-----------------------+----------------+
  13. | transaction_isolation | READ-COMMITTED |
  14. +-----------------------+----------------+
  15. 1 row in set (0.00 sec)
  17. mysql> desc t;
  18. +-------+------+------+-----+---------+-------+
  19. | Field | Type | Null | Key | Default | Extra |
  20. +-------+------+------+-----+---------+-------+
  21. | a     | int  | NO   | PRI | NULL    |       |
  22. | b     | int  | YES  |     | NULL    |       |
  23. +-------+------+------+-----+---------+-------+
  24. 2 rows in set (0.01 sec)
  26. mysql> select * from t;
  27. +---+------+
  28. | a | b    |
  29. +---+------+
  30. | 7 |    7 |
  31. +---+------+
  32. 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: 1  blocking_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)。[/code]说明：
MySQL的隔离级别是通过索引上的锁实现并发事务控制的。在READ-COMMITTED隔离级别下，session1在执行insert语句时，在主键索引上获取了a=8的行记录独占锁，以禁止插入相同主键的数据；session2如果同时插入相同的主键数据被阻塞，容易理解（Oracle也同样阻塞）。出于同样的原因session2执行update时，由于无法获取a=8的行记录独占锁，同样也会被阻塞。
2.2.验证MySQL事务未提交时已写入数据文件

验证事务未提交时，insert语句已将数据写入数据文件，索引数据也已生成。
  1. mysql> select * from information_schema.innodb_trx\G
  2. *************************** 1. row ***************************
  3.                     trx_id: 3795
  4.                  trx_state: LOCK WAIT
  5.                trx_started: 2022-10-11 16:03:38
  6.      trx_requested_lock_id: 139727275779216:52:4:3:139724882995456
  7.           trx_wait_started: 2022-10-11 16:03:38
  8.                 trx_weight: 2
  9.        trx_mysql_thread_id: 9346
  10.                  trx_query: update t set b=0 where a=8
  11.        trx_operation_state: starting index read
  12.          trx_tables_in_use: 1
  13.          trx_tables_locked: 1
  14.           trx_lock_structs: 2
  15.      trx_lock_memory_bytes: 1128
  16.            trx_rows_locked: 1
  17.          trx_rows_modified: 0
  18.    trx_concurrency_tickets: 0
  19.        trx_isolation_level: READ COMMITTED
  20.          trx_unique_checks: 1
  21.     trx_foreign_key_checks: 1
  22. trx_last_foreign_key_error: NULL
  23. trx_adaptive_hash_latched: 0
  24. trx_adaptive_hash_timeout: 0
  25.           trx_is_read_only: 0
  26. trx_autocommit_non_locking: 0
  27.        trx_schedule_weight: 1
  28. *************************** 2. row ***************************
  29.                     trx_id: 3790
  30.                  trx_state: RUNNING
  31.                trx_started: 2022-10-11 16:03:29
  32.      trx_requested_lock_id: NULL
  33.           trx_wait_started: NULL
  34.                 trx_weight: 3
  35.        trx_mysql_thread_id: 9320
  36.                  trx_query: NULL
  37.        trx_operation_state: NULL
  38.          trx_tables_in_use: 0
  39.          trx_tables_locked: 1
  40.           trx_lock_structs: 2
  41.      trx_lock_memory_bytes: 1128
  42.            trx_rows_locked: 1
  43.          trx_rows_modified: 1
  44.    trx_concurrency_tickets: 0
  45.        trx_isolation_level: READ COMMITTED
  46.          trx_unique_checks: 1
  47.     trx_foreign_key_checks: 1
  48. trx_last_foreign_key_error: NULL
  49. trx_adaptive_hash_latched: 0
  50. trx_adaptive_hash_timeout: 0
  51.           trx_is_read_only: 0
  52. trx_autocommit_non_locking: 0
  53.        trx_schedule_weight: NULL
  54. 2 rows in set (0.00 sec)
  56. 说明：通过InnoDB的事务表innodb_trx查询到thread_id=9346的事务3795正在等待锁（trx_state: LOCK WAIT）,
  57. thread_id=9320的事务3790正在执行（trx_state: RUNNING）。
  59. mysql> select * from performance_schema.data_locks\G
  60. *************************** 1. row ***************************
  61.                ENGINE: INNODB
  62.        ENGINE_LOCK_ID: 139727275779216:1113:139724882998560
  63. ENGINE_TRANSACTION_ID: 3795
  64.             THREAD_ID: 9441
  65.              EVENT_ID: 5000
  66.         OBJECT_SCHEMA: testdb
  67.           OBJECT_NAME: t
  68.        PARTITION_NAME: NULL
  69.     SUBPARTITION_NAME: NULL
  70.            INDEX_NAME: NULL
  71. OBJECT_INSTANCE_BEGIN: 139724882998560
  72.             LOCK_TYPE: TABLE
  73.             LOCK_MODE: IX
  74.           LOCK_STATUS: GRANTED
  75.             LOCK_DATA: NULL
  76. *************************** 2. row ***************************
  77.                ENGINE: INNODB
  78.        ENGINE_LOCK_ID: 139727275779216:52:4:3:139724882995456
  79. ENGINE_TRANSACTION_ID: 3795
  80.             THREAD_ID: 9441
  81.              EVENT_ID: 5012
  82.         OBJECT_SCHEMA: testdb
  83.           OBJECT_NAME: t
  84.        PARTITION_NAME: NULL
  85.     SUBPARTITION_NAME: NULL
  86.            INDEX_NAME: PRIMARY
  87. OBJECT_INSTANCE_BEGIN: 139724882995456
  88.             LOCK_TYPE: RECORD
  89.             LOCK_MODE: X,REC_NOT_GAP
  90.           LOCK_STATUS: WAITING
  91.             LOCK_DATA: 8
  92. *************************** 3. row ***************************
  93.                ENGINE: INNODB
  94.        ENGINE_LOCK_ID: 139727275781640:1113:139724883017072
  95. ENGINE_TRANSACTION_ID: 3790
  96.             THREAD_ID: 9415
  97.              EVENT_ID: 15467
  98.         OBJECT_SCHEMA: testdb
  99.           OBJECT_NAME: t
  100.        PARTITION_NAME: NULL
  101.     SUBPARTITION_NAME: NULL
  102.            INDEX_NAME: NULL
  103. OBJECT_INSTANCE_BEGIN: 139724883017072
  104.             LOCK_TYPE: TABLE
  105.             LOCK_MODE: IX
  106.           LOCK_STATUS: GRANTED
  107.             LOCK_DATA: NULL
  108. *************************** 4. row ***************************
  109.                ENGINE: INNODB
  110.        ENGINE_LOCK_ID: 139727275781640:52:4:3:139724883013968
  111. ENGINE_TRANSACTION_ID: 3790
  112.             THREAD_ID: 9441
  113.              EVENT_ID: 5007
  114.         OBJECT_SCHEMA: testdb
  115.           OBJECT_NAME: t
  116.        PARTITION_NAME: NULL
  117.     SUBPARTITION_NAME: NULL
  118.            INDEX_NAME: PRIMARY
  119. OBJECT_INSTANCE_BEGIN: 139724883013968
  120.             LOCK_TYPE: RECORD
  121.             LOCK_MODE: X,REC_NOT_GAP
  122.           LOCK_STATUS: GRANTED
  123.             LOCK_DATA: 8
  124. 4 rows in set (0.00 sec)
  125. 说明：事务3795正在等待LOCK_TYPE: RECORD，LOCK_MODE:X,REC_NOT_GAP，等待的主键值为8；
  126. 事务3790已获取主键值为8的LOCK_TYPE: RECORD，LOCK_MODE:X,REC_NOT_GAP锁。
  128. mysql> select * from sys.innodb_lock_waits\G
  129. *************************** 1. row ***************************
  130.                 wait_started: 2022-10-11 16:03:38
  131.                     wait_age: 00:02:50
  132.                wait_age_secs: 170
  133.                 locked_table: `testdb`.`t`
  134.          locked_table_schema: testdb
  135.            locked_table_name: t
  136.       locked_table_partition: NULL
  137.    locked_table_subpartition: NULL
  138.                 locked_index: PRIMARY
  139.                  locked_type: RECORD
  140.               waiting_trx_id: 3795
  141.          waiting_trx_started: 2022-10-11 16:03:38
  142.              waiting_trx_age: 00:02:50
  143.      waiting_trx_rows_locked: 1
  144.    waiting_trx_rows_modified: 0
  145.                  waiting_pid: 9346
  146.                waiting_query: update t set b=0 where a=8
  147.              waiting_lock_id: 139727275779216:52:4:3:139724882995456
  148.            waiting_lock_mode: X,REC_NOT_GAP
  149.              blocking_trx_id: 3790
  150.                 blocking_pid: 9320
  151.               blocking_query: NULL
  152.             blocking_lock_id: 139727275781640:52:4:3:139724883013968
  153.           blocking_lock_mode: X,REC_NOT_GAP
  154.         blocking_trx_started: 2022-10-11 16:03:29
  155.             blocking_trx_age: 00:02:59
  156.     blocking_trx_rows_locked: 1
  157.   blocking_trx_rows_modified: 1
  158.      sql_kill_blocking_query: KILL QUERY 9320
  159. sql_kill_blocking_connection: KILL 9320
  160. 1 row in set (0.01 sec)
  161. 说明：事务3795等待testdb.t上的rec_not_gap独占锁，事务3790持有该独占锁。
  162. mysql> select distinct,* from sys.processlist where conn_id in (select trx_mysql_thread_id from information_schema.innodb_trx)\G
  163. *************************** 1. row ***************************
  164.                 thd_id: 9441
  165.                conn_id: 9346
  166.                   user: admin@172.17.128.73
  167.                     db: testdb
  168.                command: Query
  169.                  state: updating
  170.                   time: 141
  171.      current_statement: update t set b=0 where a=8
  172.      statement_latency: 2.37 min
  173.               progress: NULL
  174.           lock_latency: 431.00 us
  175.          rows_examined: 0
  176.              rows_sent: 0
  177.          rows_affected: 0
  178.             tmp_tables: 0
  179.        tmp_disk_tables: 0
  180.              full_scan: NO
  181.         last_statement: NULL
  182. last_statement_latency: NULL
  183.         current_memory: 140.15 KiB
  184.              last_wait: wait/io/table/sql/handler
  185.      last_wait_latency: Still Waiting
  186.                 source: handler.cc:3250
  187.            trx_latency: 13.30 min
  188.              trx_state: ACTIVE
  189.         trx_autocommit: NO
  190.                    pid: 9632
  191.           program_name: mysql
  192. *************************** 2. row ***************************
  193.                 thd_id: 9415
  194.                conn_id: 9320
  195.                   user: admin@172.17.128.73
  196.                     db: testdb
  197.                command: Sleep
  198.                  state: NULL
  199.                   time: 801
  200.      current_statement: NULL
  201.      statement_latency: NULL
  202.               progress: NULL
  203.           lock_latency: 288.00 us
  204.          rows_examined: 0
  205.              rows_sent: 0
  206.          rows_affected: 1
  207.             tmp_tables: 0
  208.        tmp_disk_tables: 0
  209.              full_scan: NO
  210.         last_statement: insert into t values(8,8)
  211. last_statement_latency: 765.23 us
  212.         current_memory: 218.19 KiB
  213.              last_wait: wait/io/socket/sql/client_connection
  214.      last_wait_latency: Still Waiting
  215.                 source: viosocket.cc:146
  216.            trx_latency: 13.52 min
  217.              trx_state: ACTIVE
  218.         trx_autocommit: NO
  219.                    pid: 9600
  220.           program_name: mysql
  221. 说明：被阻塞事务执行的sql语句update t set b=0 where a=8，
  222. 阻塞事务执行的sql语句是insert into t values(8,8)。
复制代码
说明：
MySQL在执行insert 语句进行数据插入，未提交时，数据也已写入表的聚集索引，辅助索引也已生成。MySQL可以使用未提交数据的索引，通过锁机制实现事务的并发控制。
3.Oracle中insert没有阻塞update

在Oracle中，创建同样的测试表t，执行同样的insert和update，但insert不会阻塞update。
  1. 测试表test1
  2. mysql> CREATE TABLE `test1` (
  3.     ->   `id` int NOT NULL AUTO_INCREMENT,
  4.     ->   `k` int NOT NULL DEFAULT '0',
  5.     ->   `c` char(120) NOT NULL DEFAULT '',
  6.     ->   `pad` char(60) NOT NULL DEFAULT '',
  7.     ->   PRIMARY KEY (`id`),
  8.     ->   KEY `k_1` (`k`)
  9.     -> ) ;
  10. Query OK, 0 rows affected (0.07 sec)
  12. 开启一个事务，插入10万条数据。
  13. mysql> begin;
  14. Query OK, 0 rows affected (0.00 sec)
  16. mysql> insert into test1 select * from sbtest1;
  17. Query OK, 100000 rows affected (1.44 sec)
  18. Records: 100000  Duplicates: 0  Warnings: 0
  20. 检查表的data_length和index_length
  21. mysql> show table status where name like 'test1'\G
  22. *************************** 1. row ***************************
  23.            Name: test1
  24.          Engine: InnoDB
  25.         Version: 10
  26.      Row_format: Dynamic
  27.            Rows: 98712
  28. Avg_row_length: 228
  29.     Data_length: 22593536
  30. Max_data_length: 0
  31.    Index_length: 2637824
  32.       Data_free: 4194304
  33. Auto_increment: 100001
  34.     Create_time: 2022-10-11 22:14:50
  35.     Update_time: NULL
  36.      Check_time: NULL
  37.       Collation: utf8mb4_0900_ai_ci
  38.        Checksum: NULL
  39. Create_options:
  40.         Comment:
  41. 1 rows in set (0.01 sec)
  43. 回滚insert操作
  44. mysql> rollback;
  45. Query OK, 0 rows affected (1.35 sec)
  47. 更新统计信息
  48. mysql> analyze table test1;
  50. 再次检查表的data_length和index_length
  51. mysql> show table status where name like 'test1'\G
  52. *************************** 1. row ***************************
  53.            Name: test1
  54.          Engine: InnoDB
  55.         Version: 10
  56.      Row_format: Dynamic
  57.            Rows: 0
  58. Avg_row_length: 0
  59.     Data_length: 16384
  60. Max_data_length: 0
  61.    Index_length: 16384
  62.       Data_free: 29360128
  63. Auto_increment: 100001
  64.     Create_time: 2022-10-11 22:22:36
  65.     Update_time: NULL
  66.      Check_time: NULL
  67.       Collation: utf8mb4_0900_ai_ci
  68.        Checksum: NULL
  69. Create_options:
  70.         Comment:
  71. 1 row in set (0.00 sec)
  73. 检查数据文件的大小
  74. [root@host73 testdb]# ll *test1.ibd
  75. -rw-r----- 1 greatdb greatdb 30408704 Oct 11 15:12 sbtest1.ibd
  76. -rw-r----- 1 greatdb greatdb 33554432 Oct 11 22:24 test1.ibd
复制代码
执行相同的insert和update语句。
[table]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】 我们会及时删除侵权内容，谢谢合作！

上一篇: 数仓如何进行表级控制analyze？

下一篇: 不同于Oracle：MySQL的insert会阻塞update

发表于 2023-3-28 11:40:02

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4