will a bulk merge using ordered elements avoid deadlocks?
Hello all,
I have a scenario where multiple transactions can try to do a bulk merge using same rows. In order to avoid deadlocks, we can of course use select for update. However, this solution is giving us another problem, therefore we would like to avoid its use for now.
We think that another possible solution would be to do a bulk merge using elements that have been ordered previously. For example, if my query has the following structure:
MERGE INTO my_table mt
USING (
.. complex select query using ORDER BY
ON (...)
WHEN MATCHED THEN
... an update here
WHEN NOT MATCHED THEN
... an insert here
Is it correct to assume that deadlocks could not occur in this situation? For example, let's say we have the following:
- before odering, transA with elements 1,2 and transB with elements 2,1
-> this could of course cause a deadlock if transA locks 1 and transB locks 2
- after ordeing, transA with elements 1,2 and transB with elements 1,2
-> theorically, one transaction will wait
I have done some quick tests using an ordered collection instead of the select query and it seems to work fine. However, I prefer to ask the DBA community just in case I'm missing something. Any help is very much appreciated.
wf