MySQL鎖之三：MySQL的共享鎖與排它鎖編碼演示

weixin_34162629發表於2017-11-17
MySql

一、行鎖之MySQL  使用SELECT ... FOR UPDATE 做事務寫入前的確認

以MySQL 的InnoDB 為例，預設的Tansaction isolation level 為REPEATABLE READ。

在SELECT 的讀取鎖定主要分為兩種方式：

SELECT ... LOCK IN SHARE MODE

SELECT ... FOR UPDATE

這兩種方式在事務(Transaction) 進行當中SELECT 到同一個資料表時，都必須等待其它事務資料被提交(Commit)後才會執行。而主要的不同在於LOCK IN SHARE MODE 在有一方事務要Update 同一個表單時很容易造成死鎖 。

簡單的說，如果SELECT 後面若要UPDATE 同一個表單，最好使用SELECT ... UPDATE。

檢視死鎖參考：《mysql 檢視死鎖和去除死鎖》

舉個例子說明：賬號餘額的更新業務

1.1、示例1：相同主鍵值的更新，第2次的select for update會阻塞 (明確指定主鍵，並且有此資料，row lock)

會話1開啟一個事務：

SET AUTOCOMMIT=off;
BEGIN;
SELECT * FROM account_data.account WHERE account_id='0064ca796b7d450c9443bc540b2defc1' FOR UPDATE;
SELECT SLEEP(160);
ROLLBACK;

新開啟會話2，為相同行執行for update的事務：

mysql> SET AUTOCOMMIT=off;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account_data.account WHERE account_id='0064ca796b7d450c9443bc540b2defc1' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> SELECT SLEEP(160);

新開啟會話3，檢視lock情況：

SELECT * FROM `information_schema`.innodb_locks;
SELECT * FROM `information_schema`.INNODB_LOCK_WAITS;

 

1.2、示例2：不同主鍵值的更新，第2次的select for update不會影響 (明確指定主鍵，若查無此資料，無lock)

會話1開啟一個事務：

SET AUTOCOMMIT=off;
BEGIN;
SELECT * FROM account_data.account WHERE account_id='0064ca796b7d450c9443bc540b2defc1' FOR UPDATE;
SELECT SLEEP(160);
ROLLBACK;

新開啟會話2，為相同行執行for update的事務：

mysql> SET AUTOCOMMIT=off;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account_data.account WHERE account_id='01213556b4704055bb7b1397780e8a6e' FOR UPDATE;
mysql> SELECT SLEEP(160);

新開啟會話3，檢視lock情況：

mysql> SELECT * FROM `information_schema`.innodb_locks\G;
Empty set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql>

 

1.3、示例3：相同索引值的更新，第2次的select for update會阻塞

會話1開啟一個事務：

SET AUTOCOMMIT=off;
BEGIN;
SELECT * FROM account_data.account WHERE user_id='97189ba5dc624939873d39177dccf232' FOR UPDATE;
SELECT SLEEP(160);
ROLLBACK;

新開啟會話2，為相同行執行for update的事務：

mysql> SET AUTOCOMMIT=off;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account_data.account WHERE user_id='97189ba5dc624939873d39177dccf232' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> SELECT SLEEP(160);

新開啟會話3，檢視lock情況：

SELECT * FROM `information_schema`.innodb_locks;

結果如下：

 

鎖的資料是我複合索引的兩列值，我的索引如下：

SELECT * FROM `information_schema`.INNODB_LOCK_WAITS;

結果如下：

1.4、其它場景：

假設有個表單products ，裡面有id 跟name 二個欄位，id 是主鍵。

