Oracle 및 Postgre의 쓰기 Skew 변칙SQL이 트랜잭션을 롤백하지 않음

Oracle 및 Postgre의 쓰기 Skew 변칙SQL이 트랜잭션을 롤백하지 않음

Oracle과 Postgre 모두에서 다음과 같은 현상이 발생하는 것을 확인했습니다.SQL 입니다.

다음과 같은 데이터베이스 스키마가 있음을 고려하면 다음과 같습니다.

create table post (
    id int8 not null, 
    title varchar(255), 
    version int4 not null, 
    primary key (id));    

create table post_comment (
    id int8 not null, 
    review varchar(255), 
    version int4 not null, 
    post_id int8, 
    primary key (id));

alter table post_comment 
    add constraint FKna4y825fdc5hw8aow65ijexm0 
    foreign key (post_id) references post;  

다음 데이터를 사용합니다.

insert into post (title, version, id) values ('Transactions', 0, 1);
insert into post_comment (post_id, review, version, id) 
    values (1, 'Post comment 1', 459, 0);
insert into post_comment (post_id, review, version, id) 
    values (1, 'Post comment 2', 537, 1);
insert into post_comment (post_id, review, version, id) 
    values (1, 'Post comment 3', 689, 2); 

두 개의 별도 SQL 콘솔을 열고 다음 문을 실행하는 경우:

TX1: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

TX2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

TX1: SELECT COUNT(*) FROM post_comment where post_id = 1;

TX1: > 3

TX1: UPDATE post_comment SET version = 100 WHERE post_id = 1;

TX2: INSERT INTO post_comment (post_id, review, version, id) VALUES (1, 'Phantom', 0, 1000);

TX2: COMMIT;

TX1: SELECT COUNT(*) FROM post_comment where post_id = 1;

TX1: > 3

TX1: COMMIT;

TX3: SELECT * from post_comment;

     > 0;"Post comment 0";100;1
       1;"Post comment 1";100;1
       2;"Post comment 2";100;1
       1000;"Phantom";0;1

SERIALIZABLE분리 수준은 스냅샷 데이터를 TX1 트랜잭션의 시작부터 유지하고 TX1에는 3만 표시됩니다.post_comment기록.

Oracle 및 Postgre의 MVCC 모델 때문에SQL, TX2는 새 레코드를 삽입하고 커밋할 수 있습니다.

TX1에서 커밋이 허용되는 이유는 무엇입니까?이는 Write Skew 변칙이므로 TX1이 "직렬화 실패 예외" 등과 함께 롤백될 것으로 예상했습니다.

MVCC Serializable 모델이 Postgre에서 실행됩니까?SQL 및 Oracle은 스냅샷 격리만 보장하고 Write Skew 이상 감지는 제공하지 않습니까?

갱신하다

심지어 Tx1을 변경하여 UPDATE 문을 발행하기도 했습니다.version모두를 위한post_comment동일한 것에 속하는 기록.post.

이런 방식으로 Tx2는 새 레코드를 생성하고 Tx1은 UPDATE 필터링 기준을 충족하는 새 레코드가 추가된 것을 모른 채 커밋합니다.

사실 포스트그레에서 실패할 수 있는 유일한 방법은SQL은 팬텀 레코드를 삽입하기 전에 Tx2에서 다음 COUNT 쿼리를 실행하는 경우입니다.

Tx2: SELECT COUNT(*) FROM post_comment where post_id = 1 and version = 0

TX2: INSERT INTO post_comment (post_id, review, version, id) VALUES (1, 'Phantom', 0, 1000);

TX2: COMMIT;

그러면 Tx1은 다음과 함께 롤백됩니다.

org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
  Detail: Reason code: Canceled on identification as a pivot, during conflict out checking.
  Hint: The transaction might succeed if retried.

아마도 쓰기 스큐 이상 방지 메커니즘이 이 변경 사항을 감지하고 트랜잭션을 롤백했을 가능성이 높습니다.

