# 表级锁
# 1. 表锁的基本概念
表级锁每次操作锁住整张表,锁定粒度大,发生锁冲突的概率大,并发度最低,支持Myisam、InnoDB、BDB等存储引擎.
- 表级锁主要分为以下三类
1) 表锁
2) 元数据锁 (meta data lock,MDL)
3) 意向锁
1
2
3
2
3
-- 加锁
lock table $table_name read/write;
-- 释放锁
unlock tables/ 直接断开客户端连接
/*
1. 如果一个客户端 A 对表 t1 加了读锁,则客户端 B 可以读(但是不可以当前读)
2. 当前读操作也被阻塞
select * from $table_name for update;
*/
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 2. 表锁的使用(read/write lock)
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
/*read lock*/
-- 加锁
mysql> lock tables course read;
Query OK, 0 rows affected (0.00 sec)
-- 正常读
mysql> select * from course;
+----+--------+
| id | name |
+----+--------+
| 1 | Java |
| 2 | PHP |
| 3 | MySQL |
| 4 | Hadoop |
+----+--------+
4 rows in set (0.00 sec)
-- 当前回话更新直接报错
mysql> update course set name='Golang' where id = 4;
ERROR 1100 (HY000): Table 'student_course' was not locked with LOCK TABLES
-- 释放锁
mysql> unlock tables;
/*write lock*/
mysql> lock tables course write;
/*
1. 自己可以读写读写都阻塞
2. 其他会话读写都会阻塞
*/
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
35
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
35
session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 正常读
mysql> select * from course;
+----+--------+
| id | name |
+----+--------+
| 1 | Java |
| 2 | PHP |
| 3 | MySQL |
| 4 | Hadoop |
+----+--------+
4 rows in set (0.00 sec)
-- 写阻塞
mysql> update course set name='Golang' where id = 4;
-- session1 释放锁之后正常更新,阻塞时长: 1 min 44.98 sec
mysql> update course set name='Golang' where id = 4;
Query OK, 1 row affected (1 min 44.98 sec)
Rows matched: 1 Changed: 1 Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 3. 元数据锁
Mysql 自己内部维护的锁(自动加锁解锁),用于保证 DML & DQL 语句和 DDL 语句不会冲突
1) 当执行 DML & DQL 语句时,会自动对当前表加 MDL 读锁
2) 当执行 DDL 语句修改表结构时,会自动对当前表加 MDL 写锁
3) MDL 锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作
4) 当一张增删改查的时候,加MDL读锁(共享锁); 当对表结构进行变更操作时,加MDL写锁(排他锁)
1
2
3
4
2
3
4
| 对应sql | 锁类型 | 说明 |
|---|---|---|
| locak tables $table read/write | SHARED_READ_ONLY/SHARED_READ_ONLY_WRITE | - |
| select/select ... lock in share mode | SHARED_READ | 与SHARED_READ/SHARED_WRITE兼容,EXCLUSIVE互斥 |
| insert/update/delete/select ... for update | SHARED_READ_write | 与SHARED_READ/SHARED_WRITE兼容,EXCLUSIVE互斥 |
| alert table... | EXCLUSIVE | 与其他MDL都互斥 |
查询元数据锁
mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+----------------+------------------+---------------+
| object_type | object_schema | object_name | lock_type | lock_duration |
+-------------+--------------------+----------------+------------------+---------------+
| TABLE | my_test | course | SHARED_READ_ONLY | TRANSACTION |
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION |
+-------------+--------------------+----------------+------------------+---------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 4. 意向锁
Mysql 自己内部维护的锁,避免行级锁 & 表级锁冲突,当加行级锁时,会自动为该表加意向锁,当需要加表级锁时会检查意向锁,如果没有意向锁,则加锁成功
1) 意向共享锁(IS):由语句select … lock in share mode添加。与表锁共享锁(read)兼容,与表锁排他锁(write)互斥。
2) 意向排他锁(IX):由insert、update、delete、select…for update添加。与表锁共享锁(read)及排他锁(write)都互斥,意向锁之间不会互斥。
意向锁的作用就是当一个事务在需要获取资源锁定的时候,如果遇到自己需要的资源已经被排他锁占用的时候,该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁,那么就在表上面添加一个意向共享锁。而如果自己需要的是某行(或者某些行)上面添加一个排他锁的话,则先在表上面添加一个意向排他锁。
意向共享锁可以同时并存多个,但是意向排他锁同时只能有一个存在。
所以,可以说InnoDB的锁定模式实际上可以分为四种:共享锁(S),排他锁(X),意向共享锁(IS)和意向排他锁(IX)
意向锁更多是一个标志作用,意向锁之间不会排斥就已经说明了这点。
当我们添加的行锁类型为共享锁时,意向锁的类型就为共享锁,当我们添加的行锁类型为排他锁时,意向锁的类型就为排他锁。
需要注意的是,一旦事务提交了,意向共享锁、意向排他锁,都会自动释放。
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
意向锁查看使用
- session1
-- 开启一个事务
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
-- 加上一个行锁
mysql> select * from course where id = 1 lock in share mode;
+----+------+
| id | name |
+----+------+
| 1 | Java |
+----+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
- session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 可以常看到course中存在两个意向锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| my_test | course | NULL | TABLE | IS | NULL |
| my_test | course | PRIMARY | RECORD | S,REC_NOT_GAP | 1 |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)
-- 分别加读锁
mysql> lock tables course read;
Query OK, 0 rows affected (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
/*
当我们添加表共享读锁时,是能成功添加的,因为此时意向锁的类型是共享锁,但是当我们添加表独占写锁时,就会被阻塞
下边可以看出,一直阻塞到session1中涉及锁的回话已经提交了,独享锁才成功.
*/
-- 写锁阻塞
mysql> lock table course write;
Query OK, 0 rows affected (1 min 20.28 sec)
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
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