Atomic locking of all rows of a JDBC batch update

Question

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?

Answer 1

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.

Answer 2

In this situation, if your threads can't be controlled to not overlap data, then the only solution would be to lock the entire table, which isn't a great solution as the other thread (or anything else doing DML on the table) would hang until the locking session commits or rolls back. The other thing you could try is to have the "smaller" guy (the one updating a single row) commit more frequently (potentially every row/execution), thus allowing for the deadlock (or lock-wait) situation to potentially occur less often. This has performance side effects for the "smaller" guy.

Control your monkeys!

-Jim