Bo's Oracle Station › 博客首页 ›Oracle技术› Oracle云、Linux、MySQL和Java › 查看内容

【博客文章2026】MySQL 9.x数据库管理技巧6：事务隔离和锁---与Oracle数据库对比

2026-3-20 14:17| 发布者: admin| 查看: 260| 评论: 0|原作者: Bo Tang

摘要: 当多个会话同时访问同一个表中的数据的时候，要避免出现Dirty Read、Non-repeatable Read和Phantom Read这三种问题。MySQL数据库通过事务隔离来解决上述问题。本博客通过三个实验来了解MySQL 数据库9.x的事务隔离技术，并与Oracle数据库的相应技术做对比。

Author: Bo Tang

1. 并发性问题和事务隔离：

当多个会话同时访问同一个表中的数据的时候，要避免出现Dirty Read、Non-repeatable Read和Phantom Read这三种问题。MySQL数据库通过事务隔离来解决上述问题。不同的存储引擎能够实现不同的事务隔离等级。InnoDB支持以下的事务隔离等级，以应对前述的三种问题：

事务隔离等级	Dirty Read	Non-repeatable Read	Phantom Read
Read Uncommitted	会发生	会发生	会发生
Read committed	不会发生	会发生	会发生
Repeatable Read	不会发生	不会发生	对于InnoDB存储引擎：不会发生
Serializable	不会发生	不会发生	不会发生

查询当前的事务隔离等级：

mysql> show variables like 'transaction%';

+------------------------------+-----------------+

| Variable_name | Value |

+------------------------------+-----------------+

| transaction_alloc_block_size | 8192 |

| transaction_allow_batching | OFF |

| transaction_isolation | REPEATABLE-READ |

| transaction_prealloc_size | 4096 |

| transaction_read_only | OFF |

+------------------------------+-----------------+

5 rows in set (0.004 sec)

mysql> select @@global.transaction_isolation;

+--------------------------------+

| @@global.transaction_isolation |

+--------------------------------+

| REPEATABLE-READ |

+--------------------------------+

1 row in set (0.001 sec)

2. 第一个会话：

在第一个会话中，开启一个新事务：

mysql> start transaction;

Query OK, 0 rows affected (0.000 sec)

mysql> use world_y;

Database changed

mysql> select * from city where id like '407_';

+------+--------------+-------------+------------+------------------------+

+------+--------------+-------------+------------+------------------------+

| 4070 | Chitungwiza | ZWE | Harare | {"Population": 274912} |

| 4071 | Mount Darwin | ZWE | Harare | {"Population": 164362} |

| 4072 | Mutare | ZWE | Manicaland | {"Population": 131367} |

| 4073 | Gweru | ZWE | Midlands | {"Population": 128037} |

| 4074 | Gaza | PSE | Gaza | {"Population": 353632} |

| 4075 | Khan Yunis | PSE | Khan Yunis | {"Population": 123175} |

| 4076 | Hebron | PSE | Hebron | {"Population": 119401} |

| 4077 | Jabaliya | PSE | North Gaza | {"Population": 113901} |

| 4078 | Nablus | PSE | Nablus | {"Population": 100231} |

| 4079 | Rafah | PSE | Rafah | {"Population": 92020} |

+------+--------------+-------------+------------+------------------------+

10 rows in set (0.012 sec)

3. 第二个会话：

在第二个会话中，开启另外一个新事务，并插入一行新行：

mysql> prompt second>;

PROMPT set to 'second>'

second>start transaction;

Query OK, 0 rows affected (0.000 sec)

second>use world_y;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

second>select * from city where id like '407_';

+------+--------------+-------------+------------+------------------------+

+------+--------------+-------------+------------+------------------------+

| 4070 | Chitungwiza | ZWE | Harare | {"Population": 274912} |

| 4071 | Mount Darwin | ZWE | Harare | {"Population": 164362} |

