Oracle - SELECT-INSERT locking pattern

Question

I have a table Orders (with columns orderId, orderType, userId, state, ...). I need my process to do the following:

1. Check if there exist an order with specific type, with specific state for specific user (SELECT).
2. If such an order doesn't exist - create one (INSERT).

So basically I want to assure that there is always only one order with:

• orderType = x
• userId = y
• state = z

I can't however create constraints because there can exist more than one order for x1, y1, z1.

I must say that I'm not experienced in Oracle. I've read this article on locks in Oracle and it seams that the only lock type which would be useful here is:

LOCK TABLE Orders IN EXCLUSIVE MODE

But I think it is overkill to lock whole table only for some subset of data. I tried SELECT ... FOR UPDATE OF <update_column> using different columns for <update_column> but they allowed me to insert new rows.

Is there any pattern for this type of concurrency? It seams that Oracle created SELECT ... FOR UPDATE OF ... for the SELECT-UPDATE pattern. Is there anything similar for SELECT-INSERT?

Answer 1

You can create a unique function-based index to enforce this sort of constraint. If you want to enforce that there is a unique row with a state of "Done" but allow many rows with a state of "Draft".

CREATE UNIQUE INDEX idx_index_name
    ON( CASE WHEN state = 'Done' THEN orderType ELSE NULL END,
        CASE WHEN state = 'Done' THEN userId ELSE NULL END,
        CASE WHEN state = 'Done' THEN state ELSE NULL END );