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

Bald_eagle

发布于 2023-5-24 15:23

浏览

0收藏

某银行客户在从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.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.

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: 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)。1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.

说明：

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.ibd1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.

说明：

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;1.
2.
3.
4.
5.
6.

执行相同的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 :)

文章转载自公众号：GreatSQL社区

分类

数据库

标签

GreatSQL

已于2023-5-24 15:23:47修改

51CTO

51CTO博客

51CTO学堂