Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Simone.

Asked: January 23, 2018 - 4:41 pm UTC

Last updated: January 25, 2018 - 1:49 am UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked

Hi Tom,
I have this problem:
I need to "lock for update" records in two disconnected tables. For "disconnected" I mean two tables that haven't any relation, for example the tables EMPLOYEE and FOLDER: when I have to update an employee record I would have to update a folder record which has no foreign keys with the EMPLOYEE one.

My code is something like this:

-----------------------------------------
PROCEDURE UpdateEmployee
IS
   CURSOR myCurE
        is select * from EMPLOYEE
           where id = 5
           for update;
   CURSOR myCurF
        is select * from FOLDER
           where id = 108
           for update;
BEGIN
    open myCurE;
    open myCurF;

    -- updating code here
    
    close myCurE;
    close myCurF;
    
    -- commit is in the calling procedure
END;
-----------------------------------------


I think my code is unsafe because the two tables are locked in different moments and a deadlock may occur.
Is there a way to lock records in two or more disconnected tables in one time?
I considered to use a full join but it give me the ORA-02014 error

Thanks in advance for your help


and Connor said...

Do you have foreign key between the tables ? If so, then you can indeed lock a join, eg

SQL> select * from emp, dept where emp.deptno = dept.deptno for update;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10         10 ACCOUNTING     NEW YORK
      7839 KING       PRESIDENT            17-NOV-81       5000                    10         10 ACCOUNTING     NEW YORK
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10         10 ACCOUNTING     NEW YORK
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20         20 RESEARCH       DALLAS
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20         20 RESEARCH       DALLAS
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20         20 RESEARCH       DALLAS
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20         20 RESEARCH       DALLAS
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20         20 RESEARCH       DALLAS
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30         30 SALES          CHICAGO
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30         30 SALES          CHICAGO
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30         30 SALES          CHICAGO
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30         30 SALES          CHICAGO
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30         30 SALES          CHICAGO
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30         30 SALES          CHICAGO
      
SQL> select  l.type,
  2          o.name
  3  from  v$lock l, sys.obj$ o
  4  where l.id1 = o.obj#
  5  and l.type in ('TM','TX');

TY NAME
-- ------------------------------
TM EMP
TM DEPT



If not, then it really becomes your responsibility to ensure that *everyone* uses your UpdateEmployee procedure, namely, that when locking tables you always lock EMPLOYEE first and then FOLDER. Similarly, if you have a process that only locks FOLDER, then it cannot permit subsequently calling another routine that might lock EMPLOYEE.

The other option to consider is NOWAIT locks so that you can safely return an error to the caller, rather than running the risk of deadlocks.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Consider this

Gh, January 24, 2018 - 6:28 am UTC

If hash partitioning (or interval range) on id is possible on both this will be a good solution to avoid maximum of locks. Additionally try to specify explicitly the columns for update or maybe not use cursor for update but Merge instead

Deadlocks always possible

Dan Blum, January 24, 2018 - 6:38 pm UTC

You can in fact lock disparate rows in the same statement. This works fine:

select 1 from employee e, folder f
where e.id=5 and f.id=108
for update;

However, a deadlock is always possible when locking more than one row whether they're in the same table or not; Oracle locks the rows individually rather than as a single atomic operation. So even if you do this you still need to heed Connor's advice.
Connor McDonald
January 25, 2018 - 1:49 am UTC

Nice input.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database