MySQL Forums
Forum List  »  InnoDB

hard to test select for update scenario
Posted by: Andrew Nuss
Date: February 13, 2013 03:03PM


Consider a table with just one column, the primary key, a string, or possibly a second counting column with no meaning. It has 1000000s of rows. Transactions that use the rows in this table for locking semantics will pass a list of keys to select for update.

What if the same set of 1000 keys is "selected for update" by simultaneous transactions but in very different order? My guess is that this would be the same as a batch update against the scrambled keysets that increment a second "counter" column.

The isolation level is read committed so this might have some impact.

I don't know how fast the individual row locks are gained, but basically, if the transactions begin at the same millisecond, and the 2 orders are completely inverted, it would seem that if the single select for update or batch update gains the locks one by one, both transactions would hang, most definitely by the time they reach the end of their 2 lists. ?


Options: ReplyQuote

Written By
hard to test select for update scenario
February 13, 2013 03:03PM

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.