[Mysql] mysql lock table && unlock tables实验


本文总阅读量次

0、mysql版本

1
2
3
4
5
6
7
root@db 04:12:  [aaaa]> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)

1、创建实验表，内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
root@db 15:11:  [aaaa]> show tables;
+----------------+
| Tables_in_aaaa |
+----------------+
| aaa            |
| bbb            |
+----------------+
2 rows in set (0.00 sec)
root@db 15:15:  [aaaa]> select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)
root@db 15:15:  [aaaa]> select * from bbb;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
+----+------+-------------+
3 rows in set (0.00 sec)

2、开启两个会话，session1、session2,对表aaa进行read表锁

session1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
root@db 15:16:  [aaaa]> lock table aaa read;
Query OK, 0 rows affected (0.00 sec)
root@db 15:17:  [aaaa]> select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)
root@db 15:17:  [aaaa]> select * from bbb;
ERROR 1100 (HY000): Table 'bbb' was not locked with LOCK TABLES
root@db 15:17:  [aaaa]> 
root@db 15:18:  [aaaa]> update aaa set name='e' where id=1;
ERROR 1099 (HY000): Table 'aaa' was locked with a READ lock and can't be updated

session 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@db 15:18:  [aaaa]> select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)
root@db 15:18:  [aaaa]> update aaa set name='e' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@db 15:20:  [aaaa]> show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| aaaa     | aaa   |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

session 1

1
2
root@db 15:21:  [aaaa]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

结论：在session1对表aaa进行read锁表，session1只能对表aaa进行读操作，对其他表没有任何操作权限，session2对表aaa有读权限，没有写权限。

3、开启两个会话，session1、session2,对表aaa进行write表锁

session 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@db 15:21:  [aaaa]> lock table aaa write;
Query OK, 0 rows affected (0.00 sec)
root@db 15:26:  [aaaa]> select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)
root@db 15:26:  [aaaa]> select * from bbb;
ERROR 1100 (HY000): Table 'bbb' was not locked with LOCK TABLES
root@db 15:27:  [aaaa]>  update aaa set name='e' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
root@db 15:29:  [aaaa]> update bbb set name='e' where id=1;
ERROR 1100 (HY000): Table 'bbb' was not locked with LOCK TABLES

session 2

1
2
3
4
root@db 15:20:  [aaaa]> select * from aaa;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@db 15:28:  [aaaa]>  update aaa set name='e' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session 1

1
2
root@db 15:31:  [aaaa]> unlock  tables;
Query OK, 0 rows affected (0.00 sec)

结论：在session1对表aaa进行write锁表，session1对表aaa有读写权限，对其他表没有任何操作权限，session2对表aaa即没有读权限，又没有写权限。

4、开启两个会话，session1、session2,对表aaa进行write&read表锁

session1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
root@db 15:32:  [aaaa]> lock table aaa write , aaa as t1 read;
Query OK, 0 rows affected (0.00 sec)
root@db 15:32root@db 04:01:  [aaaa]> select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | e    | 11111111111 |
|  2 | e    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)

root@db 04:01:  [aaaa]> select * from aaa as t1;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | e    | 11111111111 |
|  2 | e    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)

root@db 04:02:  [aaaa]> update aaa as t1 set name='e' where id =1;
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated
root@db 04:04:  [aaaa]> update aaa  set name='e' where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

root@db 04:04:  [aaaa]> insert into aaa select * from aaa;
ERROR 1100 (HY000): Table 'aaa' was not locked with LOCK TABLES
root@db 04:04:  [aaaa]> insert into aaa select * from aaa as t1;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

session2

1
2
3
4
root@db 04:05:  [aaaa]> select * from aaa;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@db 04:06:  [aaaa]> update aaa set name='e' where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session1

1
2
root@db 04:07:  [aaaa]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

结论：session1对表aaa同时进行read，write锁，需要使用别名。对表进行select,update操作正常，如果使用insert into aaa select * from aaa as t1;需要加上别名。session 2对表aaa即没有读权限，又没有写权限。

5、对表aaa进行read表锁，并使用别名

session 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
root@db 03:47:  [aaaa]>  LOCK TABLE aaa AS t1 READ;
Query OK, 0 rows affected (0.00 sec)

root@db 03:47:  [aaaa]> select * from aaa;
ERROR 1100 (HY000): Table 'aaa' was not locked with LOCK TABLES
root@db 03:47:  [aaaa]> select * from aaa as t1;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | e    | 11111111111 |
|  2 | e    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
|  5 | f    | 5555555     |
+----+------+-------------+
5 rows in set (0.00 sec)

session 2

1
2
3
4
5
6
7
8
9
10
11
 select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | e    | 11111111111 |
|  2 | e    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
|  5 | f    | 5555555     |
+----+------+-------------+
5 rows in set (0.00 sec)

session 1

1
2
root@db 03:48:  [aaaa]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

结论：session 1对表aaa加别名read表锁，session1查询需要使用别名，直接查询无效，session2对表aaa有读权限，无写权限

6、对表aaa使用write表锁，并添加别名

session 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
root@db 03:51:  [aaaa]> LOCK TABLE aaa AS t1 write;
Query OK, 0 rows affected (0.00 sec)

root@db 03:53:  [aaaa]> 
root@db 03:53:  [aaaa]> select * from aaa;
ERROR 1100 (HY000): Table 'aaa' was not locked with LOCK TABLES
root@db 03:53:  [aaaa]> select * from aaa as t1;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | e    | 11111111111 |
|  2 | e    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
|  5 | f    | 5555555     |
+----+------+-------------+
5 rows in set (0.00 sec)

root@db 03:53:  [aaaa]> update aaa set name='e' where id =1;
ERROR 1100 (HY000): Table 'aaa' was not locked with LOCK TABLES
root@db 03:54:  [aaaa]> update aaa as t1 set name='e' where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

session 2

1
2
root@db 03:55:  [aaaa]> select * from aaa;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session 1

1
2
root@db 03:55:  [aaaa]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

结论：session 1对表aaa加别名write表锁，session1查询和更改需要使用别名，直接查询和更改无效，session2对表aaa无读权限，无写权限

##7、额外提示：

1
LOCK TABLES或者UNLOCK TABLES，当应用于分区表时，始终锁定或解锁整个表; 这些语句不支持分区锁定修剪
mysql
目录
  1. 1. 0、mysql版本
  2. 2. 1、创建实验表，内容如下：
  3. 3. 2、开启两个会话，session1、session2,对表aaa进行read表锁
  4. 4. 3、开启两个会话，session1、session2,对表aaa进行write表锁
  5. 5. 4、开启两个会话，session1、session2,对表aaa进行write&read表锁
  6. 6. 5、对表aaa进行read表锁，并使用别名
  7. 7. 6、对表aaa使用write表锁，并添加别名

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog