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

lock tables 命令是為當前線程鎖定表.這里有2種類型的鎖定,一種是讀鎖定,用命令 lock tables tablename read;另外一種是寫鎖定,用命令lock tables tablename write.下邊分別介紹.

1.lock table 讀鎖定

如果一個線程獲得在一個表上的read鎖,那么該線程和所有其他線程只能從表中讀數據,不能進行任何寫操作,下邊我們測試下,測試表為user表.

不同的線程,可以通過開多個命令行MySQL客戶端來實現(xiàn),時刻點,線程A（命令行窗口A）,線程B（命令行窗口B）,代碼如下:

1. mysql> lock tables user read; 
2. Query OK, 0 rows affected (0.00 sec) 
3. mysql> 

對user表加讀鎖定,代碼如下:

1.  mysql> select * from user; 
2. +------+-----------+ 
3. | id   | name      | 
4. +------+-----------+ 
5. |   22 | abc       | 
6. |  223 | dabc      | 
7. | 2232 | dddabc    | 
8. |   45 | asdsagd   | 
9. |   23 | ddddddddd | 
10. +------+-----------+ 
11. 5 rows in set (0.00 sec) 
12. mysql> 

自己的讀操作未被阻塞,代碼如下:

1. mysql> select * from user; 
2. +------+-----------+ 
3. | id   | name      | 
4. +------+-----------+ 
5. |   22 | abc       | 
6. |  223 | dabc      | 
7. | 2232 | dddabc    | 
8. |   45 | asdsagd   | 
9. |   23 | ddddddddd | 
10. +------+-----------+ 
11. 5 rows in set (0.00 sec) 
12. mysql> 

其他線程的讀也未被阻塞,代碼如下:

1.  mysql> insert into user values(12,'test'); 
2. ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated 
3. mysql> 

發(fā)現(xiàn)本線程的寫操作被阻塞,代碼如下:

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

發(fā)現(xiàn)沒有任何反應,一直等待中,說明沒有得到寫鎖定,一直處于等待中.代碼如下:

1.  mysql> unlock tables; 
2. Query OK, 0 rows affected (0.00 sec) 
3. mysql> 

釋放讀鎖定.

1.  mysql> insert into user values(22,'ddd'); 
2. Query OK, 1 row affected (1 min 27.25 sec) 
3. mysql> 

在線程A釋放讀鎖后,線程B獲得了資源,剛才等待的寫操作執(zhí)行了,代碼如下:

1.  mysql> lock tables user read local; 
2. Query OK, 0 rows affected (0.00 sec) 
3. mysql> 

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

1.  mysql> insert into user values(2,'b'); 
2. Query OK, 1 row affected (0.00 sec) 
3. mysql> 

發(fā)現(xiàn)其他線程的insert未被阻塞,代碼如下:

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）

代碼如下:

1. mysql> lock tables user write; 
2. Query OK, 0 rows affected (0.00 sec) 

對user表加寫鎖定,代碼如下:

1. mysql> select * from user; 
2. +----+-----------------------+ 
3. | id | name                  | 
4. +----+-----------------------+ 
5. |  1 | aaaaaaaaaaaaaaaaaaaaa | 
6. |  2 | b                     |  --Vevb.com 
7. +----+-----------------------+ 
8. 2 rows in set (0.00 sec) 

自己可以繼續(xù)進行讀操作,代碼如下:mysql> select * from user;

其他線程讀操作被阻塞,代碼如下:

1. mysql> unlock tables ; 
2. Query OK, 0 rows affected (0.00 sec) 

釋放鎖定,代碼如下:

1. mysql> select * from user; 
2. +----+-----------------------+ 
3. | id | name                  | 
4. +----+-----------------------+ 
5. |  1 | aaaaaaaaaaaaaaaaaaaaa | 
6. |  2 | b                     | 
7. +----+-----------------------+ 
8. 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查看數據庫中表的狀態(tài),是否被鎖；

1. kill id   //殺掉被鎖的表 
2. =================================================== 
3.  
4. set autocommit=0; 
5. select * from t1  where uid='xxxx' for update    //在有索引（例如uid）的情況下是行鎖，否則是表鎖 
6. insert into t1 values(1,'xxxxx'); 
7. commit; 
8.  
9. lock tables t1 write|read; 
10. insert into t1 values(2,'xxxxx'); //只有insert 
11. unlock tables;