| 4072 | Mutare | ZWE | Manicaland | {"Population": 131367} |

| 4073 | Gweru | ZWE | Midlands | {"Population": 128037} |

| 4074 | Gaza | PSE | Gaza | {"Population": 353632} |

| 4075 | Khan Yunis | PSE | Khan Yunis | {"Population": 123175} |

| 4076 | Hebron | PSE | Hebron | {"Population": 119401} |

| 4077 | Jabaliya | PSE | North Gaza | {"Population": 113901} |

| 4078 | Nablus | PSE | Nablus | {"Population": 100231} |

| 4079 | Rafah | PSE | Rafah | {"Population": 92020} |

+------+--------------+-------------+------------+------------------------+

10 rows in set (0.012 sec)

second>insert into city (id,name,countrycode) values (4080,'Fuzhou','CNA');

Query OK, 1 row affected (0.001 sec)

second>select * from city where name ='Fuzhou';

+------+--------+-------------+----------+------+

+------+--------+-------------+----------+------+

| 4080 | Fuzhou | CNA | | NULL |

+------+--------+-------------+----------+------+

1 row in set (0.006 sec)

second>select * from city where id > 4070;

+------+--------------+-------------+------------+------------------------+

+------+--------------+-------------+------------+------------------------+

| 4071 | Mount Darwin | ZWE | Harare | {"Population": 164362} |

| 4072 | Mutare | ZWE | Manicaland | {"Population": 131367} |

| 4073 | Gweru | ZWE | Midlands | {"Population": 128037} |

| 4074 | Gaza | PSE | Gaza | {"Population": 353632} |

| 4075 | Khan Yunis | PSE | Khan Yunis | {"Population": 123175} |

| 4076 | Hebron | PSE | Hebron | {"Population": 119401} |

| 4077 | Jabaliya | PSE | North Gaza | {"Population": 113901} |

| 4078 | Nablus | PSE | Nablus | {"Population": 100231} |

| 4079 | Rafah | PSE | Rafah | {"Population": 92020} |

| 4080 | Fuzhou | CNA | | NULL |

+------+--------------+-------------+------------+------------------------+

10 rows in set (0.001 sec)

4. 回到第一个会话：

在第一个会话中：

mysql> select * from city where id > 4070;

+------+--------------+-------------+------------+------------------------+

+------+--------------+-------------+------------+------------------------+

| 4071 | Mount Darwin | ZWE | Harare | {"Population": 164362} |

| 4072 | Mutare | ZWE | Manicaland | {"Population": 131367} |

| 4073 | Gweru | ZWE | Midlands | {"Population": 128037} |

| 4074 | Gaza | PSE | Gaza | {"Population": 353632} |

| 4075 | Khan Yunis | PSE | Khan Yunis | {"Population": 123175} |

| 4076 | Hebron | PSE | Hebron | {"Population": 119401} |

| 4077 | Jabaliya | PSE | North Gaza | {"Population": 113901} |

| 4078 | Nablus | PSE | Nablus | {"Population": 100231} |

| 4079 | Rafah | PSE | Rafah | {"Population": 92020} |

+------+--------------+-------------+------------+------------------------+

9 rows in set (0.001 sec)

“4080”这一行没有出现，这正是REPEATABLE-READ的事务隔离结果。

5. 回到第二个会话：

在第二个会话中结束事务：

second>commit;

Query OK, 0 rows affected (0.024 sec)

6. 最后回到第一个会话：

在第一个会话中结束事务前，由于REPEATABLE-READ要求在第一个会话的事务中查询不受影响，所以即使第二个会话已经提交，第一个会话也不可见“4080”这一行。这一点与Oracle数据库中的隔离机制完全不同。在Oracle数据库中只要第二个会话提交，那么在第一个会话中这行就可见了：

mysql> select * from city where id > 4070;

+------+--------------+-------------+------------+------------------------+

