해당 에러는 parallel 이나 direct load 로 table에 insert를 사용하고 트랜잭션의 종료를 제대로 해주지않고 select 문 수행시 발생합니다.
해결방법은 commit이나 rollback명령어를 수행하여 트랜잭션을 종료시키는 방법이 있습니다.
$ oerr ora 12838
12838, 00000, "cannot read/modify an object after modifying it in parallel"
// *Cause: Within the same transaction, an attempt was made to add read or
// modification statements on a table after it had been modified in parallel
// or with direct load. This is not permitted.
// *Action: Rewrite the transaction, or break it up into two transactions:
// one containing the initial modification and the second containing the
// parallel modification operation.
테스트를 통해 일반 insert와 parallel insert간에 어떤 lock이 발생하는지 알아보도록 하겠습니다.
모니터링용 세션(세션1)과 세션2,세션3 총 3개의 세션으로 테스트를 수행하였습니다.
---------------------------------------------------------------------------------------------------------------------------------------------
일반 insert
---------------------------------------------------------------------------------------------------------------------------------------------
세션1
SQL> select a.session_id,a.lock_type,a.MODE_REQUESTED,a.MODE_HELD,b.username
from dba_locks a,v$session b
where a.session_id=b.sid and b.username='YSBAE'
and program like '%sqlplus@dbarac1 (TNS V1-V3)%';
SESSION_ID LOCK_TYPE MODE_REQUESTED MODE_HELD USERNAME
---------- ------------ ---------------- -------------- -----------
2842 AE None Share YSBAE
2275 AE None Share YSBAE
세션2
SQL> insert into EMP_TEST select * from EMP_TEST;
14 rows created.
SQL> select count(*) from emp_test;
COUNT(*)
----------
28
세션3
정상적인 insert의 경우에는 commit을 수행하지 않았으므로 다른세션에서는 조회 시 트랜잭션 이전의 값을 조회하게 됩니다. 다른세션에서 insert도 잘됩니다.
SQL> select * from emp_test;
ENO DEPTNO COMM SAL MGR JOB
---------- ---------- ---------- ---------- ---------- ---------
7369 20 800 7902 CLERK
7499 30 300 1600 7698 SALESMAN
7521 30 500 1250 7698 SALESMAN
7566 20 2975 7839 MANAGER
7654 30 1400 1250 7698 SALESMAN
7698 30 2850 7839 MANAGER
7782 10 2450 7839 MANAGER
7788 20 3000 7566 ANALYST
7839 10 5000 PRESIDENT
7844 30 1500 7698 SALESMAN
7876 20 1100 7788 CLERK
7900 30 950 7698 CLERK
7902 20 3000 7566 ANALYST
7934 10 1300 7782 CLERK
SQL> insert into emp_test select * from emp;
14 rows created.
SQL> select count(*) from emp_test;
COUNT(*)
----------
28
세션1
dba_locks v$locked_object,v$lock등 lock과 관련된 딕셔너리 뷰 조회 결과 일반 insert시에는 Row-X(SX) Lock이 걸립니다. 이 lock은 3으로도 표시됩니다. 아래는 숫자에 따른 lock의 의미입니다.
0 - NONE: lock requested but not yet obtained
1 - NULL
2 - ROWS_S (SS): Row Share Lock
3 - ROW_X (SX): Row Exclusive Table Lock
4 - SHARE (S): Share Table Lock
5 - S/ROW-X (SSX): Share Row Exclusive Table Lock
6 - Exclusive (X): Exclusive Table Lock
SQL> select a.session_id,a.lock_type,a.MODE_REQUESTED,a.MODE_HELD,b.username
from dba_locks a,v$session b
where a.session_id=b.sid and b.username='YSBAE'
and program like '%sqlplus@dbarac1 (TNS V1-V3)%';
SESSION_ID LOCK_TYPE MODE_REQUESTED MODE_HELD USERNAME
---------- --------------- ----------------- ----------------- ---------------
2842 AE None Share YSBAE
2275 AE None Share YSBAE
2842 Transaction None Exclusive YSBAE
2842 DML None Row-X (SX) YSBAE
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID
--------- --------- --------- --------- ---------- -------------------- -------------- ---------------------------------- ----------- ---------
20 17 158142 172723 2842 YSBAE oracle 10551706 3 0
SQL> select * From v$lock
where sid in (select sid from v$session where username='YSBAE'
and program like '%sqlplus@dbarac1 (TNS V1-V3)%');
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
-------- -------- --------- ---- --------- --------- --------- --------- --------- --------- ---------
070002003320FD58 070002003320FDD8 2275 AE 133 0 4 0 2325 2 0
070002003320F8F8 070002003320F978 2842 AE 133 0 4 0 2263 2 0
070002000D8EF2C8 070002000D8EF350 2842 TX 720923 16381 6 0 9 2 0
00000001212AF870 00000001212AF8E0 2842 TM 172723 0 3 0 9 2 0세션2
세션2,세션3 둘다 insert문으로 14건의 insert가 있는 상태였는데, 세션2에서만 트랜잭션의 종료를 수행하자, 세션2에서는 자기자신의 insert 14건이 commit 되어 28건으로 조회되었습니다. 세션3에서는 세션2의 commit된 14건 값까지 조회되어 42건으로 조회되었으며, 세션3에서도 commit 시 세션2에서 세션3의 14건이 반영되거 42건으로 조회되었습니다.
SQL> commit;
Commit complete.
SQL> select count(*) from emp_test;
COUNT(*)
----------
28세션3
SQL> select count(*) from emp_test;
COUNT(*)
----------
42
SQL> commit;
Commit complete.세션2
SQL> select count(*) from emp_test;
COUNT(*)
----------
42
세션1
트랜잭션이 commit으로 정상종료된 후에는 다른 세션에서도 insert 된 값을 정상적으로 확인할 수 있고, Row-X(SX) Lock 도 해제된것을 확인할 수 있습니다.
SQL> select a.session_id,a.lock_type,a.MODE_REQUESTED,a.MODE_HELD,b.username
from dba_locks a,v$session b
where a.session_id=b.sid and b.username='YSBAE'
and program like '%sqlplus@dbarac1 (TNS V1-V3)%';
SESSION_ID LOCK_TYPE MODE_REQUESTED MODE_HELD USERNAME
---------- ----------- ----------------- ----------- -----------------
2842 AE None Share YSBAE
2275 AE None Share YSBAE
---------------------------------------------------------------------------------------------------------------------------------------------
Parallel Insert
---------------------------------------------------------------------------------------------------------------------------------------------
마찬가지로 동일하게 세션3개로 똑같은 테스트를 수행하고 모니터링 시 어떻게 나오는지 확인해보겠습니다.
세션1
수행전은 마찬가지로 동일합니다.
SQL> select a.session_id,a.lock_type,a.MODE_REQUESTED,a.MODE_HELD,b.username
from dba_locks a,v$session b
where a.session_id=b.sid and b.username='YSBAE'
and program like '%sqlplus@dbarac1 (TNS V1-V3)%';
SESSION_ID LOCK_TYPE MODE_REQUESTED MODE_HELD USERNAME
---------- ------------ ---------------- -------------- -----------
2842 AE None Share YSBAE
2275 AE None Share YSBAE세션2
parallel 4로 insert를 수행하였고, 트랜잭션의 종료없이 select문을 수행하였을 때 ORA-1283 에러가 발생합니다.
SQL> insert /*+PARALLEL(4) ENABLE_PARALLEL_DML */ into EMP_TEST select * from EMP_TEST;
SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';
STATISTIC VALUE CON_ID
-------------- --------- ---------
Servers Busy 4 0
1 rows selected.
SQL> select count(*) from emp_test;
select count(*) from emp_test
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel세션3
다른 세션에서는 정상적으로 조회가 됩니다. 하지만 insert 시 wait가 걸립니다.
SQL> select count(*) from emp_test;
COUNT(*)
----------
28
SQL> insert into emp_test select * from emp;
...waiting 중
세션1
모니터링 결과, exclusive lock이 걸리고 lock mode도 6이 생성된것을 확인할 수 있습니다.
SQL> select a.session_id,a.lock_type,a.MODE_REQUESTED,a.MODE_HELD,b.username
from dba_locks a,v$session b
where a.session_id=b.sid and b.username='YSBAE'
and program like '%sqlplus@dbarac1 (TNS V1-V3)%';
SESSION_ID LOCK_TYPE MODE_REQUESTED MODE_HELD USERNAME
---------- ---------------- ---------------- ------------- ----------
2842 PS None Share YSBAE
2842 PS None Share YSBAE
2842 PS None Share YSBAE
2842 AE None Share YSBAE
2275 AE None Share YSBAE
2842 PS None Share YSBAE
2842 Temp Segment None Share YSBAE
2842 DML None Exclusive YSBAE
2842 Transaction None Exclusive YSBAE
9 rows selected.
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID
--------- --------- --------- --------- ---------- ------------------ -------------- ---------------------------------- ----------- ---------
18 16 17933 172723 2842 YSBAE oracle 10551706 6 0
1 rows selected.
SQL> select * From v$lock
where sid in (select sid from v$session where username='YSBAE'
and program like '%sqlplus@dbarac1 (TNS V1-V3)%');
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
-------- -------- --------- ---- --------- --------- --------- --------- --------- --------- ---------
070002003320FD58 070002003320FDD8 2275 AE 133 0 4 0 2380 2 0
070002003320FF88 0700020033210008 2842 PS 1 0 4 0 6 2 0
070002003320CDD8 070002003320CE58 2842 PS 1 1 4 0 6 2 0
0700020033211A10 0700020033211A90 2842 PS 1 2 4 0 6 2 0
070002003320F8F8 070002003320F978 2842 AE 133 0 4 0 2318 2 0
070002003320F368 070002003320F3E8 2842 PS 1 3 4 0 6 2 0
070002000EF8DD88 070002000EF8DE58 2842 TS 4 32265476 4 0 6 2 0
070002000D8EF2C8 070002000D8EF350 2842 TX 2162695 6960 6 0 6 2 0
00000001212AF870 00000001212AF8E0 2842 TM 172723 0 6 0 6 2 0
9 rows selected.해소하는 방법으로는 트랜잭션을 종료하는 방법이 있습니다.
세션2
SQL> rollback;
Rollback complete.세션3
아까 구문이 waiting이 끝나고 완료됨.
SQL> insert into emp_test select * from emp;
14 rows created.
SQL> commit;
Commit complete.세션1
SQL> select a.session_id,a.lock_type,a.MODE_REQUESTED,a.MODE_HELD,b.username
from dba_locks a,v$session b
where a.session_id=b.sid and b.username='YSBAE'
and program like '%sqlplus@dbarac1 (TNS V1-V3)%';
SESSION_ID LOCK_TYPE MODE_REQUESTED MODE_HELD USERNAME
---------- ------------ ---------------- -------------- -----------
2842 AE None Share YSBAE
2275 AE None Share YSBAE
테스트 결과, 조회는 TM lock Exclusive lock(lock mode 6)의 경우에도 undo를 이용한 일관성있는 읽기가 가능합니다.
하지만 DML의 경우에는 일반 INSERT의 경우에는 다른세션에서도 INSERT문은 정상적으로 수행되고 반영은 안된 상태로 세션 waiting없는 상태로 유지되었습니다. 하지만 parallel insert의 경우에는 다른세션에서 동일한 테이블에 대해서 insert 문 수행시 waiting이 발생하게 되었고 이는, parallel insert문의 세션에서 트랜잭션을 종료할때 까지 계속 되었습니다.
댓글