MySQL lock (4) Record Lock

 

record lock은 index record에 대한 lock입니다.

 

실제로 테스트 데이터로 테스트를 진행해보겠습니다.

 

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

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

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');

 

이 테이블에서 아래와 같이 조희합니다.

START TRANSACTION;

SELECT * FROM users WHERE id = 2 FOR UPDATE;

 

record lock은 항상 index record에 대해서 lock을 겁니다.

 

그런데, index가 없이 생성되는 테이블도 있지 않나요?

맞습니다. 그런데 MySQL은 사용자가 index없이 테이블을 생성하는 경우에는 몰래 clustered index라고 하는 것을 만들어 놓습니다. 그리고 record lock시에는 이 clustered index를 사용하게 됩니다.

 

트랜잭션 1	트랜잭션 2	비고
SET @@SESSION.AUTOCOMMIT=0 START TRANSACTION;	SET @@SESSION.AUTOCOMMIT=0 START TRANSACTION;
SELECT * FROM users WHERE id = 2 FOR UPDATE;
	SELECT * FROM users WHERE id = 2;	죄회 성공
	DELETE FROM users WHERE id = 2;	blocking.

 

 

아래 명령으로 InnoDB의 상태 정보를 출력한다.

SHOW ENGINE INNODB STATUS;

 

출력된 정보 중에서 TRANSACTIONS로 검색하면 트랜잭션 정보를 확인할 수 있게 된다.

------------
TRANSACTIONS
------------
Trx id counter 14842
Purge done for trx's n:o < 14841 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 329338272614224, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 14841, ACTIVE 28 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 376, OS thread handle 47863275464448, query id 71373 112.171.127.186 admin updating
DELETE FROM users WHERE id = 2
------- TRX HAS BEEN WAITING 28 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 103 page no 3 n bits 72 index PRIMARY of table `test_db_210926`.`users` trx id 14841 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000037e9; asc     7 ;;
 2: len 7; hex a4000002c8011c; asc        ;;
 3: len 13; hex 416e6472655f50757264793835; asc Andre_Purdy85;;
 4: len 4; hex 58e130b9; asc X 0 ;;

 

 

출력된 내용에서 트랜잭션 번호가 있는데, 아래는 트랜잭션 2의 트랜잭션 번호를 확인한 내용이다.

mysql> SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
+--------+
| TRX_ID |
+--------+
| 14841  |
+--------+
1 row in set (0.20 sec)

 

 

여기서 확인한 트랜잭션 ID보 출력내용에서 확인할 수 있습니다.

RECORD LOCKS space id 103 page no 3 n bits 72 index PRIMARY of table `test_db_210926`.`users` 
trx id 14841 lock_mode X locks rec but not gap waiting

 

아쉽게도 트랜잭션 1 의 트랜잭션 ID는 SHOW ENGINE INNODB STATUS; 쿼리에서는 출력되지 않았습니다.

 

 

위의 예는 X lock에 대한 테스트였는데요. S lock의 경우도 같은 결과를 확인할 수 있었습니다.