- 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: 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的阻隔级别是经过索引上的锁完成并发业务控制的。在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 :)
关于 GreatSQL
GreatSQL是由万里数据库保护的MySQL分支,专心于提高MGR可靠性及功能,支撑InnoDB并行查询特性,是适用于金融级使用的MySQL分支版本。
相关链接: GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区:
社区有奖主张反馈: greatsql.cn/thread-54-1…
社区博客有奖征稿概况: greatsql.cn/thread-100-…
社区2022年度勋章获奖名单: greatsql.cn/thread-184-…
(对文章有疑问或许有独到见解都能够去社区官网提出或共享哦~)
技术交流群:
微信&QQ群:
QQ群:533341697
微信群:增加GreatSQL社区帮手(微信号:wanlidbc
)好友,待社区帮手拉您进群。