MySQL的lock tables和unlock tables使用詳解

在mysql中鎖表是一個比較有學問的東西了，下面我來給各位介紹一下MySQL的lock tables和unlock tables使用方法。

lock tables 命令是為當前線程鎖定表.這裡有2種類型的鎖定，一種是讀鎖定，用命令 lock tables tablename read;另外一種是寫鎖定，用命令lock tables tablename write.下邊分別介紹：
1. lock table 讀鎖定
如果一個線程獲得在一個表上的read鎖，那麼該線程和所有其他線程只能從表中讀數據，不能進行任何寫操作。
下邊我們測試下，測試表為user表。
不同的線程，可以通過開多個命令行MySQL客戶端來實現：

時刻點
 線程A（命令行窗口A）
 線程B（命令行窗口B）

 代碼如下 復制代碼  
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是一個更好的選項，因為它不根本需要鎖定表
 2. lock table 寫鎖定
如果一個線程在一個表上得到一個   WRITE   鎖，那麼只有擁有這個鎖的線程可以從表中讀取和寫表。其它的線程被阻塞。
寫鎖定的命令：lock tables user write.user表為Myisam類型的表。
參考如下測試：
 
時刻點
 線程A（命令行窗口A）
 線程B（命令行窗口B）
 

 代碼如下 復制代碼  
 
mysql> lock tables user write;
Query OK, 0 rows affected (0.00 sec)

對user表加寫鎖定。

 代碼如下 復制代碼  
 
mysql> select * from user;
+----+-----------------------+
| id | name                  |
+----+-----------------------+
|  1 | aaaaaaaaaaaaaaaaaaaaa |
|  2 | b                     |
+----+-----------------------+
2 rows in set (0.00 sec)

自己可以繼續進行讀操作

 代碼如下 復制代碼  mysql> select * from user;

其他線程讀操作被阻塞。

 代碼如下 復制代碼 mysql> unlock tables ;
Query OK, 0 rows affected (0.00 sec)

釋放鎖定。

 代碼如下 復制代碼  
mysql> select * from user;
+----+-----------------------+
| id | name                  |
+----+-----------------------+
|  1 | aaaaaaaaaaaaaaaaaaaaa |
|  2 | b                     |
+----+-----------------------+
2 rows in set (32.56 sec)

其他線程獲得資源，可以讀數據了。
 
 
以上所有結果均在MySQL 5.4.3下測試通過。
 
在MySQL中如何用命令查看表是MyISAM類型還是INNODB類型的？

 代碼如下 復制代碼

show create table ***

ENGINE=MyISAM AUTO_INCREMENT=14696 DEFAULT CHARSET=utf8 |

mysql的鎖表問題

SHOW PROCESSLIST查看數據庫中表的狀態，是否被鎖；

kill id   //殺掉被鎖的表

===================================================

 代碼如下 復制代碼

set autocommit=0;

select * from t1  where uid='xxxx' for update    //在有索引（例如uid）的情況下是行鎖，否則是表鎖

insert into t1 values(1,'xxxxx');

commit;

 

lock tables t1 write|read;

insert into t1 values(2,'xxxxx'); //只有insert

unlock tables;