Understanding MySql Lock

understanding_mysql_lock.sql

-- 테스트 테이블 생성
CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(140)
) ENGINE=INNODB;

SHOW TABLE STATUS WHERE NAME = "messages";




/******************************************************************************/
/* 1. 읽기 잠금의 동작 특성 확인
/*    - 먼저 테이블을 잠근 프로세스가 잠금을 반환하기 전에 다른 프로세스는 잠금을 얻을 수 없다.
/*    - 모든 프로세스에서 읽기 가능, 모든 프로세스에서 쓰기 불가능
/*    - 잠금을 가진 프로세스에서 쓰기하려하면 바로 오류, 다른 프로세스에서는 쓰기 쿼리하면 잠금이 풀릴 때까지 대기
/******************************************************************************/

LOCK TABLES messages READ;

-- mysql2> LOCK TABLES messages WRITE;
-- 뺑글이 (== spinner)

SHOW PROCESSLIST;
-- mysql2 프로세스(세션)의 State: "Waiting for table metadata lock"

UNLOCK TABLES;
-- mysql2 프로세스의 뺑글이 사라지고, 쓰기 잠금 얻어감.

SELECT * FROM messages;
-- mysql2 프로세스가 테이블을 잠갔으므로 뺑글이.

SHOW OPEN TABLES FROM lock_test;
-- In-use: 1

UNLOCK TABLES;
-- mysql2 프로세스가 잠금을 가지고 있으므로 다른 프로세스(세션)에서 잠금 해제 불가

-- mysql2> UNLOCK TABLES;

SELECT * FROM messages;
-- mysql2 프로세스가 잠금을 반환했으므로 쿼리 가능.

LOCK TABLES messages READ;

SELECT * FROM messages;

-- mysql2> SELECT * FROM messages;
-- 읽기 잠금이므로 mysql2 프로세스에서 쿼리 가능

-- mysql2> INSERT INTO messages (message) VALUES ('로렘입숨');
-- 뺑글이. mysql2 프로세스에서 읽기는 가능하지만, INSERT/UPDATE/DELETE는 불가능

INSERT INTO messages (message) VALUES ('로렘입숨2');
-- Table 'messages' was locked with a READ lock and can't be updated

UNLOCK TABLES;
-- mysql2 프로세스의 뺑글이 종료.

SELECT * FROM messages;
-- 쿼리 OK




/******************************************************************************/
/* 2. 쓰기 잠금의 동작 특성 확인
/*    - 잠금을 가진 프로세스에서만 읽기, 쓰기 가능
/*    - 다른 프로세스에서는 잠금이 풀릴 때가지 읽기/쓰기 모두 불가능 (뺑글이)
/******************************************************************************/

LOCK TABLES messages WRITE;

INSERT INTO messages (message) VALUES ('로렘입숨3');

SELECT * FROM messages;

-- mysql2> SELECT * FROM messages;
-- 뺑글이

SHOW PROCESSLIST;
-- mysql2 프로세스(세션)의 State: "Waiting for table metadata lock"

RENAME TABLE messages TO new_messages;
-- Can't execute the given command because you have active locked tables or an active transaction

UNLOCK TABLES;
-- mysql2 프로세스의 뺑글이 종료.




/******************************************************************************/
/* 3. TRANSACTION & ROW-LEVEL LOCK
/*    - 다른 프로세스에서 읽기 가능, 쓰기 불가능 (대기)
/*    - 설정된 시간 만큼 잠금을 기다리다가 잠금을 얻는 것을 포기하고 쿼리 실패
/*    - 같은 행에 대해 여러 개의 프로세스가 쓰기 동작을 시도할 때 데드락 발생
/******************************************************************************/

START TRANSACTION; SELECT * FROM messages WHERE id = 1 LOCK IN SHARE MODE;

-- mysql2> SELECT * FROM messages WHERE id = 1;
-- 쿼리 OK.

-- mysql2> START TRANSACTION; DELETE FROM messages WHERE id = 1; COMMIT;
-- 뺑글이
-- [ERROR in query 2] Lock wait timeout exceeded; try restarting transaction

SHOW VARIABLES WHERE VARIABLE_NAME LIKE "%innodb_lock%";
-- innodb_lock_wait_timeout: 50

-- mysql2 프로세스에서 뺑글이가 돌고 있는 상태에서
DELETE FROM messages WHERE id = 1; COMMIT;
-- No errors; 1 row affected
-- 한편 mysql2 프로세스에서는
-- [ERROR in query 2] Deadlock found when trying to get lock; try restarting transaction

SELECT * FROM messages WHERE id = 1;
-- Record Gone

SHOW ENGINE INNODB STATUS;
/*
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-07-06 12:14:27 0x7f3bfc1ec700
*** (1) TRANSACTION:
TRANSACTION 728677, ACTIVE 22 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 51, OS thread handle 139895609472768, query id 21093 172.17.0.1 root updating
DELETE FROM messages WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4588 page no 3 n bits 72 index PRIMARY of table `lock_test`.`messages` trx id 728677 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000b1e56; asc      V;;
 2: len 7; hex c1000007dd0110; asc        ;;
 3: len 12; hex eba19ceba098ec9e85ec88a8; asc             ;;

*** (2) TRANSACTION:
TRANSACTION 728678, ACTIVE 198 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 49, OS thread handle 139895609673472, query id 21096 172.17.0.1 root updating
DELETE FROM messages WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4588 page no 3 n bits 72 index PRIMARY of table `lock_test`.`messages` trx id 728678 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000b1e56; asc      V;;
 2: len 7; hex c1000007dd0110; asc        ;;
 3: len 12; hex eba19ceba098ec9e85ec88a8; asc             ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4588 page no 3 n bits 72 index PRIMARY of table `lock_test`.`messages` trx id 728678 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000b1e56; asc      V;;
 2: len 7; hex c1000007dd0110; asc        ;;
 3: len 12; hex eba19ceba098ec9e85ec88a8; asc             ;;

*** WE ROLL BACK TRANSACTION (1)
*/




/******************************************************************************/
/* 4. Laravel
/******************************************************************************/

-- https://github.com/appkr/db-lock-poc/blob/master/core/Myshop/Infrastructure/Eloquent/EloquentProductRepository.php#L22-L25