MySQL row level lock 實驗

-- 基本資訊查看
SHOW VARIABLES LIKE "%version%";
SHOW global status like 'Innodb_row_lock%';
SELECT @@tx_isolation;
SHOW OPEN TABLES
SHOW full processlist;
SHOW engine innodb status;

-- 查詢目前 DB 有哪些鎖
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.innodb_locks;

-- 鎖定與解鎖某張表讀寫
LOCK TABLE orders READ;
LOCK TABLE orders WRITE;
UNLOCK TABLES;

-- 在沒有鎖的情況下查詢與更新
SELECT * FROM orders where id = 1;
UPDATE orders SET cs_id = 1 WHERE id = 1;

-- 在有鎖的情況下查詢與更新
BEGIN;
SELECT * FROM orders WHERE id = 1 AND cs_id = 0 FOR UPDATE;
UPDATE orders SET cs_id = 2 WHERE id = 1 AND cs_id = 0;
COMMIT;

測試多連線情況可透過多個 Terminal 連進 DB & 對某筆資料上鎖

連線指令

mysql -h 127.0.0.1 -u root -p

MySQL row level lock 實驗#

MySQL row level lock 實驗