[SOLVED] Atomic locking of all rows of a JDBC batch update

Atomic locking of all rows of a JDBC batch update

I have two threads running concurrent updates on a table similar to:

CREATE TABLE T (
  SEQ NUMBER(10) PRIMARY KEY,
  VAL1 VARCHAR2(10),
  VAL2 VARCHAR2(10)
)

The table is containing a large number of entries where the updates are similar to:

UPDATE T SET VAL1 = ? WHERE SEQ < ?
UPDATE T SET VAL2 = ? WHERE SEQ = ?

Both statements are run within two different transaction as JDBC batch updates with 1000 rows each. Doing so, I encounter ORA-00060: deadlock detected while waiting for resource fairly quickly. I assume that both transaction would partially affect the same rows where both transactions managed to lock some rows before the other one.

Is there a way to avoid this by making the locking atomic or would I need to introduce some form of explicit lock between the two threads?

Solution

When you update a record, a lock is taken to prevent dirty writes which would compromise Atomicity.

However, in your case, you could use SKIP LOCKED. This way, before you try to do the update you attempt to acquire the FOR UPDATE lock with SKIP LOCKED. This will allow you to lock the records that you plan to modify and also skipping the ones which are already locked by other concurrent transactions.

Check out the SkipLockJobQueueTest in my High-Performance Java Persistence GitHub repository for an example of how you can use SKIP LOCKED.