흥미로운 점은 Oracle이 이 문제에 대해 신경쓰지 않는 것 같고 그래서 Tx1이 성공적으로 커밋한다는 것입니다.Oracle은 쓰기 스큐가 발생하는 것을 방지하지 않으므로 Tx1은 정상적으로 커밋합니다.

그런데 이 예시들은 GitHub에 있기 때문에 직접 실행할 수 있습니다.

1995년 ANSI SQL 격리 수준에 대한 비평, Jim Gray and co는 팬텀 리드를 다음과 같이 설명했습니다.

P3: r1[P]...w2[y in P]...(c1 또는 a1)(팬텀)

한 가지 중요한 사항은 ANSI SQL P3는 술어에 대한 삽입(및 일부 해석에 따르면 업데이트)만 금지하는 반면 위의 P3의 정의는 술어를 읽고 나면 술어를 만족시키는 쓰기(쓰기는 삽입, 업데이트 또는 삭제)를 금지한다는 것입니다.

따라서 팬텀 리드는 현재 실행 중인 트랜잭션의 시작 시점에 스냅샷을 반환하고 쿼리에 대해 동일한 결과를 제공하면 실제 팬텀 리드 이상으로부터 사용자를 보호할 수 있는 것처럼 간주할 수 있습니다.

원래 SQL Server 2PL(Two-Phase Locking) 구현에서 쿼리에 포함된 술어 잠금에 대해 동일한 결과를 반환합니다.

MVCC(Multi-Version Concurrency Control) 스냅샷 격리(Orgly name Serializable in Oracle)는 다른 트랜잭션이 이미 실행 중인 트랜잭션에 설정된 결과를 이미 실행하고 반환한 쿼리와 동일한 필터링 기준과 일치하는 행을 삽입/삭제하는 것을 실제로 방지하지 않습니다.

이러한 이유로 모든 직원에게 임금 인상을 적용하고자 하는 시나리오는 다음과 같습니다.

1. Tx1:SELECT SUM(salary) FROM employee where company_id = 1;
2. Tx2:INSERT INTO employee (id, name, company_id, salary) VALUES (100, 'John Doe', 1, 100000);
3. Tx1:UPDATE employee SET salary = salary * 1.1;
4. Tx2:COMMIT;
5. Tx1:COMMIT:

이 시나리오에서 CEO는 첫 번째 트랜잭션(Tx1)을 실행하므로 다음과 같습니다.

1. 그녀는 먼저 회사의 모든 월급의 합계를 확인합니다.
2. 한편, 인사부에서는 최근 John Do를 고용하여 10만 달러의 급여를 지급함에 따라 2차 거래(Tx2)를 진행하고 있습니다.
3. 사장님은 연봉이 10만원으로 인상된 것을 모르고 연봉 총액을 고려해 10% 인상이 가능하다고 판단합니다.
4. 한편, HR 트랜잭션 Tx2가 커밋됩니다.
5. Tx1이 커밋되었습니다.

흥! 사장님께서 오래된 스냅샷에 대한 결정을 내리셔서 현재 업데이트된 급여 예산으로는 유지할 수 없는 인상을 주셨군요.

이 사용 사례에 대한 자세한 설명(많은 다이어그램 포함)은 다음 게시물에서 확인할 수 있습니다.

이것은 팬텀 읽기입니까 아니면 쓰기 스큐입니까?

Jim Gray and co에 따르면 Write Skew는 다음과 같이 정의되므로 이는 Phantom Read라고 합니다.

A5B 쓰기 스큐 T1이 C()와 일치하는 x와 y를 읽은 다음 T2가 x와 y를 읽고 x를 쓰고 커밋한다고 가정합니다.그러면 T1은 y라고 씁니다.만약 x와 y사이에 제약조건이 있다면 그것은 위반될 수 있습니다.이력 측면에서:

A5B: r1[x]...r2[y]...w1[y]...w2[x]...(c1 및 c2 발생)

Oracle에서 트랜잭션 관리자는 MySQL과 같이 술어 잠금 또는 인덱스 범위 잠금(다음 키 잠금)을 사용하지 않기 때문에 위의 이상 징후를 탐지하거나 탐지하지 못할 수 있습니다.

