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?

If those deadlocks are not occurring too often you could just repeat that transaction until it goes through. Just catch the SQLRecoverableException / SQLRecoverableException. — Benjamin, Apr 11 '19 at 15:40
Thanks for your suggestion, I have tried this now and this is actually the best approach for little contention. I was also able to reduce the amount of dead locks by quite a bit by ordering the second batch by SEQ in reverse order. Oracle uses an index for SEQ and it seems to lock the rows in the reverse order of the index most of the time. — Rafael Winterhalter, Apr 15 '19 at 13:11

Vlad Mihalcea · Accepted Answer · 2021-01-09T15:26:32.673

14

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.

edited Jan 09 '21 at 15:26

answered Apr 11 '19 at 13:24

Vlad Mihalcea

142,745
71
566
911

1

I implemented a job queue using your article and tested it on mysql 8. In my implementation a job most of the time ends up inserting a new job to the jobqueue table. When I scale up to more nodes (2-3) I get dead lock exceptions (SQL Error 1205 and 1213) on the insert statement. I am not going to spend a lot of time debugging it. For now I am just changing to a JMS solution. – Lasse L Sep 08 '20 at 09:12
INSERT statements only take locks on the row itself, so I don't see how you'd get a deadlock for individual tasks. If you can provide a replicating test case, I could take a look and see what the problem is. – Vlad Mihalcea Sep 08 '20 at 10:45

score 1 · Answer 2 · answered Apr 11 '19 at 13:20

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

score 1 · Answer 3 · answered Apr 11 '19 at 13:25

I assume that both transaction would partially affect the same rows where both transactions managed to lock some rows before the other one.

That's right. I can suggest two options to avoid this:

1) Use SELECT ... FOR UPDATE clause before updating:

SELECT * FROM T WHERE SEQ < ? FOR UPDATE;
UPDATE T SET VAL1 = ? WHERE SEQ < ?

SELECT * FROM T WHERE SEQ = ? FOR UPDATE;
UPDATE T SET VAL2 = ? WHERE SEQ = ?

Predicates must be the same to affect the same rows.
FOR UPDATE clause makes Oracle lock the requested rows. And as long as another session uses FOR UPDATE clause for SELECT as well, it gets blocked untill the previous transaction is committed\rolled back.

2) Use DBMS_LOCK package to create and control a custom lock. Acquiring and releasing the lock has to be performed manually.

score 1 · Answer 4 · answered Apr 17 '19 at 09:47

One simple solution is to lock the table in share mode, to ensure no concurrent writes, before the largest update, with LOCK TABLE ... IN SHARE MODE.

Here are my two scripts if you want to reproduce: The main one creates the table and runs the test case - /tmp/sql1.sql:

set echo on time on define off sqlprompt "SQL1> " linesize 69 pagesize 1000
set sqlformat ansiconsole
connect sys/oracle@//localhost/PDB1 as sysdba
grant dba to scott identified by tiger;
connect scott/tiger@//localhost/PDB1
exec begin execute immediate 'drop table T'; exception when others then null; end;
CREATE TABLE T (
  SEQ NUMBER(10) constraint T_SEQ PRIMARY KEY,
  VAL1 VARCHAR2(10),
  VAL2 VARCHAR2(10)
);
insert into T select rownum , 0 , 0 from xmltable('1 to 5');
commit;
-- -------- start session 1
connect scott/tiger@//localhost/PDB1
select sys_context('userenv','sid') from dual;
variable val number
variable seq number;
exec :seq:=4; :val:=2;
UPDATE T SET VAL2 = :val WHERE SEQ = :seq;
-- -------- call session 2
host sql /nolog @/tmp/sql2.sql < /dev/null & :
host sleep 5
select session_id,lock_type,mode_held,mode_requested,lock_id1,lock_id2,blocking_others from dba_locks where lock_type in ('DML','Transaction','PL/SQL User Lock');
-- -------- continue session 1 while session 2 waits
exec :seq:=1; :val:=3;
UPDATE T SET VAL2 = :val WHERE SEQ = :seq;
host sleep 1
commit;
select * from T;
-- -------- end session 1

The second one is called in the main one to be run concurrently - /tmp/sql2.sql:

set echo on time on define off sqlprompt "SQL2> "
-- -------- start session 2 -------- --
host sleep 1
connect scott/tiger@//localhost/PDB1
select sys_context('userenv','sid') from dual;
variable val number
variable seq number;
exec :seq:=5; :val:=1;
/* TM lock solution */ lock table T in share mode;
UPDATE T SET VAL1 = :val WHERE SEQ < :seq;
commit;
select * from T;
-- -------- end session 2

Here is the run with the shared lock, where we see DML lock 'Share' blocked by 'Row-X' (which is the one automatically acquired by updates):

SQLcl: Release 18.4 Production on Wed Apr 17 09:32:04 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL>
SQL> set echo on time on define off sqlprompt "SQL1> " linesize 69 pagesize 1000
09:32:04 SQL1> set sqlformat ansiconsole
09:32:04 SQL1> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
09:32:05 SQL1>
09:32:05 SQL1> grant dba to scott identified by tiger;

