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) 當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;

連線二 (在連線一完成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