MySQL Lock (5) Gap Locks

gap lock은 index record 사이에 lock을 겁니다.

 

위의 그림처럼 index record에 거는 lock아니라 index record간의 gap에 lock을 걸 기 때문에, 새로운 record가 gap에 추가 되는 것을 방지할 수 있습니다.

 

실제로 테스트를 진행하면서 동작을 확인해보겠습니다.

 

먼저, 테스트를 위한 테이블을 생성합니다.

CREATE TABLE users (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (username, created_at) VALUES ('Kenton_Kirlin', '2017-02-16 18:22:10.846'), 
('Andre_Purdy85', '2017-04-02 17:11:21.417'), ('Harley_Lind18', '2017-02-21 11:12:32.574'), 
('Arely_Bogan63', '2016-08-13 01:28:43.085'), ('Aniya_Hackett', '2016-12-07 01:04:39.298'), 
('Travon.Waters', '2017-04-30 13:26:14.496'), ('Kasandra_Homenick', '2016-12-12 06:50:07.996'),
('Tabitha_Schamberger11', '2016-08-20 02:19:45.512'), ('Gus93', '2016-06-24 19:36:30.978'), 
('Presley_McClure', '2016-08-07 16:25:48.561');

 

터미널을 2개 열어서 각 터미널에서 SQL을 입력합니다.

트랜잭션 1	트랜잭션 2	비고
SET @@SESSION.AUTOCOMMIT=0; START TRANSACTION;	SET @@SESSION.AUTOCOMMIT=0; START TRANSACTION;
SELECT @@SESSION.TRANSACTION_ISOLATION;	SELECT @@SESSION.TRANSACTION_ISOLATION;	READ-UNCOMMITTED입니다.
SELECT * FROM users WHERE id BETWEEN 5 and 8 FOR UPDATE;
	DELETE FROM users WHERE id = 7;	blocking. innodb_lock_wait_timeout 기간동안 대기되고나서 timeout 에러.

 

접속해 있는 터미널의 트랜잭션 ID는 아래 쿼리로 확인 할 수 있습니다.

SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();

 

위의 트랜잭션 1과 2의 트랜잭션 ID를 확인하였습니다.

트랜잭션 1의 TRX_ID는 15920이고 

트랜잭션 2의 TRX_ID는 15921입니다.

 

트랜잭션 2에서 DELETE FROM users WHERE id = 7; 쿼리로 blocking이 발생하게 됩니다.

 

이 상황에서 SHOW ENGINE INNODB STATUS; 쿼리로 확인하였습니다.

 

------------
TRANSACTIONS
------------
Trx id counter 15922
Purge done for trx's n:o < 15920 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 329338272614224, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 15921, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1059, OS thread handle 47863275730688, query id 177199 112.171.127.186 admin updating
DELETE FROM users WHERE id = 7
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 107 page no 3 n bits 80 index PRIMARY of table `test_db_210926`.`users` trx id 15921 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 000000003e2b; asc     >+;;
 2: len 7; hex b7000001610158; asc     a X;;
 3: len 17; hex 4b6173616e6472615f486f6d656e69636b; asc Kasandra_Homenick;;
 4: len 4; hex 584e48a0; asc XNH ;;

------------------
---TRANSACTION 15920, ACTIVE 45 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 1058, OS thread handle 47863262103296, query id 177204 112.171.127.186 admin starting

SHOW ENGINE INNODB STATUS; 쿼리로 실제 lock에 의해서 blocking된 트랜잭션의 정보를 확인할 수 있습니다.

 

 

더 자세한 정보를 확인하기 위해서 INFORMATION_SCHEMA에서 lock 관련 정보를 조회했습니다.

mysql> SELECT * FROM  INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 15921             | 15921:107:3:8     | 15920           | 15920:107:3:8    |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.19 sec)

mysql> SELECT * FROM  INFORMATION_SCHEMA.INNODB_LOCKS;
+---------------+-------------+-----------+-----------+--------------------------+------------+------------+-----------+----------+-----------+
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table               | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+--------------------------+------------+------------+-----------+----------+-----------+
| 15921:107:3:8 | 15921       | X         | RECORD    | `test_db_210926`.`users` | PRIMARY    |        107 |         3 |        8 | 7         |
| 15920:107:3:8 | 15920       | X         | RECORD    | `test_db_210926`.`users` | PRIMARY    |        107 |         3 |        8 | 7         |
+---------------+-------------+-----------+-----------+--------------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.19 sec)

 

 

트랜잭션 2에서 blocking되었된 트랜잭션 2에서 에러가 발생합니다.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 

에러 발생이후에 다시 INFORMATION_SCHEMA에서 LOCK 관련 정보를 조회하였습니다.

mysql> SELECT * FROM  INFORMATION_SCHEMA.INNODB_LOCK_WAITS ;
Empty set, 1 warning (0.19 sec)

mysql> SELECT * FROM  INFORMATION_SCHEMA.INNODB_LOCKS;
Empty set, 1 warning (0.19 sec)

 

트랜잭션 2에서 에러 발생후, 다시 lock관련 정보를 조회한 이유는 어떤 트랜잭션이 lock을 걸고 있는지 확인하기 위해서였는데, 조회되지 않는 것을 알 수 있습니다. 그리고 트랜잭션 2의 blocking상태에서 조회한 결과에 lock_type이 RECORD로 잡혀있습니다. RECORD로 잡힌 것은 아래 특성 때문입니다.

 

gap lock은 unique index를 가지고 unique row를 검색하는 경우에는 잡히지 않는다(하지만, 다중 컬럼으로 unique 제약 속성을 잡아 놓고, WHERE 조건을 일부 컬럼으로만 설정한 경우에는 gap locking이 발생합니다.)

 

 

gap lock은 다른 트랜잭션이 gap(간격)에 INSERT하는 것을 금지하는 용도로 사용됩니다. gap lock은 다른 트랜잭션에서 범위가 중복되는 gap(간격)에 대해서 gap lock을 잡아도 에러가 발생하지 않습니다.  또한, gap lock은 share mode와 exclusive mode에서 아무런 차이가 없고, 같은 동작을 합니다. 

 

참조문서 - https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

 

---

READ COMMITED 인 경우의 제약사항

 

transaction isolation level 설정은 트랜잭션의 처리에 영향을 줍니다.

 

transaction isolation이 READ COMMITED인 경우에는 gap lock이 일부 비활성화 됩니다.

이 경우 검색 및 인덱스 스캔에 대해 간격 잠금이 비활성화되고 외래 키 제약 조건 검사 및 중복 키 검사에만 사용됩니다.

 

semi-consisiten read

그 밖에 record lock관련해서 일치하지 않는 행에 대한 record lock은 MySQL이 WHERE 조건을 평가한 후에 해제됩니다. UPDATE구문관련해서 semi-consistent read가 됩니다. MySQL이 UPDATE의 WHERE 조건과 일치하는지 여부를 MySQL이 결정할 수 있도록 MySQL에 최신 커밋된 버전을 반환합니다.