MySQL lock (2) 개별row에 대한 잠금(row lock)

전 포스트에서 테이블에 대한 잠금 처리에 대해서 살펴 보았습니다. 

이번에는 lock관련해서 가장흔하게 언급되는 레코드에 대한 잠금 처리를 알아보겠습니다.

 

흔히 row lock이라고 하는데, shared mode와 exclusive mode각각 동작을 확인해보겠습니다.

 

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

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

 

 

S lock(Shared lock) 테스트

터미널을 2개 열어서 아래 순서로 쿼리를 실행해서 테스트를 진행합니다.

트랜잭션 1	트랜잭션 2	비고
START TRANSACTION;
	START TRANSACTION;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
UPDATE users set created_at=NOW() WHERE id = 1;		자신에 shared mode로 row lock을 잡은 상태에서 UPDATE 성공.
	UPDATE users set created_at=NOW() WHERE id = 1;	blocking. innodb_lock_wait_timeout 파라미터에 설정된 시간만큼 blocking된 후에 에러 발생.
	SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;	같은 레코드에 대해서 row lock 추가 성공
UPDATE users set created_at=NOW() WHERE id = 1;		blocking.
	UPDATE users set created_at=NOW() WHERE id = 1;	Deadlock 감지되어서 에러 발생함. ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

 

위 테스트 내용 처럼 같은 레코드에 대해서 row lock을 shared mode로 거는 것은 가능하지만, 다른 트랜잭션에서의 업데이트는 blocking됩니다. 또한, 동시에 두개 트랜잭션이 데이터를 업데이트하는 경우에는 Deadlock이 발생합니다.

 

 

 

X lock(Exclusive lock) 테스트

터미널을 2개 열어서 아래 순서로 쿼리를 실행해서 테스트를 진행합니다.

트랜잭션 1	트랜잭션 2	비고
START TRANSACTION;
	START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
	SELECT * FROM users WHERE id = 1;	조회 성공
	UPDATE users set created_at=NOW() WHERE id = 1;	blocking. innodb_lock_wait_timeout 파라미터에 설정된 시간만큼 blocking된 후에 에러 발생.
	SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;	blocking. innodb_lock_wait_timeout 파라미터에 설정된 시간만큼 blocking된 후에 에러 발생.

 

트랜잭션 1에서 레코드에 대해서 row lock을 X mode로 얻는 경우는 트랜잭션 2에서 조회는 성공하지만, 변경은 blocking되는 것을 확인할 수 있었습니다. 또한, shared mode로도 lock을 획득할 수 없습니다.

 

 

S lock과 X lock 의외로 간단합니다. 

table lock과 row lock을 모두 다뤘으니, 다음에는 intention lock에 대해서도 알아보겠습니다.