MySQL 服務(wù)器級別的鎖等待

使用鎖來控制資源共享的應(yīng)用系統(tǒng)，如何處理鎖的競爭問題是個頭疼事。MySQL 有兩個級別的鎖等待，服務(wù)器級別和存儲引擎級別，本節(jié)重點介紹服務(wù)器級別的鎖等待。

1. 表鎖

表鎖可以是顯式的，也可以是隱式的。

1.1 顯式鎖

通過 lock tables和unlock tables 可以控制顯式鎖。在 MySQL 會話中執(zhí)行 lock tables 命令，在表customer上會獲得一個顯式鎖。

mysql> lock tables customer read;
Query OK, 0 rows affected (0.00 sec)

在 MySQL 另一個會話中，對表 customer 執(zhí)行 lock tables 命令，查詢會掛起。

mysql> lock tables customer write;

在第一個會話中執(zhí)行 show processlist 查看線程狀態(tài)，可以看到線程 13239868 的狀態(tài)為 Waiting for table metadata lock。在 MySQL 中，當(dāng)一個線程持有該鎖后，其他線程只能不斷嘗試獲取。

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 13239801
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 13239868
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 12
  State: Waiting for table metadata lock
   Info: lock tables customer write
2 rows in set (0.00 sec)

1.2 隱式鎖

除了顯式鎖會阻塞這樣的操作，MySQL 在查詢過程中也會隱式地鎖住表。通過 sleep() 函數(shù)可以實現(xiàn)長時間的查詢，然后 MySQL 會產(chǎn)生一個隱式鎖。

在 MySQL 會話中執(zhí)行 sleep(30)，在表 customer上 會獲得一個隱式鎖。

mysql> select sleep(30) from customer;

在 MySQL 另一個會話中，對表 customer 執(zhí)行 lock tables 命令，查詢會掛起。

mysql> lock tables customer write;

在第三個會話中執(zhí)行 show processlist 查看線程狀態(tài)，可以看到線程 13244135 的狀態(tài)為 Waiting for table metadata lock。select 查詢的隱式鎖阻塞了 lock tables 中所請求的顯式寫鎖。

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 13244112
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 6
  State: User sleep
   Info: select sleep(30) from customer
*************************** 2. row ***************************
     Id: 13244135
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 2
  State: Waiting for table metadata lock
   Info: lock tables customer write

2. 全局鎖

MySQL 服務(wù)器可以支持全局讀鎖，可以通過 flush tables with read lock 或設(shè)置 read_only=1 來實現(xiàn)，全局鎖與任何表鎖都沖突。

在 MySQL會 話中執(zhí)行 flush tables 命令，獲得全局讀鎖。

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

在 MySQL 另一個會話中，對表 customer 執(zhí)行 lock tables 命令，查詢會掛起。

mysql> lock tables customer write;

在第一個會話中執(zhí)行 show processlist 查看線程狀態(tài)，可以看到線程 13283816 的狀態(tài)為 Waiting for global read lock。這是一個全局讀鎖，而不是表級別鎖。

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 13283789
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 13283816
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 10
  State: Waiting for global read lock
   Info: lock tables customer write
2 rows in set (0.00 sec)

3. 命名鎖

命名鎖是一種表級別鎖，它是 MySQL 服務(wù)器在重命名或刪除表時創(chuàng)建。命名鎖與普通的表鎖沖突，無論是顯式的還是隱式的表鎖。

在 MySQL會 話中執(zhí)行 lock table s命令，在表 customer上 獲得一個顯式鎖。

mysql> lock tables customer read;
Query OK, 0 rows affected (0.00 sec)

在 MySQL 另一個會話中，對表 customer 執(zhí)行 rename table 命令，此時會話會掛起，會話狀態(tài)為Waiting for table metadata lock：

mysql> rename table customer to customer_1;

mysql> show processlist\G
...
*************************** 2. row ***************************
     Id: 51
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 128
  State: Waiting for table metadata lock
   Info: rename table customer to customer_1

4. 用戶鎖

MySQL 服務(wù)器還可以實現(xiàn)用戶鎖，這種鎖需指定名稱字符串，以及等待超時時間（單位秒）。

在 MySQL 會話中執(zhí)行 get_lock 命令，成功執(zhí)行并持有一把鎖。

mysql> select get_lock('user_1',20);
+------------------------+
| get_lock('user_1',20) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

在 MySQL 另一個會話中，也執(zhí)行 get_lock 命令，嘗試鎖相同的字符串，此時會話會掛起，會話狀態(tài)為User lock。

mysql> select get_lock('user_1',20);
+------------------------+
| get_lock('user_1',20) |
+------------------------+
|                      1 |
+------------------------+

mysql> show processlist\G
...
*************************** 2. row ***************************
     Id: 51
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 3
  State: User lock
   Info: select get_lock('user_1',20)

5. 小結(jié)

本小節(jié)介紹了服務(wù)器級別的鎖等待：表鎖、全局鎖、命名鎖、用戶鎖。

表鎖可以是顯式的，也可以是隱式的。顯式鎖通過 lock tables 和 unlock tables 進行控制，隱式鎖在查詢過程中產(chǎn)生。全局鎖可以通過 flush tables with read lock 或設(shè)置 read_only=1 來 實現(xiàn)，它與任何表鎖都沖突。