首页 > 代码库 > 【练习】使用事务和锁定语句
【练习】使用事务和锁定语句
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表中插入新的一行，确认新行已经加入。
1.
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)
2.
mysql> prompt t1>;
PROMPT set to ‘t1>‘
t1>start transaction;
Query OK, 0 rows affected (0.00 sec)
3.
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
4.
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)
5.
t1>prompt t2>;
PROMPT set to ‘t2>‘
t2>
t2>start transaction;
Query OK, 0 rows affected (0.00 sec)
6.
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)
7.
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)
8.
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)
9.
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)
【练习】使用事务和锁定语句
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。