MERGE STATEMENT FAILS WITH ORA-00001 UNIQUE CONSTRAINT VIOLATION (Doc ID 1081283.1)

Symptoms

MERGE STATEMENT AFTER UNCOMMITTED UPDATE FAILS WITH ORA-1

The problem can be reproduced as follows:
1.
Session #1

oracle@thor:~> sqlplus login/password

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 21 16:40:52 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With
the Partitioning, OLAP, Data Mining and Real Application Testing options

2.
SQL> CREATE TABLE table1
(
ORDER_LINE_ID NUMBER(19) NOT NULL,
click NUMBER NOT NULL,
CONSTRAINT PK3
PRIMARY KEY
( ORDER_LINE_ID)
)
ORGANIZATION INDEX;

Table created.

3.
SQL> CREATE TABLE TABLE2
(
ORDER_LINE_ID NUMBER,
CLICK NUMBER
);
Table created.

4.
SQL> Insert into TABLE1 (ORDER_LINE_ID, CLICK) Values (10430001, 20);
1 row created.

SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10210001, 10);
1 row created.

SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10210001, 20);
1 row created.

SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10430001, 50);
1 row created.

SQL> commit;
Commit complete.

5.
SQL> UPDATE table1 set click=click+5 WHERE order_line_id=10430001;
1 row updated.

6.
Session #2

SQL> connect login/password
Connected.
SQL> set autotrace on explain
SQL> MERGE INTO table1 t1
USING(
SELECT
fact.ORDER_LINE_ID,
sum (fact.click
) AS click
FROM table2 fact
GROUP by fact.ORDER_LINE_ID
ORDER BY fact.ORDER_LINE_ID
) t2 ON (
t1.ORDER_LINE_ID = t2.ORDER_LINE_ID
)
WHEN MATCHED THEN
UPDATE SET
t1.click = t2.click+10
WHEN NOT MATCHED THEN
INSERT (
ORDER_LINE_ID,
click
) VALUES (
t2.ORDER_LINE_ID,
t2.click) ;

MERGE INTO table1 t1
(the session is waiting)

7.
Go back to Session #1 and enter commit
SQL> commit;
Commit complete.

Now i see in session 1 the following about the waiting MERGE statement:
*
ERROR at line 1:
ORA-00001: unique constraint (OWNER.PK3) violated

**** Failure Reproduced *****


SQL>

8.
Go to session #2 and re-run the merge sql
SQL> /
2 rows merged.

Execution Plan
----------------------------------------------------------
Plan hash value: 2274614803

------------------------------------------------------------------------------
--

-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

------------------------------------------------------------------------------
--

-

| 0 | MERGE STATEMENT | | 2 | 130 | 4 (25)|
00:00:01
|

| 1 | MERGE | TABLE1 | | | |
|

| 2 | VIEW | | | | |
|

| 3 | NESTED LOOPS OUTER | | 2 | 170 | 4 (25)|
00:00:01
|

| 4 | VIEW | | 2 | 52 | 4 (25)|
00:00:01
|

| 5 | SORT GROUP BY | | 2 | 52 | 4 (25)|
00:00:01
|

| 6 | TABLE ACCESS FULL| TABLE2 | 2 | 52 | 3 (0)|
00:00:01
|

|* 7 | INDEX UNIQUE SCAN | PK3 | 1 | 59 | 0 (0)|
00:00:01
|

------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("T1"."ORDER_LINE_ID"(+)="T2"."ORDER_LINE_ID")

Note
-----
- dynamic sampling used for this statement


SQL>

DIAGNOSTIC ANALYSIS:
--------------------
Execution Plan
----------------------------------------------------------
Plan hash value: 2274614803

------------------------------------------------------------------------------ 

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

------------------------------------------------------------------------------

| 0 | MERGE STATEMENT | | 2 | 130 | 4 (25)|
00:00:01
|

| 1 | MERGE | TABLE1 | | | |
|

| 2 | VIEW | | | | |
|

| 3 | NESTED LOOPS OUTER | | 2 | 170 | 4 (25)|
00:00:01
|

| 4 | VIEW | | 2 | 52 | 4 (25)|
00:00:01
|

| 5 | SORT GROUP BY | | 2 | 52 | 4 (25)|
00:00:01
|

| 6 | TABLE ACCESS FULL| TABLE2 | 2 | 52 | 3 (0)|
00:00:01
|

|* 7 | INDEX UNIQUE SCAN | PK3 | 1 | 59 | 0 (0)|
00:00:01
|

------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("T1"."ORDER_LINE_ID"(+)="T2"."ORDER_LINE_ID")

Note
-----
dynamic sampling used for this statement

WORKAROUND:
-----------
COMMIT update before running MERGE command.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.