InnoDB鎖機制之表級鎖

一、前言

表級鎖是MySQL中鎖定粒度最大的一種鎖，表示對目前操作的整張表加鎖，它實作簡單，資源消耗較少，被大部分MySQL引擎支援。最常使用的MYISAM與INNODB都支援表級鎖定。

• 表級鎖定分為: 表共享讀鎖（共享鎖）與表獨占寫鎖（排他鎖）。
• 特點: 開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖沖突的機率最高，并發度最低。

二、資料準備

-- 建立表mylock01,選擇 MYISAM存儲引擎
CREATE TABLE mylock01(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20)
)ENGINE MYISAM;

-- 建立表mylock02,選擇 MYISAM存儲引擎
CREATE TABLE mylock02(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20)
)ENGINE MYISAM;

-- mylock01表中向插入資料
INSERT INTO mylock01(title) VALUES('a1');
INSERT INTO mylock01(title) VALUES('b1');
INSERT INTO mylock01(title) VALUES('c1');
INSERT INTO mylock01(title) VALUES('d1');
INSERT INTO mylock01(title) VALUES('e1');

-- mylock02表中向插入資料
INSERT INTO mylock02(title) VALUES('a');
INSERT INTO mylock02(title) VALUES('b'); 
INSERT INTO mylock02(title) VALUES('c'); 
INSERT INTO mylock02(title) VALUES('d'); 
INSERT INTO mylock02(title) VALUES('e');           

三、加鎖文法

1、查詢表中加過的鎖

-- 0表示沒有加鎖,目前的所有資料庫表都沒有加鎖
SHOW OPEN TABLES;

-- 查詢加鎖的表,條件In_use 大于0
SHOW OPEN TABLES WHERE In_use > 0;           

2、手動加表鎖

文法格式: LOCK TABLE 表名 READ(WRITE), 表名2 READ(WRITE), 其他;

-- 為mylock01加讀鎖(共享鎖),給mylock02加寫鎖(排它鎖)
mysql> lock table mylock01 read,mylock02 write;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where In_use > 0;
+-----------+----------+--------+-------------+
| Database | Table | In_use | Name_locked |
+-----------+----------+--------+-------------+
| test_lock | mylock01 | 1 | 0 |
| test_lock | mylock02 | 1 | 0 |
+-----------+----------+--------+-------------+           

3、釋放鎖,解除鎖定

unlock tables;           

四、加讀鎖測試

開啟session1和session2兩個會話視窗

1、在session1中對mylock01表加讀鎖

lock table mylock01 read;           

2、對mylock01進行讀操作, 兩個視窗都可以讀。

select * from mylock01;           

3、在session1進行寫操作,失敗

mysql> update mylock set title='a123' where id=1;
ERROR 1100 (HY000): Table 'mylock' was not locked with LOCK TABLES           

4、在session1中 讀取其他表,比如讀取mylock02表,讀取失敗.不能讀取未鎖定的表

mysql> select * from mylock02;
ERROR 1100 (HY000): Table 'mylock02' was not locked with LOCK TABLES           

5、在session2中 對 mylock01表 進行寫操作, 執行後一直阻塞

update mylock01 set title='a123' where id = 1;           

6、session1解除 mylock01 的鎖定,session2 的修改執行。

unlock tables;

mysql> update mylock01 set title='a123' where id = 1;
Query OK, 1 row affected (47.83 sec)           

總結:

對MyISAM表的讀寫操作,加讀鎖,不會阻塞其他程序對同一表的讀請求,但是會阻塞對同一表的寫請求。 隻有當讀鎖釋放後才會執行其他程序的寫操作。

五、加寫鎖測試

1、在session1中 對mylock01表加寫

lock table mylock01 write;           

2、在session1中, 對 mylock01 進行讀寫操作, 都是可以進行的

select * from mylock01 where id = 1;
update mylock01 set title = 'a123' where id = 1;           

3、在session1中讀其他表, 還是不允許

mysql> select * from mylock02 where id = 1;
ERROR 1100 (HY000): Table 'mylock02' was not locked with LOCK TABLES           

4、在session2 中讀mylock01 表, 讀操作被阻塞

select * from mylock01;           

5、在session2 中 對mylock01表進行寫操作, 仍然被阻塞

update mylock01 set title = 'a456' where id = 1;           

6、釋放鎖, session2操作執行執行.

unlock tables;           

六、總結

對MyISAM表加寫鎖, 會阻塞其他程序對同一表的讀和寫操作,隻有當寫鎖釋放後,才會執行其他程序的操作。