Grant succeeded.

09:32:05 SQL1> connect scott/tiger@//localhost/PDB1
Connected.
09:32:08 SQL1>
09:32:08 SQL1> exec begin execute immediate 'drop table T'; exception when others then null; end;

PL/SQL procedure successfully completed.

09:32:09 SQL1> CREATE TABLE T (
  2    SEQ NUMBER(10) constraint T_SEQ PRIMARY KEY,
  3    VAL1 VARCHAR2(10),
  4    VAL2 VARCHAR2(10)
  5  );

Table created.

09:32:09 SQL1> insert into T select rownum , 0 , 0 from xmltable('1 to 5');

5 rows created.

09:32:09 SQL1> commit;

Commit complete.

09:32:09 SQL1> -- -------- start session 1
09:32:09 SQL1> connect scott/tiger@//localhost/PDB1
Connected.
09:32:09 SQL1>
09:32:09 SQL1> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
4479


09:32:09 SQL1> variable val number
09:32:09 SQL1> variable seq number;
09:32:09 SQL1> exec :seq:=4; :val:=2;

PL/SQL procedure successfully completed.

09:32:09 SQL1> UPDATE T SET VAL2 = :val WHERE SEQ = :seq;

1 row updated.

09:32:09 SQL1> -- -------- call session 2
09:32:09 SQL1> host sql /nolog @/tmp/sql2.sql < /dev/null & :

09:32:09 SQL1> host sleep 5

SQLcl: Release 18.4 Production on Wed Apr 17 09:32:10 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

09:32:10 SQL2> -- -------- start session 2 -------- --
09:32:10 SQL2> host sleep 1

09:32:11 SQL2> connect scott/tiger@//localhost/PDB1
Connected.
09:32:11 SQL2> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
4478


09:32:12 SQL2> variable val number
09:32:12 SQL2> variable seq number;
09:32:12 SQL2> exec :seq:=5; :val:=1;

PL/SQL procedure successfully completed.

09:32:12 SQL2> /* TM lock solution */
09:32:12 SQL2>  lock table T in share mode;

09:32:14 SQL1> select session_id,lock_type,mode_held,mode_requested,lock_id1,lock_id2,blocking_others from dba_locks where lock_type in ('DML','Transaction','PL/SQL User Lock');
  SESSION_ID LOCK_TYPE     MODE_HELD    MODE_REQUESTED   LOCK_ID1   LOCK_ID2   BLOCKING_OTHERS
        4478 DML           None         Share            73192      0          Not Blocking
        4479 DML           Row-X (SX)   None             73192      0          Blocking
        4479 Transaction   Exclusive    None             655386     430384     Not Blocking


09:32:14 SQL1> -- -------- continue session 1 while session 2 waits
09:32:14 SQL1> exec :seq:=1; :val:=3;

PL/SQL procedure successfully completed.

09:32:17 SQL1> UPDATE T SET VAL2 = :val WHERE SEQ = :seq;

1 row updated.

09:32:17 SQL1> host sleep 1

09:32:18 SQL1> commit;

Lock succeeded.


Commit complete.

09:32:18 SQL2> UPDATE T SET VAL1 = :val WHERE SEQ < :seq;
09:32:18 SQL1> select * from T;

4 rows updated.

09:32:18 SQL2> commit;

Commit complete.

09:32:18 SQL2> select * from T;
  SEQ VAL1   VAL2
    1 1      3
    2 1      0
    3 1      0
    4 1      2
    5 0      0


09:32:18 SQL1> -- -------- end session 1

  SEQ VAL1   VAL2
    1 1      3
    2 1      0
    3 1      0
    4 1      2
    5 0      0


09:32:18 SQL2> -- -------- end session 2

09:32:18 SQL2>
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

And same example without the shared lock, where we see Transaction exclusive lock (which is the one when an update encounters a row locked by another transaction) leading to deadlock:

SQLcl: Release 18.4 Production on Wed Apr 17 09:39:35 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL>
SQL> set echo on time on define off sqlprompt "SQL1> " linesize 69 pagesize 1000
09:39:35 SQL1> set sqlformat ansiconsole
09:39:35 SQL1> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
09:39:36 SQL1>
09:39:36 SQL1> grant dba to scott identified by tiger;

Grant succeeded.

09:39:36 SQL1> connect scott/tiger@//localhost/PDB1
Connected.
09:39:36 SQL1>
09:39:36 SQL1> exec begin execute immediate 'drop table T'; exception when others then null; end;

PL/SQL procedure successfully completed.

09:39:37 SQL1> CREATE TABLE T (
  2    SEQ NUMBER(10) constraint T_SEQ PRIMARY KEY,
  3    VAL1 VARCHAR2(10),
  4    VAL2 VARCHAR2(10)
  5  );

Table created.

09:39:37 SQL1> insert into T select rownum , 0 , 0 from xmltable('1 to 5');

5 rows created.

09:39:37 SQL1> commit;

