MySQL 的鎖依照顆粒度大小能分成全局鎖、表級鎖、行級鎖，而在各個種類又能夠分成以下
全局鎖
表級鎖: Table Lock、 MDL ( Meta Data Lock )、 Intention Lock ( 意向鎖 ) 、 Auto-Inc Lock
行級鎖: Record Lock、Gap Lock、 Next-Key Lock
加上全局鎖後，整個 DB 都會變成唯讀狀態，當執行以下的動作都會被阻塞：
insert, update, deletealter table, drop tablemysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE messages SET content = 'changed'; ERROR 1223 (HY000): Can't execute the query because you have a co nflicting read lock mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
盡量避免使用 Table Lock 會影響性能
Table Lock Table Lock 需顯示的加上，其中又有分
mysql> LOCK TABLE users READ; Query OK, 0 rows affected (0.00 sec) mysql> SELECT id FROM users WHERE id = 1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> UPDATE users SET name = 'Aaron' WHERE id = 1; ERROR 1099 (HY000): Table 'users' was locked with a READ lock and can't be updated mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
mysql> LOCK TABLE users WRITE; │mysql> SELECT id, name FROM users WHERE id = 1; <-- 阻塞 Query OK, 0 rows affected (0.01 sec) │ mysql> UNLOCK TABLE; <-- 移除寫鎖
Meta Data Locks (MDL)
目的： 確保多併發狀態下可以安全、一致地執行讀取或寫入表等操作，而不會出現資料損壞或其他不一致的風險。
種類：
DML: 操作資料庫中資料的 SQL 命令, EX: INSERT, UPDATE, DELETE, SELECT, CALL
DDL: 定義資料結構和修改資料的SQL指令, EX: CREATE, ALTER, DROP, TRUNCATE, RENAME
| DML | DDL |
|---|---|
| 用於操作資料庫內的資料 | 定義資料庫對象，如表格、索引 |
| 變更會影響資料庫中儲存的資料 | 變更會影響資料庫的結構 |
| 事務性，可以在必要時回滾 | 不是事務性，無法回滾 |
為何能夠確保多併發狀態下可以安全、一致地執行讀取或寫入表等操作?
GET SHARED LOCK p1 UPDATE ... FINISH UPDATE OPERATION | | time ----------------------------------------------------> | | p2 ALTER TABLE SHARED LOCK release, Request EXCLUSIVE LOCK get EXCLUSIVE LOCK, and do alter table but SHARED LOCK has been taken so blocked.
如何加上 MDL, 又在什麼時候釋放呢？ 兩種鎖哪一種會優先取得呢？
不需顯示的加上 MDL, 在進行操作時會自動請求鎖
釋放的時機為：
X 鎖會優先於 Ｓ 鎖， 所以如果有一個 transaction 拿到（或正在等待拿到） X 鎖，又遲遲不能結束 transaction。會導致後面的讀寫操作全部卡住。
GET S LOCK release S LOCK t1 | |(commit) time --------------------------------------------------------------> t2 | | | (commit)|| ( request X LOCK ) | GET X LOCK release X LOCK | t3 | | SELECT .. IN SHARED MODE BLOCKED GET S LOCK, execute select
Intention Locks Intention Locks 表示 Transaction 稍後需要對表中的行使用哪種類型的鎖(S 或 X Lock), 其中能分成以下兩種：
在 Transaction 拿到 row level S(X) Lock 前需要先拿到 table IS(IX) LOCK。
表級鎖的相容性:
| X | IX | S | IS | |
|---|---|---|---|---|
| X | 衝突 | 衝突 | 衝突 | 衝突 |
| IX | 衝突 | 相容 | 衝突 | 相容 |
| S | 衝突 | 衝突 | 相容 | 相容 |
| IS | 衝突 | 相容 | 相容 | 相容 |
AUTO-INC LOCK (ref) 用來 generate auto-increment values, 在 innodb_autoinc_lock_mode = 0 時就會使用 AUTO-INC LOCK。 會在執行 “INSERT-like” statements 時自動在 Table 加上 AUTO-INC LOCK 以確保值是自增的，然後在 statement 結束後直接釋放（不是在 transaction）但這樣在大量 Insert 時會有效能問題，因為會 block 其他 transaction 的 insert operation.
A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.
mysql> BEGIN; │mysql> BEGIN; Query OK, 0 rows affected (0.01 sec) │Query OK, 0 rows affected (0.00 sec) │ mysql> SELECT id FROM users WHERE id = 1 FOR UPDATE; │mysql> UPDATE users SET name = 'aaron' WHERE id = 1; <-- blocked +----+ │ | id | │ +----+ │ | 1 | │ +----+ │ 1 row in set (0.00 sec) │
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;防止其他交易將 的值插入15列 中 t.c1，無論該列中是否已經存在任何此類值，因為範圍中所有現有值之間的間隙已被鎖定
mysql> BEGIN; │mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) │Query OK, 0 rows affected (0.00 sec) │ mysql> SELECT id FROM users WHERE id BETWEEN 1 and 10 FOR UPDATE;│mysql> UPDATE users SET name = 'aaron' WHERE id = 12; │Query OK, 1 row affected (0.01 sec) +----+ │Rows matched: 1 Changed: 1 Warnings: 0 | id | │ +----+ │mysql> UPDATE users SET name = 'aaron' WHERE id = 9; <-- blocked | 1 | │ | 2 | │ | 3 | │ | 4 | │ | 5 | │ | 6 | │ | 7 | │ | 8 | │ | 9 | │ | 10 | │ +----+ │ 10 rows in set (0.00 sec) │ │
create table t(id int , key idx_id(id))engine = innodb;
mysql> BEGIN; │mysql> begin; Query OK, 0 rows affected (0.00 sec) │Query OK, 0 rows affected (0.00 sec) │ mysql> select * from t │mysql> insert into t values ( 12 ); -> ; │^C^C -- query aborted +------+ │ERROR 1317 (70100): Query execution was interrupted | id | │mysql> insert into t values ( 6 ); +------+ │^C^C -- query aborted | 1 | │ERROR 1317 (70100): Query execution was interrupted | 3 | │ | 5 | │ | 8 | │ | 11 | │mysql> insert into t values ( 4 ); +------+ │^C^C -- query aborted 5 rows in set (0.00 sec) │ERROR 1317 (70100): Query execution was interrupted │mysql> insert into t values ( 2 ); mysql> select * from t where a between 5 and 11 for update ; │Query OK, 1 row affected (0.00 sec) +------+ │ | a | │mysql> +------+ │ | 5 | │ | 8 | │ | 11 | │ +------+ │ 3 rows in set (0.00 sec) │ │ mysql>
因為目前的 key 有 1, 3, 5, 8, 11, 間隙為 (-inf, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, inf] between 5 and 11 鎖定得範圍為 (3, 5], (5, 8], (8, 11], (11, inf]，所以 insert 4, 6, 12 都被 blocked