Re: hard to test select for update scenario
I guess what I hoped was some certainty that if the lists were sorted,
say the first was ids 1-100, in that order,
and the second was ids 50-150, in that order,
and the first completes in 20 milliseconds, the second will block only for 20 milliseconds, and never(!) deadlock. Your "probably" worries me.
(1) subquestion: is this idea a good stategy for maintaining tree relationships too complex for foreign keys? I.e. I have 5 tree-like tables, and when I do an update to my "tree", I determine all the dependent root objects, and lock them, in all the transactions that update any portion of the tree. I hope that this will ensure that all the short running transactions that update the 5 tree tables will have lock blockage only on the numbered lock items being discussed, and never deadlock??
(2) Which is better for the lock strategy of the numbered dependent keys:
a) select t.mykey from LockTable t where t.key in (:sortedkeylist) for update
b) update t.mydummycount
set t.mydummycount = t.mydummycount + 1
where t.mykey in (:sortedkeylist)
c) same as a, but iterating the sorted list in order in the client and selecting one key at a time
d) same as be, but iterating the sorted key list and making one update to mydummycount at a time.
It seems like a) and b) are faster and better for the database engine, but if you say "probably" my approach avoids deadlock, then c) or d) would be better.
Andy