Oracle - SELECT-INSERT locking pattern

3
votes

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?

1
oracleconcurrencyplsql
If you have a unique index on the appropriate columns, skip the SELECT and just perform the INSERT. If the row already exists, the INSERT will fail and you can catch the duplicate-index exception. - DwB
@DwB, this is the main issue. I've written above that I can't have constraints. For example I can have many orders with the same type for the same user which have state = "Done". But I can have only one in state "Draft". - Lukasz Lysik
What about creating 2 tables. one for drafts (that uses a primary key or a unique index) and one for not-drafts. - DwB

1 Answers

6
votes

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 );