문장수준 쓰기 일관성

다른 트랜잭션에 의한 동시추가, 변경, 삭제가 발생하는 상황에서 단일 update 또는 delete 문을 일관성 있게 처리하는 것

 

• 보통 pk로 접근하는 단순 트랜잭션은 기본 격리수준에서도 완벽한 일관성 보장 가능

 

 

읽기 모드에 따른 갱신 연산 차이

상황

1. 통안에 공이 4개 있는데 노란색 2개, 빨간색 2개 존재
2. 간발의 차로 거의 동시에 update 문이 진행됨
   1. 빨간색 공을 노란색으로 업데이트
   2. 노란색 공을 빨간색으로 업데이트

• current 모드( SQL server, MySQL, DB2 ..)
  • 업데이트시 lock 기반으로 동작하므로 한쪽이 blocking 되서 기다림
  • 전부 노란색으로 바뀐 공을 전부 빨간색으로 바꾸게 되어 빨간색 공 4개가 나옴
• consistent 모드( postgre, oracle)
  • 갱신대상이 다르므로 blocking 없음

읽기 모드가 달라지면 결과도 달라질 수 있다

 

 

 

Lock 기반, mvcc  동시성 제어별 모드 차이

---

 

	Lock 기반 동시성 제어(SQL server, MySQL)	MVCC 동시성 제어
select	current 모드 사용	consistent 모드 사용
update, delete	식별 및 갱신 모두 current 모드 사용	1. 대상을 식별할 때는 Lock을 설정하지 않고 consistent 모드로 대상을 식별 2. 갱신할 때는 Lock 설정 후 current 모드로 대상여부 확인 후 갱신 3. 일관성 기준 시점이 Update 시작 시점.

 

Q 왜 MVCC는 update등을 할때 갱신시에는 current 모드를 사용할까?

• 만약 수정시에도 consistent 모드를 사용하면 cr 블록에 있는 값을 읽어서 그값 기준으로 update를 하게 되므로, 그전에 이미 업데이트 된 내역이 있다면 덮어 씌워지는 lost update가 발생가능함. 그래서 갱신시에는 current 모드로 진행.

 

current 모드와 consistent 모드만 사용시의 이상현상들 추가 정리

 

 

 

---

Oracle의 Restart 메커니즘

 

 

1. Consistent 모드로 갱신 대상을 확정.(일관성 기준을 DML 시작시점으로 고정.)
2. current 모드로 갱신대상의 변경여부를 확인한 뒤 갱신한다.
3. 조건절 변경 여부에 따라서 다르게 동작
   1. 조건절의 대상이 되는 값이 변경되었을 경우
      1. update를 전부 rollback 진행
      2. select for update로 xlock를 검
      3. update 재시작
   2. 조건절 이외의 대상 값이 변경된 경우
      1. update 그대로 성공

 

 

PostgreSQL과의 메커니즘 비교

---

• PostgreSQL의 update시 갱신 메커니즘
  • postgreSQL은 조회시작 시점에 갱신대상이 current 모드로 조회시 갱신대상이 아니게 되면 그대로 제외처리
  • 신규 delete가 들어와도 최초 대상이 아니거나 current 조회시점 값이 아니면 제외처리
  • Read Committed에서 문장 수준 쓰기 비일관성을 가지고 있음
    • 문장수준 쓰기 일관성 확보를 위해 격리수준을 Repeatable read로 설정하는 방법 존재
    • 설정시 could not serialize access due to concurrent update 에러 발생하므로 트랜잭션을 중단해서 일관성을 보장하는 방식.

 

Oracle과 PostgreSQL이 동일 메커니즘인 경우

1. 기존에 조건절을 만족하던 레코드가 조건절을 만족하지 않도록 변경되었을 경우
   • oracle
     • update rollback후 restart를 진행.
     • 락을 걸고 재 update시 current 모드 기준 조건절을 만족하지 않으므로 제외
   • PostgreSQL
     • 갱신시 current 모드로 조회했을때 조건절을 만족하지 않을 것이므로 그냥 제외
   • 예시

     • 시간	트랜잭션 A (Updater)	트랜잭션 B (Changer)
       T1	id가 1이고 BALANCE가 100인 대상에 대해 BALANCE +10이 되도록 업데이트
       T2		id가 1인 행의 BALANCE 50으로 업데이트
       T3		COMMIT (ID=1의 잔액이 50으로 확정)
       T4	(A가 ID=1 레코드에 도달하여 갱신 시도)

     • 둘 다 BALANCE는 50으로 최초 update(+10 처리)는 제외
2. 조건절을 만족하지 않던 레코드가 조건절을 만족하게 된 경우
   • oracle
     • rollback & restart
     • restart 기준 current 조회시 조건절을 만족하므로 update 진행
   • PostgreSQL
     • current 모드 조회시점 조건절을 만족하더라도, 조회시점 갱신대상이 아니므로 누락처리
   • 예시

     • 조건: UPDATE emp SET sal=sal+100 WHERE sal=1000
       ------------------------------------------------------
       시점          | Oracle (Restart=Current) | PostgreSQL (MVCC Snapshot)
       ------------------------------------------------------
       실행 시작시   | sal=900 (불일치)          | sal=900 (불일치)
       중간 변경     | sal=1000                 | sal=1000
       재시작/재평가 | current sal=1000 (일치) → Update | snapshot sal=900 (불일치) → Skip
       ------------------------------------------------------
       최종 결과     | sal=1100                  | sal=1000 (변경 없음)

     • oracle은 restart시점으로 조회해서 update 성공하나, postgre는 최초 대상이 아니었으므로 스킵
3. 조건절을 만족하는 신규 레코드가 입력된 경우
   • oracle
     •  

