[데이터베이스] MySQL의 Lock과 트랜잭션 모델

안녕하세요. 이전 포스팅, [DataBase] Lock에 대해서 알아보자 - 기본편에서 우리는 Lock이란 어떤 것이고 DBMS에서는 Lock을 어떤 경위로 사용는지에 대해서 자세하게 알아보았습니다. 도움이 되셨을까요? 포스팅에서도 말했다시피 Lock은 DBMS마다 조금씩 구현하는 요소들이 다릅니다. 대표적인 예로 MySQL은 읽는것에 대해서도 Lock(공유 락)을 잡는 반면 Oracle에서는 Lock을 전혀 잡지 않습니다.

오늘은 Mysql의 Lock과 트랜잭션에 대해서 공식문서를 보며 한번 파헤쳐보도록 하겠습니다.

이번 포스팅은 Lock에 대해서 기본적인 지식이 있다라는 것을 전제로 만들어 졌습니다. 혹시 개념이 없으시거나 미흡하다고 생각되시는 분들은 Lock에 대한 포스팅및 트랜잭션 및 격리성에 대한 포스팅 읽고 해당 포스팅을 이어서 읽어주시기 바랍니다.

MySQL(InnoDB)

간단하게 MySQL이란 어떤것인지 한번 살펴보고 가도록 하겠습니다.

MySQL은 관계형 데이터베이스의 데이터 관리 시스템(database management system, 이하 DBMS)입니다. 이 DBMS는 Oracle(회사)에서 제공하는 오픈소스입니다. 또한 MySQL, MariaDB는 엔진으로 InnoDB를 선택하고 있어 InnoDB라고도 불립니다.

MySQL의 Lock과 트랜잭션

Lock과 트랜잭션 모델은 엔진인 InnoDB에 종속적입니다. 따라서 MariaDB 또한 해당 매커니즘을 따른다고 봐주시면 될 것 같습니다.

MySQL의 Lock

MySQL은 Lock 레벨로는 행(row) 단위 Lock을 기본으로 사용하며 Lock의 종류로는 공유(Shared) Lock과 배타(Exclusive) Lock을 사용합니다.

레코드 락(Record Lock)

Record Lock이란 row의 index를 기준으로 Lock을 거는 것을 말합니다. 아래와 같은 쿼리에 해당 Lock이 걸립니다. 이 쿼리는 c1을 가져옵니다. 일반적으로는 공유락으로 가져오지만 이 경우 레코드락으로 가져오게 됩니다. 레코드 락으로 가져오게되면 다른 트랜잭션에서 해당 index의 row에 insert, update, delete 구문 사용이 락이 걸려있는 동안은 불가능해집니다.

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

레코드 락은 index를 기준으로 lock을 건다고 말씀드렸습니다. 그럼 index를 설정하지 않으면 레코드 락은 돌아가지 않을까요? 아닙니다. 사실 우리가 index를 명시적으로 걸지않아도 MySQL(InnoDB)은 숨겨진 clustered index를 건다고합니다. 따라서 아무런 index를 걸지 않으셨다면 숨겨진 index를 통해 Lock이 걸릴 것입니다.

MySQL의 트랜잭션 격리성(Transaction Isolation Level)

그렇다면 이번에는 MySQL의 트랜잭션 격리성에 대해서 알아보도록 하겠습니다. 격리성이란 실행중인 트랜잭션의 중간결과를 다른 트랜잭션에서는 접근할 수 없음을 나타냅니다. MySQL에서는 격리성에 4개의 레벨을 부여했습니다. 이러한 각 레벨은 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, 그리고 SERIALIZABLE입니다. 이 4단계는 일반적인 DBMS와 동일한 표준을 따르고 있습니다. 또한 MySQL은 이 중 REPEATABLE READ를 기본으로 채택하고있습니다. 해당 단계는 Commit 된 것을 이전에 시작한 트랜잭션이 읽을 때 변경된 값을 읽는 것이 아니라 UNDO 로그에 있는 값을 읽는것을 기본으로하는 레벨입니다. 이렇게 하여 한 트랜잭션에 대해서 동일한 읽기를 가능하게 해줍니다.

여기에 Lock 매커니즘이 적용되게됩니다. 한 트랜잭션에서 5개의 row를 update한다고 하겠습니다. REPEATABLE READ 레벨에서는 각 row에 대해서 update시 배타락을 겁니다. 하지만 업데이트가 되었다고 해서 lock을 풀지 않습니다. 이 lock은 트랜잭션이 마무리될때까지 가지고 있는 것입니다.

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

x-lock(1,2); block and wait for first UPDATE to commit or roll back

한단계 낮은 READ COMMITTED 레벨에서는 어떨까요? READ COMMITED는 커밋된다면 다른 트랜잭션에서 업데이트된 상태값을 읽을 수 있는 레벨입니다. 이때는 update 되고 다음 row로 넘어갈 때 update된 row의 lock을 풀어줍니다.

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

MySQL의 데드락(Dead Lock) 처리

MySQL에서 사용하는 InnoDB는 데드락 감지를 사용가능합니다. 이 설정은 디폴트로 설정되어있습니다. MySQL의 데드락 감지 기능에 의해서 데드락으로 감지되면 2개의 트랜잭션 중 데이터 변화가 적은 트랜잭션을 rollback 시켜 데드락을 해소시킵니다. 하지만 높은 동시성을 사용하는 고성능 시스템에서는 데드락 감지기능은 속도 저하의 원인이 될 수 있습니다. 이럴 경우는 데드락 감지기능을 사용하기 보다는 innodb_lock_wait_timeout을 사용하는 것을 권장하고 있습니다. innodb_lock_wait_timeout은 row lock을 걸고 특정시간이상 걸리면 자동으로 lock을 해재해버리는 매커니즘입니다.

마무리

이번 포스팅은 쓰다보니 좀 딱딱한 내용이 주를 이루었던 것 같습니다.

저도 많이 미흡한 부분이어서 지적 및 새로운 것을 알려주시는 것은 환영입니다.

다음번에는 또 다른 주제로 찾아뵙도록 하겠습니다.

감사합니다.

참조

wikipedia_innoDB

DBGuide_net+Lock

innodb-locking

mysql_deadlock_handling