mysql 学习记录（十三）--事务与表锁

一、备注：
1.host1：主机1
2.host2：主机2
二、实践：

host1:

mysql> use test1;
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

mysql> lock table film_text read;
Query OK, 0 rows affected (0.00 sec) 

mysql> select film_id,title from film_text where film_id = 1;
+---------+-----------+
| film_id | title     |
+---------+-----------+
|       1 | film_name |
|       1 | film_name |
+---------+-----------+
2 rows in set (0.00 sec)

host2:
mysql> use test1;
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
mysql> select film_id,title from film_text where film_id = 1;
+---------+-----------+
| film_id | title     |
+---------+-----------+
|       1 | film_name |
|       1 | film_name |
+---------+-----------+
2 rows in set (0.00 sec)

mysql> update film_text set title='title_update' where film_id = 1;
^CCtrl-C -- sending "KILL QUERY 112" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

host1:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

host2:
mysql> update film_text set title='title_update' where film_id = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from film_text;
+---------+--------------------+--------------------------+
| film_id | title              | description              |
+---------+--------------------+--------------------------+
|       1 | title_update       | film_description         |
|    NULL | before insert      | NULL                     |
|    NULL | after insert       | NULL                     |
|    NULL | before insert      | NULL                     |
|    NULL | after insert       | NULL                     |
|    NULL | before insert      | NULL                     |
|     111 | test_trigger_title | test_trigger_description |
|     111 | test_trigger_title | test_trigger_description |
|       1 | title_update       | film_description         |
+---------+--------------------+--------------------------+
9 rows in set (0.00 sec)

host1:
mysql> select * from actor where actor_id = 1;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Test       | 1         |
+----------+------------+-----------+
1 row in set (0.00 sec)

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

host2:
mysql> select * from actor where actor_id = 235;
^CCtrl-C -- sending "KILL QUERY 112" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

host1:
mysql> insert into actor(actor_id,first_name,last_name) 
    -> values ('235','List','Tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from actor;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Test       | 1         |
|      201 | Test       | 201       |
|      235 | List       | Tom       |
+----------+------------+-----------+
3 rows in set (0.00 sec)

host2:
mysql> select * from actor where actor_id = 235;
^CCtrl-C -- sending "KILL QUERY 112" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

host1:
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from actor;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Test       | 1         |
|      201 | Test       | 201       |
|      235 | List       | Tom       |
+----------+------------+-----------+
3 rows in set (0.00 sec)

host2:
mysql> select * from actor ;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Test       | 1         |
|      201 | Test       | 201       |
|      235 | List       | Tom       |
+----------+------------+-----------+
5 rows in set (0.00 sec)

host1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into actor(actor_id,first_name,last_name)  values ('236','List','Tom');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from actor where actor_id = 235;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      235 | List       | Tom       |
+----------+------------+-----------+
1 row in set (0.00 sec)
 
mysql> lock table actor write;
Query OK, 0 rows affected (0.00 sec)

host2:
mysql> select * from actor where actor_id = 235;
^CCtrl-C -- sending "KILL QUERY 112" to server ...
Ctrl-C -- query aborted.

host1:
mysql> select * from actor;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Test       | 1         |
|      201 | Test       | 201       |
|      235 | List       | Tom       |
|      236 | List       | Tom       |
+----------+------------+-----------+
4 rows in set (0.01 sec)

mysql> insert into actor(actor_id,first_name,last_name)
    -> values ('237','Abc','Def');
Query OK, 1 row affected (0.01 sec)

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

mysql> select * from actor;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Test       | 1         |
|      201 | Test       | 201       |
|      235 | List       | Tom       |
|      236 | List       | Tom       |
|      237 | Abc        | Def       |
+----------+------------+-----------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

host2:
mysql> select * from actor ;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Test       | 1         |
|      201 | Test       | 201       |
|      235 | List       | Tom       |
|      236 | List       | Tom       |
|      237 | Abc        | Def       |
+----------+------------+-----------+

host1:
mysql> select * from actor;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Test       | 1         |
|      201 | Test       | 201       |
|      235 | List       | Tom       |
|      236 | List       | Tom       |
|      237 | Abc        | Def       |
+----------+------------+-----------+
5 rows in set (0.00 sec)

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

mysql> select * from actor where actor_id = 301;
Empty set (0.00 sec)

host1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into actor(actor_id) values (301);
Query OK, 1 row affected (0.00 sec)

mysql> select * from actor where actor_id = 301;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      301 | NULL       | NULL      |
+----------+------------+-----------+
1 row in set (0.00 sec)

mysql> savepoint test;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into actor(actor_id) values (302);
Query OK, 1 row affected (0.00 sec)

mysql> select * from actor;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Test       | 1         |
|      201 | Test       | 201       |
|      235 | List       | Tom       |
|      236 | List       | Tom       |
|      237 | Abc        | Def       |
|      301 | NULL       | NULL      |
|      302 | NULL       | NULL      |
+----------+------------+-----------+
7 rows in set (0.00 sec)

mysql> rollback to savepoint test;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Test       | 1         |
|      201 | Test       | 201       |
|      235 | List       | Tom       |
|      236 | List       | Tom       |
|      237 | Abc        | Def       |
|      301 | NULL       | NULL      |
+----------+------------+-----------+
6 rows in set (0.00 sec)

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