【练习】使用事务和锁定语句

最新推荐文章于 2025-09-11 20:53:34 发布

转载最新推荐文章于 2025-09-11 20:53:34 发布 · 45 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/tomatoes-/p/6024578.html

文章标签：

#数据库

本文通过具体步骤展示了如何在MySQL中操作两个并发事务，并演示了不同事务隔离级别下数据的一致性和可见性特点。实验中，两个会话分别进行了查询、修改等操作，揭示了REPEATABLE-READ隔离级别下可能出现的现象。

1.在mysql客户机会话中，检查当前隔离级别。

2.使用PROMPT语句，将上一步打开的mysql会话中提示符改为t1，以便将其与后面的客户机会话区别开来。

3.在t1 mysql会话中启动一个新事务。

4.在t1会话中，选择city表中的ID>4070的所有行。

5.在另一个终端窗口中打开第二个mysql会话，将该mysql会话中的提示符更改为t2

6.在t2会话中，启动一个新事务,选择city表中的ID>4070的所有行。

7.在t2会话中，向city表中插入新的一行，确认新行已经加入。

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

mysql> prompt t1>;
PROMPT set to 't1>'
t1>start transaction;
Query OK, 0 rows affected (0.00 sec)

t1>use world
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

t1>select * from city where id >4070;
+------+--------------+-------------+------------+------------+
| ID   | Name         | CountryCode | District   | Population |
+------+--------------+-------------+------------+------------+
| 4071 | Mount Darwin | ZWE         | Harare     |     164362 |
| 4072 | Mutare       | ZWE         | Manicaland |     131367 |
| 4073 | Gweru        | ZWE         | Midlands   |     128037 |
| 4074 | Gaza         | PSE         | Gaza       |     353632 |
| 4075 | Khan Yunis   | PSE         | Khan Yunis |     123175 |
| 4076 | Hebron       | PSE         | Hebron     |     119401 |
| 4077 | Jabaliya     | PSE         | North Gaza |     113901 |
| 4078 | Nablus       | PSE         | Nablus     |     100231 |
| 4079 | Rafah        | PSE         | Rafah      |      92020 |
+------+--------------+-------------+------------+------------+
9 rows in set (0.00 sec)

t1>prompt t2>;
PROMPT set to 't2>'
t2>
t2>start transaction;
Query OK, 0 rows affected (0.00 sec)

t2>use world;
Database changed
t2>select * from city where id >4070;
+------+--------------+-------------+------------+------------+
| ID   | Name         | CountryCode | District   | Population |
+------+--------------+-------------+------------+------------+
| 4071 | Mount Darwin | ZWE         | Harare     |     164362 |
| 4072 | Mutare       | ZWE         | Manicaland |     131367 |
| 4073 | Gweru        | ZWE         | Midlands   |     128037 |
| 4074 | Gaza         | PSE         | Gaza       |     353632 |
| 4075 | Khan Yunis   | PSE         | Khan Yunis |     123175 |
| 4076 | Hebron       | PSE         | Hebron     |     119401 |
| 4077 | Jabaliya     | PSE         | North Gaza |     113901 |
| 4078 | Nablus       | PSE         | Nablus     |     100231 |
| 4079 | Rafah        | PSE         | Rafah      |      92020 |
+------+--------------+-------------+------------+------------+
9 rows in set (0.00 sec)

t2>insert into city (name,countrycode) values ('new city','ata');
Query OK, 1 row affected (0.00 sec)

t2>select * from city where id >4070;
+------+--------------+-------------+------------+------------+
| ID   | Name         | CountryCode | District   | Population |
+------+--------------+-------------+------------+------------+
| 4071 | Mount Darwin | ZWE         | Harare     |     164362 |
| 4072 | Mutare       | ZWE         | Manicaland |     131367 |
| 4073 | Gweru        | ZWE         | Midlands   |     128037 |
| 4074 | Gaza         | PSE         | Gaza       |     353632 |
| 4075 | Khan Yunis   | PSE         | Khan Yunis |     123175 |
| 4076 | Hebron       | PSE         | Hebron     |     119401 |
| 4077 | Jabaliya     | PSE         | North Gaza |     113901 |
| 4078 | Nablus       | PSE         | Nablus     |     100231 |
| 4079 | Rafah        | PSE         | Rafah      |      92020 |
| 4080 | new city     | ata         |            |          0 |
+------+--------------+-------------+------------+------------+
10 rows in set (0.00 sec)

t2>prompt t1>;
PROMPT set to 't1>'
t1>start transaction;
Query OK, 0 rows affected (0.01 sec)

t1>commit;
Query OK, 0 rows affected (0.00 sec)

t1>select * from city where id >4070;
+------+--------------+-------------+------------+------------+
| ID   | Name         | CountryCode | District   | Population |
+------+--------------+-------------+------------+------------+
| 4071 | Mount Darwin | ZWE         | Harare     |     164362 |
| 4072 | Mutare       | ZWE         | Manicaland |     131367 |
| 4073 | Gweru        | ZWE         | Midlands   |     128037 |
| 4074 | Gaza         | PSE         | Gaza       |     353632 |
| 4075 | Khan Yunis   | PSE         | Khan Yunis |     123175 |
| 4076 | Hebron       | PSE         | Hebron     |     119401 |
| 4077 | Jabaliya     | PSE         | North Gaza |     113901 |
| 4078 | Nablus       | PSE         | Nablus     |     100231 |
| 4079 | Rafah        | PSE         | Rafah      |      92020 |
| 4080 | new city     | ata         |            |          0 |
+------+--------------+-------------+------------+------------+
10 rows in set (0.01 sec)

10.

t1>start transaction;
Query OK, 0 rows affected (0.00 sec)

t1>delete from city where id =4080;
Query OK, 1 row affected (0.00 sec)

t1>select * from city where id >4070;
+------+--------------+-------------+------------+------------+
| ID   | Name         | CountryCode | District   | Population |
+------+--------------+-------------+------------+------------+
| 4071 | Mount Darwin | ZWE         | Harare     |     164362 |
| 4072 | Mutare       | ZWE         | Manicaland |     131367 |
| 4073 | Gweru        | ZWE         | Midlands   |     128037 |
| 4074 | Gaza         | PSE         | Gaza       |     353632 |
| 4075 | Khan Yunis   | PSE         | Khan Yunis |     123175 |
| 4076 | Hebron       | PSE         | Hebron     |     119401 |
| 4077 | Jabaliya     | PSE         | North Gaza |     113901 |
| 4078 | Nablus       | PSE         | Nablus     |     100231 |
| 4079 | Rafah        | PSE         | Rafah      |      92020 |
+------+--------------+-------------+------------+------------+
9 rows in set (0.00 sec)