1. Gap LockAçıklaması
şöyle. Sanırım sadece Repeatable Read seviyesinde Gap Lock var
A gap lock is a shared lock that locks the gaps between two existing index keys in a table.
If a gap lock is held, no other statement is allowed to INSERT, UPDATE, or DELETE a record that would fall within that gap.
This prevents
phantom reads and ensures that a range of values remains consistent throughout the lifetime of a transaction.
Here are two scenarios where a gap lock is acquired automatically.
- Range query. When a transaction queries a range of records.
- Index query using shared or exclusive lock. InnoDB automatically acquires gap locks on the gap before and after the indexed record. (Also known as next-key lock)
Örnek
# Confirm the database version.
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.30 |
+-----------+
1 row in set (0.00 sec)
# Confirm the database isolation level.
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
# Turn off autocommit for MySQL.
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.01 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
Elimizde şöyle bir tablo
olsun. age sütununda index var. Yaşlar 10 ve 20
CREATE TABLE `students` (
`id` int NOT NULL AUTO_INCREMENT,
`age` int DEFAULT 0,
`name` varchar(64) DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1;
# insert data
mysql> insert into students(`age`, `name`) values (10, 'foo'), (20, 'bar');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from students;
+----+------+------+
| id | age | name |
+----+------+------+
| 1 | 10 | foo |
| 2 | 20 | bar |
+----+------+------+
2 rows in set (0.00 sec)
TA (10, +∞) için lock koyar.
TB (-∞, 20) için lock koyar.
# TA
mysql> begin;
mysql> update students set name = 'bar_a' where age = 20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# TB
mysql> begin;
mysql> update students set name = 'foo_b' where age = 10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
TA bloke olur, çünkü TB'nin alanına girmektedir
mysql> insert into students(`age`, `name`) values (15, 'lee');
|
TB de bloke olur ancak bir müddet sonra TA rollback yaptığı için devam
edebilirmysql> insert into students(`age`, `name`) values (30, 'dec');
Query OK, 1 row affected (0.00 sec)
Dead lock loglarına bakmak için şöyle
yaparızshow engine innodb status;
2. Next-key Lock
A next-key lock is a combination of two different locks
- A record lock (shared or exclusive lock)
- A gap lock
When a read statement is executed via a
non-clustered index with a shared or exclusive lock, the database acquires
- A record lock on the record
- Gap locks on the gap before and after the indexed record.
This ensures that no new rows can be inserted before and after the indexed entry, ensuring data consistency.
Açıklaması şöyle
But here’s the intriguing part, the behaviour of a next-key lock varies depending on the type of index being used:
Querying using a non-clustered index
The database acquires a record lock and gap locks on the gaps before and after the index entry.
Querying using a clustered index
The database acquires only a record lock without a gap lock.
Querying using a non-indexed column
The database acquires a record lock and a gap lock on the ENTIRE table.
As a result, querying a record using a non-indexed column prevents other transactions from modifying any records in the table.
Örnek - non-clustered index
Elimizde şöyle bir tablo
olsun. Burada non-clustered index kullanıldığı için bir önceli ve bir sonraki kayıt ta kilitlendi
/* Sample table */
+---------+---------+------+-------------------+
| id | user_id | age | email |
+---------+---------+------+-------------------+
| 1000076 | 101 | 12 | peter@hotmail.com |
| 1000080 | 104 | 17 | dan@hotmail.com |
| 1000079 | 107 | 19 | lim@hotmail.com |
| 1000081 | 109 | 20 | chris@hotmail.com |
+---------+---------+------+-------------------+
/* Session 1 */
mysql> begin;
mysql> select * from user_tab where user_id=107 for update;
+---------+---------+------+-----------------+
| id | user_id | age | email |
+---------+---------+------+-----------------+
| 1000079 | 107 | 19 | lim@hotmail.com |
+---------+---------+------+-----------------+
/* Session 2 */
mysql> begin;
mysql> insert into user_tab (user_id, age, email)
values (106, 10, 'lee@gmail.com');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into user_tab (user_id, age, email)
values (108, 10, 'lee@gmail.com');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into user_tab (user_id, age, email)
values (110, 10, 'lee@gmail.com');
Query OK, 1 row affected (0.00 sec)Açıklaması şöyle
In the example above, the first transaction queried a record using an xLock. The database locks the record and also the gaps from user_id=104 to user_id=109.
Hence, the second transaction was blocked when trying to insert a record within the gap user_id=104-109. When trying to insert a record outside of the gap user_id=110, the statement succeeded.
