Optimistic Lock vs Pessimistic Lock

DBMS	MySQL
Storage Engine	Inno DB

문제 정의

UPDATE T
   SET value = :value
 WHERE id = 1

위와 같이 T 테이블의 값을 수정하는 쿼리가 있는 경우,
여러 트랜잭션에서 해당 쿼리를 실행한다면 id가 1인 row의 값으로는
가장 마지막으로 commit 실행된 트랜잭션에서 수정한 값이 들어가게 된다.
(아래의 경우, transaction2에서 실행한 update의 경우,
transaction1에서 commit을 수행하기 전까지 block되어 있게 된다. - innoDB row level exclusive lock)

# transaction 1

start transaction;        # 1

update T                  # 2
   set value = 200
 where id = 1;
 
commit;                   # 5

# transaction 2
start transaction;        # 3

update T                  # 4
   set value = 300
 where id = 1;
 
commit;                   # 6

하지만 이렇게 최종 실행된 트랜잭션에 의해서 데이터가 수정되는 것을 방지하기 위해서 아래의 2가지 방법으로 처리하는 방법이 있다.

(쿼리 옆의 # {숫자}는 실행된 순서를 의미한다.)

 

Optimistic Lock

• 트랜잭션 대부분은 충돌이 발생하지 않을 것이라고 가정하여
  application 레벨에서 Lock을 거는 방식

# transaction 1
start transaction;        # 1

update T                  # 2
   set value = 200
     , version = version + 1
 where id = 1
   and version = 1;
 
commit;                   # 5

# transaction 2
start transaction;        # 3

update T                  # 4
   set value = 300
     , version = version + 1
 where id = 1
   and version = 1;
 
commit;                   # 6

해당 순서로 쿼리가 실행되는 경우, transaction 1의 내용만 반영이 되어있고 transaction 2의 내용은 반영되지 않는다.
(transaction 1에서 value를 수정할 때 version을 같이 수정하였기 때문에 transaction2에서는 해당 조건에 일치하는 데이터를 발견할 수 없다.)
(transaction 2의 Update 문 실행시 affected rows는 0이다,
이 점을 이용하여 어플리케이션에서는 수정된 row 수가 0인 경우 다른 트랜잭션에서 해당 데이터를 수정했음을 인지 할 수 있다.)

 

Pessimistic Lock

• 직접 DB의 Lock을 사용하여 데이터의 접근을 제어하는 방식

 

Row Level Locking

• innoDB storage engine에서 가능
• 해당 row에만 lock을 거는 방식(다른 row에는 접근이 자유롭다.)

Lock in share mode

• 다른 트랜잭션에서 잠겨있는 row를 읽어 가는 것을 허용한다.
• 다른 트랜잭션에서 insert, update, delete 실행을 허용하지 않는다.
• 조회 조건에 인덱스가 없는 경우 전체 테이블에 lock이 걸리게 된다.

# transaction 1
start transaction;                  # 1

select *                            # 2
  from T 
 where id = 2 lock in share mode;
 
 commit;                            # 7

# transaction 2
start transaction;                  # 3

select *                            # 4
  from T;

select *                            # 5
  from T
 where id = 2;                      

insert into T                       # 6 (update, delete의 경우에도 동일)
(
    id
  , value
)
values
(
    2
  , 100
);

commit;                             # 8

 

transaction 2의 #4, #5까지는 block되지 않고 잘 실행되지만,
#6을 실행하는 경우, transaction 1이 commit 되기 전까지 block 되게 된다.

 

For update

• 다른 트랜잭션에서 잠겨있는 row를 읽어 가는 것을 허용하지 않는다.
• 다른 트랜잭션에서 insert, update, delete 실행을 허용하지 않는다.
• 조회 조건에 인덱스가 없는 경우 전체 테이블에 lock이 걸리게 된다.

# transaction 1
start transaction;                  # 1

select *                            # 2
  from T 
 where id = 2 for update;
 
 commit;                            # 7

# transaction 2
start transaction;                  # 3

select *                            # 4
  from T;

select *                            # 5
  from T
 where id = 2;                      

insert into T                       # 6 (update, delete의 경우에도 동일)
(
    id
  , value
)
values
(
    2
  , 100
);

commit;                             # 8

transaction 2의 #4를 실행하는 경우, transaction 1이 commit 될 때까지 block 되게 된다.