例1: (明確指定主鍵，並且有此資料，row lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;

例2: (明確指定主鍵，若查無此資料，無lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (無主鍵，table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主鍵不明確，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例5: (主鍵不明確，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE 僅適用於InnoDB，且必須在事務區塊(BEGIN/COMMIT)中才能生效。

注2: 要測試鎖定的狀況，可以利用MySQL 的Command Mode ，開二個視窗來做測試。

 

鎖超時引數：innodb_rollback_on_timeout見《MySQL鎖之二：鎖相關的配置引數》

mysql> SHOW VARIABLES LIKE 'innodb_rollback_on_timeout%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)

mysql>

 

二、表鎖演示

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

3.1.1、表顯式鎖：

顯式鎖通過lock tables 和unlock tables完成。同時表鎖還分為讀鎖和寫鎖。

早就聽說lock tables和unlock tables這兩個命令，從字面也大體知道，前者的作用是鎖定表，後者的作用是解除鎖定。但是具體如何用，怎麼用，不太清楚。今天詳細研究了下，總算搞明白了2者的用法。

lock tables 命令是為當前執行緒鎖定表.這裡有2種型別的鎖定，一種是讀鎖定，用命令 lock tables tablename read;另外一種是寫鎖定，用命令lock tables tablename write.下邊分別介紹：

3.1.1.1、 lock table 讀鎖定

如果一個執行緒獲得在一個表上的read鎖，那麼該執行緒和所有其他執行緒只能從表中讀資料，不能進行任何寫操作。

下邊我們測試下，測試表為user表。（user表必須為Myisam表）

不同的執行緒，可以通過開多個命令列MySQL客戶端來實現：

時刻點

執行緒A（命令列視窗A）

執行緒B（命令列視窗B）

 

 

 

1

mysql> lock tables user read;

Query OK, 0 rows affected (0.00 sec)

mysql>

對user表加讀鎖定。

 

2

mysql> select * from user;

+------+-----------+

| id   | name      |

+------+-----------+

|   22 | abc       |

|  223 | dabc      |

| 2232 | dddabc    |

|   45 | asdsagd   |

|   23 | ddddddddd |

+------+-----------+

5 rows in set (0.00 sec)

mysql>

自己的讀操作未被阻塞

mysql> select * from user;

+------+-----------+

| id   | name      |

+------+-----------+

|   22 | abc       |

|  223 | dabc      |

| 2232 | dddabc    |

|   45 | asdsagd   |

|   23 | ddddddddd |

+------+-----------+

5 rows in set (0.00 sec)

mysql>

其他執行緒的讀也未被阻塞

3

mysql> insert into user values(12,'test');

ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated

mysql>

發現本執行緒的寫操作被阻塞

mysql> insert into user values(22,'2test');

發現沒有任何反應，一直等待中，說明沒有得到寫鎖定，一直處於等待中。

4

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

mysql>

釋放讀鎖定。

mysql> insert into user values(22,'ddd');

Query OK, 1 row affected (1 min 27.25 sec)

mysql>

線上程A釋放讀鎖後，執行緒B獲得了資源，剛才等待的寫操作執行了。

5

mysql> lock tables user read local;

Query OK, 0 rows affected (0.00 sec)

mysql>

獲得讀鎖定的時候增加local選項。

mysql> insert into user values(2,'b');

Query OK, 1 row affected (0.00 sec)

mysql>

發現其他執行緒的insert未被阻塞。

6

 

mysql> update user set name  = 'aaaaaaaaaaaaaaaaaaaaa' where id = 1;

但是其他執行緒的update操作被阻塞了。

 

注意：user表必須為Myisam表，以上測試才能全部OK，如果user表為innodb表，則lock tables user read local命令可能沒有效果，也就是說，如果user表為innodb表，第6時刻將不會被阻塞，這是因為INNODB表是事務型的，對於事務表，例如InnoDB和BDB，--single-transaction是一個更好的選項，因為它不根本需要鎖定表。

3.1.1.2、 lock table 寫鎖定

如果一個執行緒在一個表上得到一個   WRITE   鎖，那麼只有擁有這個鎖的執行緒可以從表中讀取和寫表。其它的執行緒被阻塞。

寫鎖定的命令：lock tables user write         (user表為Myisam型別的表)

參考如下測試：

時刻點

執行緒A（命令列視窗A）

執行緒B（命令列視窗B）

 

 

 

1

mysql> lock tables user write;

Query OK, 0 rows affected (0.00 sec)

對user表加寫鎖定。

 

2

mysql> select * from user;

+----+-----------------------+

| id | name                  |

+----+-----------------------+

|  1 | aaaaaaaaaaaaaaaaaaaaa |

|  2 | b                     |

+----+-----------------------+

2 rows in set (0.00 sec)

自己可以繼續進行讀操作

mysql> select * from user;

其他執行緒讀操作被阻塞。

3

mysql> unlock tables ;

Query OK, 0 rows affected (0.00 sec)

釋放鎖定。

  

4

  

mysql> select * from user;

+----+-----------------------+

| id | name                  |

+----+-----------------------+

|  1 | aaaaaaaaaaaaaaaaaaaaa |

|  2 | b                     |

+----+-----------------------+

2 rows in set (32.56 sec)

其他執行緒獲得資源，可以讀資料了。

以上所有結果均在MySQL 5.4.3下測試通過。

相關文章