oracle-databasetransactionsrowcount

rowcount in 2 concurrent transactions

I have the following code which updates a customer and if it doesn't exist it inserts/creates it.

UPDATE CUSTOMER SET ...
IF SQL%rowcount = 0 THEN
  INSERT INTO customer ..

The problem is this code is in a transaction. On production I get the following error, randomly:

ORA-00001: unique constraint (..) violated at the line with the INSERT INTO customer .. (see above)

My question is how do Oracle transactions work ? I have 2 transactions concurrently (rare, but may happen). Say transaction 1 has passed the IF with rowcount = 0 and it's not yet committed. Say transaction 2 has passed the IF with rowcount = 0 (because transaction 1 has not inserted yet anything). Then transaction 1 commits. Then transaction 2 commits, it will "keep" the rowcount = 0 or it will check the IF SQL%rowcount = 0 THEN again to take into consideration what transaction 1 has commited?

The concurrency issue I described is the only reason I can think of for the random error above that happens in production.

It may help to set a specific transaction ISOLATION Level?

Solution

  • Yes, that's exactly what's happening. The timing has to be just right, which is why it's intermittent. You can verify this if you open two sqlplus sessions.

    1. Session A: Updates, no rows changed so SQL%ROWCOUNT = 0.
    2. Session B: Updates, no rows changed so SQL%ROWCOUNT = 0.
    3. Session A: Passes the IF condition, so does the insert.
    4. Session B: Passes the IF condition, so does the insert.
    5. Session A: Commits.
    6. Session B: Commits. Oops, there's a constraint violation because now I can see session A's changes.

    Changing the isolation level will not help you. Oracle does not have any isolation level that lets you see uncommitted changes from another session (and that's a good thing).

    The first thing to do is change your UPDATE and INSERT into a MERGE statement. That way, you have only a single statement that will succeed or fail. As far as I'm concerned, having an insert and update separated by a condition is an antipattern. Something like this:

    MERGE INTO customer
USING ( SELECT customer_name FROM wherever ) source
   ON ( source.customer_name = customer.customer_name )
 WHEN NOT MATCHED THEN INSERT VALUES ( source.customer_name )
 WHEN MATCHED THEN UPDATE SET ( customer_name = source.customer_name );

    The drawback to MERGE is that it doesn't have a RETURNING INTO clause, so if you need that, you do have to go with the select/insert.

    Second, to stop two sessions from inserting at the same time, you need to do:

    LOCK TABLE customer IN SHARE MODE;

    I generally don't like making custom locks, but I don't know any other way around this. This will stop other sessions from modifying the table until the first session commits, though they can query it. This means access to the table is serialized, so if you have a lot of sessions trying to update this table, that may not be acceptable.