PostgreSQL은 Bob이 직원 테이블에 대한 읽기를 실행하는 경우에만 이 이상 현상을 탐지할 수 있습니다. 그렇지 않으면 이 현상을 방지할 수 없습니다.

갱신하다

처음에, 저는 직렬화 가능성이 시간 주문도 의미할 것이라고 생각했습니다.그러나 Peter Bailis가 잘 설명한 바와 같이 벽 시계 순서화 또는 선형화 가능성은 엄격한 직렬화 가능성에 대해서만 가정됩니다.

따라서 엄격한 직렬화 가능 시스템에 대한 가정이 이루어졌습니다.하지만 그건 시리얼라이블이 제공해야 할 사항이 아닙니다.직렬화 가능 격리 모델은 시간에 대한 보장이 없으며, 작업이 직렬 실행과 동일한 경우에는 순서를 변경할 수 있습니다.

따라서 Serialable(직렬 가능) 정의에 따르면 두 번째 트랜잭션에서 읽기가 발생하지 않으면 이러한 Phantom Read(팬텀 읽기)가 발생할 수 있습니다.그러나 2PL에서 제공하는 Strict Serializable 모델에서는 두 번째 트랜잭션이 팬텀 리드로부터 보호하려는 동일한 항목에 대해 리드를 발행하지 않더라도 팬텀 리드가 방지됩니다.

관찰한 내용은 팬텀 판독이 아닙니다.쿼리가 두 번째로 실행될 때(팬텀이 예기치 않게 나타남) 새 행이 나타나는 경우입니다.

Oracle 및 Postgre 모두에서 팬텀 읽기로부터 보호됩니다.SQL withSERIALIZABLE고립의

Oracle과 Postgre의 차이점SQL은SERIALIZABLEOracle의 isolation level은 스냅샷 isolation(팬텀이 나타나지 않도록 방지하기에 충분한)만 제공하는 반면 Postgre에서는 snapshot isolation(스냅샷 격리)을 제공합니다.SQL은 진정한 직렬화 가능성을 보장합니다(즉, 동일한 결과를 초래하는 SQL 문의 직렬화가 항상 존재함).동일한 것을 Oracle 및 Postgre에서 얻고 싶다면SQL, 사용REPEATABLE READ포스트그레에서의 고립SQL 입니다.

블라드 미할체아의 대답이 완전히 틀렸다는 것을 지적하고 싶었습니다.

이것은 팬텀 읽기입니까 아니면 쓰기 스큐입니까?

두 가지 모두 - 여기에는 이상이 없습니다. 트랜잭션은 Tx1 -> Tx2로 직렬화할 수 있습니다.

SQL 표준 상태: "시리얼화 가능한 실행이란 SQL 트랜잭션을 동시에 실행하는 작업의 실행으로 정의되며, 이는 동일한 SQL 트랜잭션의 일부 직렬 실행과 동일한 효과를 가져옵니다."

PostgreSQL은 Bob이 직원 테이블에 대한 읽기를 실행하는 경우에만 이 이상 현상을 탐지할 수 있습니다. 그렇지 않으면 이 현상을 방지할 수 없습니다.

여기서 PostgreSQL의 동작은 100% 정확하며, 명백한 트랜잭션 순서를 "뒤집기"할 뿐입니다.

Postgres 설명서에서는 다음과 같이 팬텀 판독값을 정의합니다.

트랜잭션은 검색 조건을 만족하는 행 집합을 반환하는 쿼리를 다시 실행하고 최근에 커밋된 다른 트랜잭션으로 인해 조건을 만족하는 행 집합이 변경되었음을 발견합니다.

선택한 항목은 커밋된 다른 트랜잭션의 이전과 이후 모두 동일한 값을 반환하기 때문에 팬텀 읽기의 기준을 충족하지 못합니다.

언급URL : https://stackoverflow.com/questions/39567266/write-skew-anomaly-in-oracle-and-postgresql-does-not-rollback-transaction