- mysql lock
- lock mode
- share lock , 略S, for read
- exclusive lock, 略X,for update or delete。even read have to wait the lock.
- lock granularity(粒度)
- table lock
- row lock(record lock)
- A record lock is a lock on an index record.
- if no index, innodb will create a hidden cluster index to lock.
- show engine innodb status;
```
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
```
- gap lock(range lock)
- lock a gap between index records
- for example, select ... where id between 1 and 10 for update.
- Gap locking is not needed for statements that lock rows using a unique index to search for a unique row
- only purpose is to prevent other transactions from inserting to the gap (Gap内にInsertできない)
- can co-exist. does not prevent another transaction from taking a gap lock on the same gap
- 最初のインデックスレコードの前または最後のインデックスレコードの後のギャップのロック
- (Gap内にInsertできない)、Update、Deleteできる！！
- Next Key lock
- a combination of
- a record lock on the index record
- and a gap lock on the gap before the index record.
- If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order
- show engine innodb status
```
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
rx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
```
- Insert Intention Locks
- a type of gap lock set by INSERT operations prior to row insertion
- multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap
- AUTO-INC Locks
- transactions inserting into tables with AUTO_INCREMENT columns
- intention lock, multiple granularity lock.(粒度が多いlock),lock modeとlock targetの組み合わせ
- intention lock is table level lock that show the row lock a transaction will want on a row
- テーブル級のロックであるが、次のTXの行ロックを提示する
- intention share lock,略IS. a tx intents to set a share lock on a row in a table.
- for example. select *** for share
- intention exclusive lock,略IX. a tx intents to set a exclusive lock on a row in a table.
- for example. select *** for update
- Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
- Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.
- Intention locks do not block anything,
- The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.
- ロック発生前に予測してErrorにする、実際のロックを回避する？
- show engine innodb status; TABLE LOCK table `test`.`t` trx id 10080 lock mode IX
example:
```
show engine innodb status;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-01-27 10:24:38 0x409e8ed700
*** (1) TRANSACTION:
TRANSACTION 22982, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 13158, OS thread handle 277537257216, query id 175713 172.18.0.1 root update
insert into
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 70 page no 3 n bits 264 index PRIMARY of table `w`.`t` trx id 22982 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 22981, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
insert into
MySQL thread id 13157, OS thread handle 277538068224, query id 175714 172.18.0.1 root update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 70 page no 3 n bits 264 index PRIMARY of table `w`.`t` trx id 22981 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 70 page no 3 n bits 264 index PRIMARY of table `w`.`t` trx id 22981 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
============================
```
```
In my circumstance, the only index is PRIMARY, which was useless in my SELECT query (can be verified by explain select...).
As a result, all entries in the PRIMARY index were locked.
When TXN_2 waited for an X lock on a certain entry, the entry was locked by an S lock retained by TXN_1.
Similarly, TXN_1 waited for an X lock on another entry but the entry was also locked by S lock retained by itself.
A "one S two X" deadlock occurred.
By contrast, after I created an index name on the column name,
the index name would be used in the SELECT statement (can be verified by explain select ...),
so the locks would be issued on the index name instead of PRIMARY.
More importantly, the SELECT statement would only issue S lock on the entry equals to someValue instead of all entries of index name.
Besides, the IX lock and X lock required by INSERT would be issued on the index PRIMARY.
Conflicts between S lock and IX lock, X lock would be solved.
The index on column name did not only speed up the query but more importantly prevented locking all entries of the index.
```