理解mysql锁（2）表级锁定

MyISAM 存储引擎使用的锁定机制完全是由 MySQL 提供的表级锁定实现。

mysql的表级锁定主要有两种：写锁和读锁

对write写锁，MySQL使用的表锁定方法原理如下：
* 如果在表上没有锁，在它上面放一个写锁。
* 否则，把锁定请求放在写锁定队列中。
对read读锁，MySQL使用的表锁定方法原理如下：
* 如果在表上没有写锁定，把一个读锁定放在它上面。
* 否则，把锁请求放在读锁定队列中。

当一个锁定被释放时，锁定可被写锁定队列中的线程得到，然后是读锁定队列中的线程。这意味着，如果你在一个表上有许多更新，SELECT语句将等待直到没有更多的更新。
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 18    |
| Table_locks_waited    | 0     |
+-----------------------+-------+

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

在 MySQL 中,主要通过四个队列来维护这两种锁定:两个存放当前正在锁定中的读和写锁定信息,另外两个存放等待中的读写锁定信息,如下:

• Current read-lock queue (lock->read)
• Pending read-lock queue (lock->read_wait)
• Current write-lock queue (lock->write)
• Pending write-lock queue (lock->write_wait)

当客户端请求写锁时，mysql首先检查在Current write-lock queue是否已经有锁定相同资源到信息存在，如果Current write-lock queue没有，则再检查Pending write-lock queue ，如果在Pending write-lock queue 中找到了，则自己也需要进入该等待队列；反之，如果在Pending write-lock queue 找不到，则再检测Current read-lock queue，如果有锁定存在，则同样需要进入Pending write-lock queue。如果一开始就检测到Current write-lock queue中有锁定相同资源的写锁存在，那么就直接进入Pending write-lock queue。

读请求和写等待队列中的写锁请求的优先级规则主要为以下规则决定:

1. 除了 READ_HIGH_PRIORITY 的读锁定之外,Pending write-lock queue 中的 WRITE 写锁定能够阻塞所有其他的读锁定;
2. READ_HIGH_PRIORITY 读锁定的请求能够阻塞所有 Pending write-lock queue 中的写锁定;
3. 除了 WRITE 写锁定之外,Pending write-lock queue 中的其他任何写锁定都比读锁定的优先级低。

表级锁在下列几种情况下比行级锁更优越:
1. 很多操作都是读表。
2. 在严格条件的索引上读取和更新,当更新或者删除可以用单独的索引来读取得到时:
3. UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
4. DELETE FROM tbl_name WHERE unique_key_col=key_value;
5. SELECT 和 INSERT 语句并发的执行,但是只有很少的 UPDATE 和 DELETE 语句。
6. 很多的扫描表和对全表的 GROUP BY 操作,但是没有任何写表。

测试：

session A
显示地给t1表加读锁定
mysql> lock table t1 read;
Query OK, 0 rows affected (0.00 sec)
自己的读操作未被阻塞：
mysql> select * from t1;
+------+
| i    |
+------+
|    1 |
|    2 |
|    5 |
+------+
3 rows in set (0.00 se）
session B
其他进程的读操作也未被阻塞：
mysql> select * from t1;
+------+
| i    |
+------+
|    1 |
|    2 |
|    5 |
+------+
3 rows in set (0.00 sec)
session A
mysql> update t1 set i=3 limit 1;
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated
session B
mysql> update t1 set i=3 limit 1;
直接被阻塞了
session A
解除读锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
session B
在session A释放锁定资源后，session B获得了资源，更新成功
mysql> update t1 set i=3 limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
session A
获取读锁的时候增加local选项
mysql> lock table t1 read local;
Query OK, 0 rows affected (0.00 sec)
session B
其他session的insert 未被阻塞
mysql> insert into t1 values(6);
Query OK, 1 row affected (0.00 sec)
然而，其他session的update被阻塞了
mysql> update t1 set i=3 limit 1;
直接被阻塞鸟
session A
这次加写锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)
自己的session可以继续读：
mysql> select * from t1;
+------+
| i    |
+------+
|    3 |
|    2 |
|    5 |
|    6 |
+------+
4 rows in set (0.00 sec)
session B：
其他session的读被阻塞了
mysql> select * from t1;
直接被阻塞鸟
session A
释放锁定资源
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
session B
其他session可以获得资源了
mysql> select * from t1;
+------+
| i    |
+------+
|    3 |
|    2 |
|    5 |
|    6 |
+------+
4 rows in set (0.00 sec)
session A
通过DDL获取write_allow_read类型的写锁定
mysql> alter table t1 add constraint t1_pk primary key(i);
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0
session B
其他session的读未被阻塞
mysql> select * from t1;
+---+
| i |
+---+
| 2 |
| 3 |
| 5 |
| 6 |
+---+
4 rows in set (0.00 sec)