Commit complete.

09:39:37 SQL1> -- -------- start session 1
09:39:37 SQL1> connect scott/tiger@//localhost/PDB1
Connected.
09:39:37 SQL1>
09:39:37 SQL1> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
4479


09:39:37 SQL1> variable val number
09:39:37 SQL1> variable seq number;
09:39:37 SQL1> exec :seq:=4; :val:=2;

PL/SQL procedure successfully completed.

09:39:37 SQL1> UPDATE T SET VAL2 = :val WHERE SEQ = :seq;

1 row updated.

09:39:37 SQL1> -- -------- call session 2
09:39:37 SQL1> host sql /nolog @/tmp/sql2.sql < /dev/null & :

09:39:37 SQL1> host sleep 5

SQLcl: Release 18.4 Production on Wed Apr 17 09:39:38 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

09:39:38 SQL2> -- -------- start session 2 -------- --
09:39:38 SQL2> host sleep 1

09:39:39 SQL2> connect scott/tiger@//localhost/PDB1
Connected.
09:39:39 SQL2> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
4478


09:39:40 SQL2> variable val number
09:39:40 SQL2> variable seq number;
09:39:40 SQL2> exec :seq:=5; :val:=1;

PL/SQL procedure successfully completed.

09:39:40 SQL2> /* TM lock solution */
09:39:40 SQL2>  --lock table T in share mode;
09:39:40 SQL2> UPDATE T SET VAL1 = :val WHERE SEQ < :seq;

09:39:42 SQL1> select session_id,lock_type,mode_held,mode_requested,lock_id1,lock_id2,blocking_others from dba_locks where lock_type in ('DML','Transaction','PL/SQL User Lock');
  SESSION_ID LOCK_TYPE     MODE_HELD    MODE_REQUESTED   LOCK_ID1   LOCK_ID2   BLOCKING_OTHERS
        4478 Transaction   None         Exclusive        655368     430383     Not Blocking
        4479 DML           Row-X (SX)   None             73194      0          Not Blocking
        4478 DML           Row-X (SX)   None             73194      0          Not Blocking
        4479 Transaction   Exclusive    None             655368     430383     Blocking
        4478 Transaction   Exclusive    None             589838     281188     Not Blocking


09:39:46 SQL1> -- -------- continue session 1 while session 2 waits
09:39:46 SQL1> exec :seq:=1; :val:=3;

PL/SQL procedure successfully completed.

09:39:46 SQL1> UPDATE T SET VAL2 = :val WHERE SEQ = :seq;

1 row updated.

09:39:47 SQL1> host sleep 1

UPDATE T SET VAL1 = :val WHERE SEQ < :seq
             *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

09:39:47 SQL2> commit;

Commit complete.

09:39:47 SQL2> select * from T;
  SEQ VAL1   VAL2
    1 0      0
    2 0      0
    3 0      0
    4 0      0
    5 0      0


09:39:47 SQL2> -- -------- end session 2

09:39:47 SQL2>
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

09:39:48 SQL1> commit;

Commit complete.

09:39:48 SQL1> select * from T;
  SEQ VAL1   VAL2
    1 0      3
    2 0      0
    3 0      0
    4 0      2
    5 0      0


09:39:48 SQL1> -- -------- end session 1

This share lock prevents all concurrent modifications, even some modifications on tables linked by referential integrity, so take care of the overall write activity on them. Another solution is to use a custom user lock with dbms_lock to serialize the two sets of updates.

Regards, Franck.

Rafael Winterhalter · Answer 5 · 2019-04-16T08:14:39.460

I found a solution that required a bit of redesign on the inserting side but essentially still does the same thing as before. I have split the table into two tables:

CREATE TABLE T1 (
  SEQ NUMBER(10) PRIMARY KEY,
  VAL1 VARCHAR2(10)
);

CREATE TABLE T2 (
  SEQ NUMBER(10) PRIMARY KEY,
  VAL2 VARCHAR2(10)
);

Now I can update the columns without locking the same row, in a way I am emulating a column lock doing so. This would of course a major change but Oracle fortunately allows to define a materialized view for avoiding to change any selects:

CREATE MATERIALIZED VIEW LOG ON T1 WITH ROWID INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON T2 WITH ROWID INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW T 
REFRESH FAST ON COMMIT
AS
SELECT SEQ, VAL1, VAL2, T1.ROWID AS T1_ROWID, T2.ROWID AS T2_ROWID
FROM T1
NATURAL JOIN T2;

Doing so, I was able to retain all indices on the base table T which typically contained both VAL1 and VAL2.

Before this, I was able to reduce the amount of dead locks drastically by applying the batch updates in a given order (from highest SEQ to lowest). As a result, Oracle seemed to often use the index order to lock the tables but this was not 100% reliable either.

Maybe using a dynamic view and `select * from T for update of VAL1;` could also work in your case. https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805#followup-11904479515494 — SubOptimal, Apr 17 '19 at 07:20

Atomic locking of all rows of a JDBC batch update

5 Answers5