I am currently implementing a procedure that basically updates a set of rows.
Currently I use Oracle Database version 11.2.0.4 ("soon" to be upgraded to 19c).
The code is basically:
CREATE TABLE ITEMS (
ITEM_ID INTEGER PRIMARY KEY,
ITEM_STATUS INTEGER NOT NULL,
FLAG char(1) not null
);
create type array_of_numbers IS TABLE OF NUMBER;
create procedure proc1(
p_Ids IN array_of_numbers)
is
begin
update ITEMS set
ITEM_STATUS=1
where ITEM_ID IN (select value(x) from TABLE(p_Ids) x) and FLAG='a';
end;
What I am worried is deadlocks.
As [1] states, the order UPDATE locks rows is not guaranteed. So if two sessions are running the above code with parameters that select overlapping set of rows, there is a chance of deadlock.
Interestingly, in my testing, I could not get a deadlock.
What is (are) the correct ways to handle this?
SELECT FOR UPDATE followed by the actual UPDATE?
What about this “trick” (posted for PostgreSQL, not tested it on Oracle) from [2] that combines the SELECT … FOR UPDATE and UPDATE into a single statement, copied from that page:
UPDATE customer SET ...
WHERE id IN (
SELECT id
FROM customer
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE
)
[1] https://stackoverflow.com/a/56794278
[2] https://stackoverflow.com/q/10245560
PS: About the “And frustratingly, there is no way to order updates (or deletes) like there is for selects and inserts.” from[2], a hypothetical ORDER BY after WHERE in UPDATE would solve this. Or maybe not…