MySQL InnoDB Lock

詳細說明
詳細說明

InnoDB在transaction中，會視語法不同，自動加鎖。
Transaction中的鎖，都是以Row為標的。

非transaction中，因為會自動COMMIT，所以使用加鎖的語法也沒用。

InnoDB三種加鎖類型

• Record lock: This is a lock on an index record.
• Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
• Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.
• 官網說明：http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html
• 重點1：Next-key locking combines index-row locking with gap locking.
InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters (select 過程搜尋遇到的資料列都會被加鎖)

InnoDB鎖定模式

• 共享鎖定 (shared lock) (S lock)：permits the transaction that holds the lock to read a row.
• 排他鎖定 (exclusive lock) (X lock)：permits the transaction that holds the lock to update or delete a row.

不同語句的加鎖效果

• SELECT ... FROM 基本不加鎖
  但在 SERIALIZABLE 隔離層級時，會自動在遇到的資料加鎖
• SELECT ... FROM ... LOCK IN SHARE MODE (加共享鎖)
• SELECT ... FROM ... FOR UPDATE (加排它鎖)
• UPDATE ... WHERE ... (加排它鎖)
• DELETE FROM ... WHERE ... (加排它鎖)
• INSERT (加排它鎖)