+------+--------------+-------------+------------+------------------------+

| 4071 | Mount Darwin | ZWE | Harare | {"Population": 164362} |

| 4072 | Mutare | ZWE | Manicaland | {"Population": 131367} |

| 4073 | Gweru | ZWE | Midlands | {"Population": 128037} |

| 4074 | Gaza | PSE | Gaza | {"Population": 353632} |

| 4075 | Khan Yunis | PSE | Khan Yunis | {"Population": 123175} |

| 4076 | Hebron | PSE | Hebron | {"Population": 119401} |

| 4077 | Jabaliya | PSE | North Gaza | {"Population": 113901} |

| 4078 | Nablus | PSE | Nablus | {"Population": 100231} |

| 4079 | Rafah | PSE | Rafah | {"Population": 92020} |

+------+--------------+-------------+------------+------------------------+

9 rows in set (0.001 sec)

在第一个会话中，结束事务后：

mysql> commit;

Query OK, 0 rows affected (0.000 sec)

mysql> select * from city where id > 4070;

+------+--------------+-------------+------------+------------------------+

+------+--------------+-------------+------------+------------------------+

| 4071 | Mount Darwin | ZWE | Harare | {"Population": 164362} |

| 4072 | Mutare | ZWE | Manicaland | {"Population": 131367} |

| 4073 | Gweru | ZWE | Midlands | {"Population": 128037} |

| 4074 | Gaza | PSE | Gaza | {"Population": 353632} |

| 4075 | Khan Yunis | PSE | Khan Yunis | {"Population": 123175} |

| 4076 | Hebron | PSE | Hebron | {"Population": 119401} |

| 4077 | Jabaliya | PSE | North Gaza | {"Population": 113901} |

| 4078 | Nablus | PSE | Nablus | {"Population": 100231} |

| 4079 | Rafah | PSE | Rafah | {"Population": 92020} |

| 4080 | Fuzhou | CNA | | NULL |

+------+--------------+-------------+------------+------------------------+

10 rows in set (0.001 sec)

因为两个会话中各自的事务都已经提交，所以REPEATABLE-READ的事务隔离条件满足，第一个会话见到了在第二个会话插入的新行。

7. 尝试改变隔离等级为READ COMMITED（这个与Oracle数据库事务隔离机制一致）：

在第二个会话中：

second>set session transaction_isolation='READ-COMMITTED';

Query OK, 0 rows affected (0.000 sec)

second>start transaction;

Query OK, 0 rows affected (0.000 sec)

在第一个会话中：

mysql> start transaction;

Query OK, 0 rows affected (0.000 sec)

mysql> delete from city where id=4077;

Query OK, 1 row affected (0.001 sec)

mysql> select * from city where id =4077;

Empty set (0.001 sec)

mysql> commit;

Query OK, 0 rows affected (0.024 sec)

虽然第二个会话中的事务还没结束，但是READ-COMMITTED可以看见所有提交过的数据。这一点与Oracle数据库中的隔离机制相同。在Oracle数据库中只要第一个会话提交，那么在第二个会话中这行就不存在了：

second>select * from city where id =4077;

Empty set (0.001 sec)

8. 尝试改变隔离等级为READ UNCOMMITED（用于对比，不推荐）：

在第二个会话中：

second>set session transaction_isolation='READ-UNCOMMITTED';

Query OK, 0 rows affected (0.000 sec)

在第一个会话中：

mysql> start transaction;

Query OK, 0 rows affected (0.000 sec)

mysql> delete from city where id=4075;

Query OK, 1 row affected (0.001 sec)

第二个会话中：

second>select * from city where id =4075;

Empty set (0.001 sec)

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：【博客文章2026】MySQL 9.x数据库管理技巧---给初学者的建议5：获取数据字典信息

【博客文章2026】MySQL 9.x数据库管理技巧6：事务隔离和锁---与Oracle数据库对比

相关分类