MySQL Lock : Table Lock與Row Lock

I 、基本觀念

問題:什麼是 Lock (鎖定) ?
答案:Lock 的主要目的是避免資料發生錯誤,把不應該進行動作的指令排除在外,並讓應該進行動作的指令能夠順利完成。

問題:什麼是Table Lock (表單鎖定) ? 什麼是Row Lock (紀錄鎖定)?
答案:表單鎖定就是將整個資料表鎖定,讓其他連線無法讀取及異動,直到資料處理完畢為止。紀錄鎖定就是將指定的紀錄鎖定,讓其他連線無法讀取及異動,直到資料處理完畢為止。

問題:InnoDB與MyISAM的鎖定有何差別?
答案:MyISAM 沒有交易功能 (Transaction),若要避免多個連線交互執行 SQL 指令,造成資料錯亂,只好使用鎖定資料表 (Table Lock) 的方式,InnoDB則可以使用Table Lock 與Row Lock。

問題:MySQL不同版本的鎖定有何差別?
答案:MySQL不同版本的鎖定原理一樣,只是語法上有些差異。

例如 :
MySQL 5.5 使用 Select ... lock in share mode;
MySQL 5.5 使用 Select ... for update;
MySQL 8.0 還可以使用 Select ... for share;
MySQL 8.0 還可以使用 Select ... for update NOWAIT;

II、實際範例

以下來看看這個版本的MySQL怎麼進行資料鎖定吧。

檢查MYSQL版本
SHOW VARIABLES LIKE "%version%";



建立一個表單
CREATE TABLE T (
ID INT NOT NULL,
F1 INT,
PRIMARY KEY (ID));

插入資料
INSERT INTO T (ID,F1) VALUES (1,2), (2,1), (3,2), (4,4);

(1) TABLE level LOCK for READ

LOCK TABLE T READ;
這是屬於表單鎖定,也就是鎖定整個表單。自身連線只能針對該表單T讀取資料,不能寫入更改該表單T,也不能再去讀取其他表單。其他連線可以讀取該表單T,但是不能寫入更改該表單T(會進入等待,直到解除)。

UNLOCK TABLES;
解除剛剛的LOCK。

(2) TABLE level LOCK for WRITE

LOCK TABLE T WRITE;
這是屬於表單鎖定,也就是鎖定整個表單。自身連線可以針對該表單T讀取及寫入更改資料,但是也不能操作其他表單。其他連線禁止對該表單T讀取及寫入更改資料,要寫入更改該表單T,會進入等待,直到解除。

UNLOCK TABLES;
解除剛剛的LOCK。

這個指令 SHOW OPEN TABLES,可以看到那些表單已經被鎖定。

(3) 當set autocommit=0時,可以使用交易(transaction)方式,以下列指令來處理鎖定。
因為set autocommit=1時,因為已經自動commit而結束鎖定。
所以使用下列的鎖定方式,要先確定先執行set autocommit=0。

方式一
BEGIN;
SELECT … FOR UPDATE;
... 執行後續程序
再執行COMMIT; 或是 ROLLBACK;

方式二
BEGIN;
SELECT … LOCK IN SHARE MODE;
... 執行後續程序
再執行COMMIT; 或是 ROLLBACK;

以上這兩個方式,不一定是表單鎖定(table level lock)或是紀錄鎖定(row level lock),要看是否針對主鍵。

以下範例是在set autocommit=0;情況下執行。

【範例一】
連線一
begin;
select * from t where f1=4 for update;

連線二
update t set f1=10 where id=1;
這個指令也不能執行而進入等待,因為連線一的鎖定,where f1=4不是指定在主鍵上,所以變成表單鎖定(table level lock)。

【範例二】
連線一
begin;
select * from t where id=1 for update;

連線二
update t set f1=10 where id=2;
這是可以執行的,因為連線一是紀錄鎖定(row level lock),只鎖定在id=1這筆紀錄。

但是連線二
update t set f1=10 where id=1;
就會被鎖定而進入等待,必須等連線一的commit後才能執行。

【範例三】
連線一
begin;
select * from t where id=1 lock in share mode;
update t set f1=5 where id=1;
commit;

連線二
update t set f1=5 where id=1;
先進入等待,然後當連線一有update動作後,會出現deadlock訊息。

【範例四】
連線一
begin;
select * from t where id=1 for update;

連線二
begin;
select * from t where id=1 for update;
因為連線一已經紀錄鎖定,所以會進入等待。
但是如果連線二是
select * from t where id=2 for update;
就可以執行,因為是另外一個紀錄鎖定。

【範例五】
連線一
begin;
select * from t where id=1 lock in share mode;

連線二
begin;
select * from t where id=1 lock in share mode;

以上都可以運作,但是在update執行時,連線二的update執行會出現deadlock訊息。

【範例六】
連線一
begin;
select * from t where id=1 lock in share mode;

連線二
begin;
select * from t where id=1 for update;
會進入等待,但是當連線一有update程序時,連線二會出現deadlock訊息。

範例七

使用連線一與連線二

連線一使用For Update並且更新一筆資料。
連線二使用For Update並且更新同一筆資料。

連線一使用lock in share mode並且更新一筆資料。
連線二使用lock in share mode並且更新同一筆資料。

【結論】

LOCK TABLE table_name [READ/WRITE] 是表單整個鎖定,等待UNLOCK TABLES 來解除
鎖定。

SELECT … FOR UPDATE 與 SELECT … LOCK IN SHARE MODE 可以是表單整個鎖定,也可以是紀錄鎖定。如果指定的紀錄在主鍵上,則只有鎖定該紀錄上。

因為LOCK IN SHARE MODE是分享鎖定(share lock),所以其他連線如果有變更資料的動作,其他連線會收到deadlock訊息。但是FOR UPDATE是專有鎖定(exclusive lock),他會等待能夠鎖定後進入處理,不會收到其他連線的更新警告訊息。

【其他】

//檢查innodb lock 狀態
show global status like 'Innodb_row%';

//檢查isolation level
SELECT * FROM information_schema.session_variables WHERE variable_name = 'tx_isolation';
或是
SELECT @@tx_isolation;


更改 ISOLATION LEVEL 指令: 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

ISOLATION LEVEL有以下四種~
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ (預設)
SERIALIZABLE

參考資料
https://xyz.cinc.biz/2013/05/mysql-transaction.html
http://geekdirt.com/blog/shared-and-exclusive-locks/
http://www.xpertdeveloper.com/2011/11/row-locking-with-mysql/
http://www.cnblogs.com/langtianya/p/5138598.html
http://www.mysqltutorial.org/mysql-table-locking/
http://www.bigdbahead.com/?p=23
http://highscalability.com/blog/2011/2/10/database-isolation-levels-and-their-effects-on-performance-a.html

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理