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.
January 25, 2018 - 1:49 am UTC
Nice input.