끄적끄적

1. 서론

 MySQL, MariaDB 의 default Isolation level 은 REPEATABLE READ 이다. 그런데, RDB 하면 떠오를 만한 대표적인 DB 인 Oracle, Postgresql 은 READ COMMITTED 수준의 default Isolation level 를 갖고 있다. 문득 Isolation level에 따른 차이가 무엇이 있을지 궁금하게 되었고 이를 바탕으로 각 Isolation level에 대해 정리해놔야 겠다는 생각이 들어서 본 글을 끄적이게 되었다. 참고로 본 글에서 테스트 시 사용한 RDB는MariaDB 이며, engine 은 row level lock 을 지원해주는 InnoDB 를 사용했다.  

2. 본론

- 테스트 시 사용한 TABLE 

2-(1) READ UNCOMMITTED

  - 설명

 A User 가 transaction 을 시작하고 특정 값을 update 했으며 아직 commit 을 하지 않았다고 생각해보자(②). READ UNCOMMITTED 의 경우 B User 가 해당 값을 select 했을 경우 A user 가 commit 을 하지 않았음에도 B user 의 select 시에는 A User 가 update 한 내용이 보인다.(③) 이를 통상적으로 dirty read 현상이 일어 났다고 일컫는다. 뿐만 아니라, READ UNCOMMITTED 는 Non-repeatable read 와 Phantom read 현상도 발생한다. 이 양자의 현상에 대한 설명은 글의 마지막에 기술하겠다. 

- 잘 사용하지 않는 이유 

 dirty read 현상은 B user 에게 확정되지 않은 data 를 제공해줌으로 인해 B user 가 현재 본인이 조회한 data 로 특정 판단을 내린 경우, 해당 판단의 전제가 되는 data 가 변동 될 수 있다는 점에서 큰 단점이 있다. 

- 테스트 내용 

  ① Isolation level 설정

  ② (A user 화면) A user 가 transaction 을 시작하고, 특정 data 를 update 함. 아직 commit 을 하지 않음.

③ (B user 화면) A User 가 commit 을 하지 않았음에도 B User 에게는 update 된 값이 보인다.

트랜젝션은 하나의 논리 로직을 구성한 단위를 의미한다. 때문에, 하나의 트랜잭션의 과정 자체를 다른 User 에게 노출시키고 이를 바탕으로 다른 User 에게 정확하지 않을 수도 있는 Data 를 제공하는 것은 트랜젝션의 취지에 맞지 않는다고 생각한다. 필자는 개인적으로 이와 같은 이유로 인해 본 level 의 격리 수준을 사용하지 않는다. 

2-(2) READ COMMITTED

 - 설명 

2-(1) 의 경우 A user 가 commit 을 하지 않았음에도 B user 에게 A user 가 수정한 data 가 노출되었다. READ COMMITTED 는 A user 의  트랜잭션에서 COMMIT 된 데이터만 B user 가 읽어올 수 있는 level 이다. REPEATABLE READ 과 더불어 가장 보편적으로 사용되는 격리 수준이다. 이 경우에 Dirty read 는 발생하지 않지만 Non-repeatable read 와 Phantom read 현상은 지속적으로 발생한다. 

- 사용처

 Oracle 과 PostgreSQL 의 경우 본 level 을 default 격리 수준으로 사용하고 있다. 

- 테스트 

  ① Isolation level 설정

  ② (A user 화면) A user 가 transaction 을 시작하고, 특정 data 를 update 함. 아직 commit 을 하지 않음.

③ (B user 화면) A User 가 commit 을 하지 않았기 때문에 B User 에게는  최초의 값이 보인다. 즉, commit 된 값만 B user 에게 노출된다. 만약, ② 에서 A user 가 commit 을 진행했다면, 본 화면에서 amount 는 4000 으로 보일 것이다. 

2-(3) REPEATABLE READ

 - 설명 

 위에서 격리 수준들에서 생기던 문제인 dirty read, Non-repeatable read, Phantom read현상이 모두 일어나지 않는다. 다시 말해, 하나의 트랜젝션에서 항상 동일한 select에 대한 동일한 결과를 보장하는 것을 의미한다.

 - 사용처

 MySQL 과 MariaDB 의 경우 본 level 을 default 격리 수준으로 사용하고 있다.

 - 테스트

  ① Isolation level 설정

  ② (A user 화면) A user 가 transaction 을 시작하고, 특정 data 를 insert 하고 commit 까지 진행

 ③ (B user 화면)  ② 의 현상이 일어나기 전에 transaction 을 시작하고,  ②의 현상이 일어난 후에 select 문을 실행. ②에서 진행한 insert문에 대한 결과가 반영되지 않았음을 알 수 있다. 이를 통해 하나의 transaction 에서는 항상 같은 select에 대해 같은 결과의 값이 나오는 것을 알 수 있다.

