Ora-00060 When Performing Insert using WITH Clause And Table Locked (Doc ID 1250454.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.

Symptoms

On production 11.1.0.7/11.2.0.1 version,

When attempting to execute
LOCK TABLE AG_TT IN EXCLUSIVE MODE;


INSERT INTO AG_TT ( id, key_type_cd, key_field_cd, key_value , key_source_cd, start_dt, end_dt, is_active, is_Placeholder)
WITH all_keys as (
SELECT key_type_cd, key_value
FROM AG_TT
WHERE SYSDATE BETWEEN start_dt and NVL(end_Dt, SYSDATE)
)
SELECT rownum,
key_type_cd1,
key_field_cd1,
key_value1,
'DUMMY' key_source_cd,
SYSDATE start_dt,
to_date('31.12.9999','dd.mm.yyyy') end_dt,
'1' is_active,
'0' is_placeholder
FROM (
SELECT DISTINCT
vti.KEY_FIELD_CD1, vti.KEY_TYPE_CD1, vti.KEY_VALUE1
FROM ag_vti vti
WHERE vti.KEY_SOURCE_CD = 'Source1'
-- no valid master key
AND vti.KEY_TYPE_CD1 IS NOT NULL AND vti.KEY_VALUE1 IS NOT NULL
AND ( vti.KEY_TYPE_CD1, vti.KEY_VALUE1) NOT IN (
SELECT KEY_TYPE_CD, KEY_VALUE
FROM all_keys
)
-- no valid AK 1
AND ( NVL(vti.KEY_TYPE_CD2,CHR(1) ), NVL(vti.KEY_VALUE2, CHR(1)) ) NOT IN (
SELECT KEY_TYPE_CD, KEY_VALUE
FROM all_keys
)
-- no valid AK 2
AND ( NVL(vti.KEY_TYPE_CD3,CHR(1) ), NVL(vti.KEY_VALUE3, CHR(1)) ) NOT IN (
SELECT KEY_TYPE_CD, KEY_VALUE
FROM all_keys
)
-- no valid AK 3
AND ( NVL(vti.KEY_TYPE_CD4,CHR(1)), NVL(vti.KEY_VALUE4,CHR(1))) NOT IN (
SELECT KEY_TYPE_CD, KEY_VALUE
FROM all_keys
)
)
;
and the table has degree >1 then

the following error occurs.

ERROR
-----------------------
INSERT INTO AG_TT ( id, key_type_cd, key_field_cd, key_value , key_source_cd, start_dt, end_dt, is_active, is_Placeholder)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P001
ORA-00060: deadlock detected while waiting for resource


STEPS
-----------------------
The issue can be reproduced at will with the steps indicated above

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms