DROP TABLE IF NOT EXISTS zzz_list;
CREATE TABLE zzz_list
(
eai_trns_stat_cd VARCHAR(1)
) PARTITION BY LIST (eai_trns_stat_cd)
;
CREATE TABLE IF NOT EXISTS zzz_list_pt_list_1 PARTITION OF zzz_list FOR VALUES IN ('1');
CREATE TABLE IF NOT EXISTS zzz_list_pt_list_2 PARTITION OF zzz_list FOR VALUES IN ('2');
CREATE TABLE IF NOT EXISTS zzz_list_pt_list_3 PARTITION OF zzz_list FOR VALUES IN ('3');
INSERT INTO zzz_list VALUES (1);
INSERT INTO zzz_list VALUES (2);
INSERT INTO zzz_list VALUES (3);
1) 아래 두개의 세션을 열고 동시에 업데이트합니다. 동일로우라 두번째세션은 행상태가 됩니다.
세션#1
begin;
update zzz_list SET eai_trns_stat_cd=2 WHERE eai_trns_stat_cd=1;
세션#2
begin;
update zzz_list SET eai_trns_stat_cd=2 WHERE eai_trns_stat_cd=1;
2) 1번세션에서 commit 과 동시에 세션2에서는 아래 에러가 발생합니다.
SQL*> update zzz_list SET eai_trns_stat_cd=2 WHERE eai_trns_stat_cd=1;
ERROR: tuple to be deleted was already moved to another partition due to concurrent update
Time: 3317.355 ms (00:03.317)
enterprisedb@[local]:5444!:edb(09:08:32)
SQL!>
