0

I am working with an Oracle database (11g Release 2). Imagine multiple connections doing the following simultaneously:

  1. Start transaction
  2. Check if a specific value exists in a table of unique values
  3. If the value does not exist, insert it
  4. Commit transaction

It seems to me that the only way to prevent conflicts is to block connections from performing the above 4-step sequence while any other connection is currently performing the 4-step sequence.

Can transactions achieve this kind of broad locking/blocking in Oracle?

Thanks in advance for your answers and advice on how to best deal with this scenario.

misha256
  • 337
  • 3
  • 16

1 Answers1

1

Add a unique check constraint, and implement an exception handler to get the next sequence and try again.

This is assuming you're using pl/sql.

An alternative would be using an Oracle sequence, with cache size 1. This will also ensure no gaps in the sequence 2. SELECT * FROM table_name FOR UPDATE to block all reads from other sessions...

Panayiotis Savva
  • 134
  • 8
  • Ah yes, the unique check constraint – that prevents duplicates but won't prevent the inevitable exceptions (btw the values are text strings arising client-side so sequences won't work here). I was hoping to avoid exceptions altogether; wouldn't it be nice if Oracle could serialize my transactions with a read-blocking option. – misha256 Sep 22 '14 at 06:26
  • 1
    Then you could try 'for update' in your select statement. You can either lock the whole table by omitting the where clause, or lock specific rows by specifying the rows in the where clause – Panayiotis Savva Sep 22 '14 at 09:45
  • Meh, 'for update' seemed promising but it doesn't block reads either. In other words, it still won't prevent my application from doing Step 2. Seems nothing blocks readers in Oracle. Might just have to catch exceptions client-side. No big deal, just not as neat and tidy as I would have liked it. – misha256 Sep 22 '14 at 21:11
  • I suggest you have a look here : http://docs.oracle.com/cd/E17952_01/refman-5.0-en/select.html, you have 2 modes of locking. Either in shared mode which allows other sessions too read, or using for update without shared mode, which blocks selecting from other sessions for the rows selected. If you want to block all others from reading from the table, use for update without a where clause. – Panayiotis Savva Sep 23 '14 at 04:11
  • I appreciate your efforts – thank you. The above link is actually MySQL documentation, but I have found the Oracle Database equivalent. I shall do some tests and see how it goes. Will update you in a day or two and mark your answer correct if it works :-) – misha256 Sep 23 '14 at 06:58
  • Greek God, you're spot on with SELECT FOR UPDATE. The only caveat is that normal SELECTs (those that don't include FOR UPDATE) are still not blocked but I can see why that's a good thing – exclusive read locking would compromise the performance of all queries hitting this table. – misha256 Sep 25 '14 at 02:35