---

격리수준 별 문장수준 쓰기 일관성

격리 수준	Lock 기반 동시성 제어	MVCC 동시성 제어
Read Committed	1. current 모드로 읽고 갱신 2. 다른 트랜잭션의 동시 update의 경우, Update 완료시점 기준으로 쓰기 일관성을 제공. 락을 잡기 때문에 순차적으로 update 되므로. 3. 동시 insert에 대한 쓰기 비일관성	1. consistent 모드로 갱신대상 식별, current 모드로 변경여부 확인후 갱신 2. insert에 대해서는 무시 3. 동시 update에 대해서는 oracle은 쓰기 일관성을 제공하나, postgre는 쓰기 일관성 보장 x
Repeatable Read	insert 비일관성 허용	1. 트랜잭션 시작시점의 일관성을 가짐 2. 다른 트랜잭션이 변경한 데이터 발견시, 쓰기 일관성을 보장할 수 없는 상황으로 간주해 Update 충돌 에러. 3. PostgreSQL은 Repeatable Read에서 동일 에러 발생함. 그래서 Repeatable Read 시에도 문장수준 쓰기 일관성 보장 가능.
Serializable	1. Predicate Lock을 활용해서 이미 변경한 영역에 변경/삽입 모두 차단. 그래서 update 완료 시점 일관성을 가짐

 

 

요약 버전

 

격리 수준	Lock 기반 동시성 제어	MVCC 동시성 제어
Read Committed	insert에 의한 비일관성	1.oracle은 update 시작 시점의 일관성 (일관성 확보가 어려우면 재시작) 2. postgreSQL은 update에 의한 비일관성 발생
Repeatable Read	insert 비일관성 허용 (sql server는 여전히 비일관성을 가지나, MySQL은 predicate lock을 사용해서 변경된 영역의 insert를 막음. 그래서 일관성을 가진다. sql server만 해당)	트랜잭션 시작시점의 일관성 (동시 update 에러발생)
Serializable	update 완료 시점 일관성을 가짐

 

• Read Committed는 트랜잭션간 진행순서를 직렬화하지 않으므로 모두 직렬화 이상 발생 가능
• Lock기반에서 Update와 Delete는 기본적으로 exclusive lock을 트랜잭션 종료시까지 유지
• 오라클은 명시적으로 committed read랑 serializable만 지원.

 

 

 

 

 

todo 질문

1. update문에서 갱신시에도 consistent 모드를 사용하면 cr 블록에 있는 값을 읽어서 그값 기준으로 current 블록을  update를 하게 되는게 맞는지? 그렇다면 lost update가 되는걸 이해할 수 있음.

2. mvcc oracle, postgreSQL의 update 진행시 조건절을 만족하는 신규 레코드가 입력된 경우에 어떻게 결과가 달라지는지 잘 모르겠다. 둘다 최초대상이 아니라서 무시하고 넘어가니 결과가 같을 거 같은데 왜다르지?

3. Read Committed 수준에서 Lock기반은 다른 트랜잭션의 동시 insert에 대해서는 비일관성을 허용한다고 하는데, 이 동시 insert가 어떤 상황인지 알고 싶음. 이미 지나간 영역에 입력된 데이터는 무시하고 아직 처리하지 않은 영역에 입력된 데이터만 overwrite하는게 왜 비일관성인지 잘 모르겠음

-> 전체영역중 어디에 insert가 되는가에 따라서 결과가 다 달라진다. 읽지 않은 영역에 넣으면 업데이트 한 대상개수에 추가가 안되고, 읽지 않은 영역에 넣었을때는 대상에 추가가 된다. repeatable read까지는 전체영역에 insert가 되므로 쭉 insert에 대해서 비일관성을 제공하는것. 다만 mysql은 Predicate Lock을 활용해서 변경된 영역에 삽입/변경을 둘다 차단하기 때문에 update 기준의 일관성을 제공할 수 있는 것. 이 얘기는 SQL server는 repeatable read에서 비일관성을 갖지만, mySQL은 일관성을 갖는다는 얘기가 맞을듯? 확인 필요

어쨌든 변경된 영역에 추가되었으니, PR랑 어떤건 업데이트되고 신규추가한건 업데이트 안된것에 대한 비일관성 얘기도 맞는지 궁금하다. 동시 insert일때 비일관성이 repeatable 기준으로는 PR을 말하는게 맞는지

 

4. update 완료 시점 일관성을 가진다는 표현이 좀 모호함 어떤 의미인지 바로 와닿지는 않음

5. MySQL의 기본 격리수준은 Repeatable Read인데, 이미 변경한 영역의 변경 및 삽입을 차단하므로  SQL server의 Serializable 수준의 격리성 제공 -> 왜지? 왜 이미변경한 영역의 삽입까지 차단하는거지? locking reads 아닌가? 그러면 삽입은 허용하는거 아닌가?

-> 그 이유는 predicate lock으로 변경된 영역의 삽입까지 차단하기 때문. 얘는 그래서 phantom 까지도 막을 수 있다.

6. Read Committed에서 lock 기반이 어떻게 변경완료시점 일관성을 제공할 수 있는지?

-> lock 기반은 update문과 delete문 사용할때 기본적으로 xlock을 트랜잭션 종료시까지 유지한다. 그래서 Read Committed에서도 repeatable read 수준의 격리성을 제공할 수 있고, 그러니 update 완료시점 일관성을 제공해줄 수 있는 것.

 

7. 조건절을 만족하는 신규 레코드가 입력한 경우에 왜 oracle과 postgre동작이 차이가 있는가?

둘다 갱신대상 조회 이후에 들어온 애들이라서 갱신대상에서 제외하면 같은 동작 아닌가?