2-(4) SERIALIZE

 - 설명  

shared lock 을 통해 select 문에 조회 되는 row 들에 대해 lock 을 설정하는 격리 수준이다. 

- 잘 사용하지 않는 이유 

select 에 걸린 모든 row 에 shared lock 을 걸어버린다면 사용자가 많아지는 경우, 동시 작업에 대해 지속적인 lock 현상으로 인해 부하가 발생할 수 있다. 

- 테스트

  ① Isolation level 설정

  ② (A user 화면) A user 가 transaction 을 시작하고, select 문을 실행. SERIALIZE level 에서는 단순 select 문을 실행해도 모든 select 문에 자동적으로 sql 문 마지막에 "...for share" 이 붙는다고 생각하면 된다. 

③ (B user 화면) ② 에서 A user 가 select 문을 shared lock 을 통해 실행했기 때문에, 특정 data 를 update 하려고 할 경우 ② 의 lock 이 풀리기 전까지는 불가능하다. 

3. 결론

 위의 내용들을 정리하면서 READ UNCOMMITTED 와 REPEATABLE READ의 격리 수준이 가장 많이 사용되는 이유를 나름 알 수 있었다.그렇다면, 도대체 READ UNCOMMITTED 와 REPEATABLE READ 은 어떻게 A user 와 B user 에게 상황에 맞게 다른 data 들을 보여주고 특정 User 가 해당 트랜잭션을 취소하면 rollback 을 통해 예전 버전의 data 로 돌아갈 수 있을까라는 의문이 든다. 이는 snapshot 과 MVCC 패턴을 이용해서 가능한 것이다. 이에 대해서는 다음 글에서 다루도록 하겠다.

4. 참고

- Non-repeatable read 현상

: 하나의 transaction 내에서 동일한 select 문에 대한 결과가 다른 현상을 말한다.

- Phantom read 현상

:  insert 문으로 인해 Non-repeatable read 현상이 발생한 것을 말한다. 다시 말해, A user 의 update 로 인한 data 변화(commit 까지 진행한 data)가 B user 의 select 문에는 반영되지 않지만, A user 의 insert 로 인한 data 변화는  B user 의 select 문에 반영되는 현상을 의미한다. 

- PostgreSQL 의 default 격리 수준 

https://www.postgresql.org/docs/9.5/transaction-iso.html

- Oracle 의 default 격리 수준 

https://blogs.oracle.com/oraclemagazine/on-transaction-isolation-levels

- Shared lock 이란

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

1. 문제의 발견

  Ubuntu 18.04 LTS 를 기준으로 소스 설치를 하지 않는 이상 대게 apt install mariadb-server 명령어를 통해 MariaDB 를 설치한다. 그 후, mysql_secure_installation 명령어를 통해 최초 기본 설정을 진행하게 되는데 그 설정 가운데 root password 를 설정할 수 있는 step 이 존재한다.  필자는 해당 step 에서 설정한 비밀번호를 바탕으로 MariaDB 접속을 시도했는데, 비밀번호가 없이도 접속이 가능했다. 즉, password 설정이 제대로 안된 것이다.

2. 원인

 그 이유는 다음과 같다. MariaDB 10.4.3 버전 이상부터는 local Unix socket file 을 통해 MariaDB 를 접근하려고 하는 것이 default 로 설정이되어 있는데, 이 때는 운영 체제 자체의 "증명" 을 따른다는 것이다. 쉽게 이야기 하자면, OS 자체의 비밀번호 설정을 따른다는 것인데 이렇게 되면 최초 기본 설정 시에 (mysql_secure_installation) 설정했던, MariaDB root password 설정이 제대로 작동하지 않는다. 

3. 해결방안 

 해당 부분을 disable 시키고 MariaDB 자체에 설정해놓은 root password 를 실제 MariaDB 접속 시에 사용하고자 한다면, MariaDB 의 mysql database > user table 에서 다음과 같은 명령어를 사용하면 된다. [ UPDATE user SET plugin=""; ] 
그 후에, MariaDB root password를 재설정한다면 재설정한 password를 바탕으로 접속이 가능할 것이다. 

4. 참고

https://mariadb.com/kb/en/authentication-plugin-unix-socket/

https://mariadb.com/kb/en/after-reset-root-password-i-still-get-access-denied-for-user-rootlocalhost/