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;

參考資料 : https://www.mysql.tw/2023/05/mysql-lock.html

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。

實作範例

連線#1

執行 Lock table t read; 以後,只能針對該表單t讀取資料,不能再去讀取其他表單。

連線#1

執行 Lock table t read; 以後,只能針對該表單t讀取資料,不能執行寫入資料。

連線#2

連線#1執行 Lock table t read; 以後,連線#2可以read,但是insert要等待。

連線#1 UNLOCK TABLES; 之後,連線#2的insert into才能執行。



(2) TABLE level LOCK for WRITE

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

UNLOCK TABLES;
解除剛剛的LOCK。

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

連線#1
執行 Lock table t write; 以後,只能針對該表單t讀寫資料,不能再去讀取其他表單。

連線#1
連線#1執行 Lock table t write; 以後,可以針對該表單t讀取資料/寫入資料。


連線#2
連線#1執行 Lock table t write; 以後,連線#2讀取資料/寫入資料~都需要等待。


(3) 交易(transaction)方式處理鎖定。



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

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

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



【範例一】
連線一
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;


連線二 (在連線一完成select動作後進來)
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並且更新一筆資料。
begin;
select * from t where id=1 for update;

連線二使用For Update並且更新同一筆資料。
begin;
select * from t where id=1 for update;

然後~ 連線一
Update t set f1=10 where id=1;

然後~ 連線二
Update t set f1=10 where id=1;

會發生甚麼事情?

範例八

連線一使用lock in share mode並且更新一筆資料。
begin;
select * from t where id=1 lock in share mode;

連線二使用lock in share mode並且更新同一筆資料。
begin;
select * from t where id=1 lock in share mode;

然後~ 連線一
Update t set f1=100 where id=1;

然後~ 連線二
Update t set f1=100 where id=1;

會發生甚麼事情?

【結論】

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

張貼留言

0 留言