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

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

1. 表鎖

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

1.1 顯式鎖

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

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

在 MySQL 另一個(gè)會(huì)話(huà)中，對(duì)表 customer 執(zhí)行 lock tables 命令，查詢(xún)會(huì)掛起。

mysql> lock tables customer write;

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

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 隱式鎖

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

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

mysql> select sleep(30) from customer;

在 MySQL 另一個(gè)會(huì)話(huà)中，對(duì)表 customer 執(zhí)行 lock tables 命令，查詢(xún)會(huì)掛起。

mysql> lock tables customer write;

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

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ù)器可以支持全局讀鎖，可以通過(guò) flush tables with read lock 或設(shè)置 read_only=1 來(lái)實(shí)現(xiàn)，全局鎖與任何表鎖都沖突。

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

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

在 MySQL 另一個(gè)會(huì)話(huà)中，對(duì)表 customer 執(zhí)行 lock tables 命令，查詢(xún)會(huì)掛起。

mysql> lock tables customer write;

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

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. 命名鎖

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

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

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

在 MySQL 另一個(gè)會(huì)話(huà)中，對(duì)表 customer 執(zhí)行 rename table 命令，此時(shí)會(huì)話(huà)會(huì)掛起，會(huì)話(huà)狀態(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. 用戶(hù)鎖

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

在 MySQL 會(huì)話(huà)中執(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 另一個(gè)會(huì)話(huà)中，也執(zhí)行 get_lock 命令，嘗試鎖相同的字符串，此時(shí)會(huì)話(huà)會(huì)掛起，會(huì)話(huà)狀態(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ù)器級(jí)別的鎖等待：表鎖、全局鎖、命名鎖、用戶(hù)鎖。

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