MySQL Forums
Forum List  »  InnoDB

Re: hard to test select for update scenario
Posted by: Andrew Nuss
Date: February 14, 2013 06:20PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2325
February 13, 2013 03:03PM
Re: hard to test select for update scenario
963
February 14, 2013 06:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.