0

Applying the dining philosophers problem to innodb, and the use of select for update:

1) a table with 100,000 forks, represented by an innodb table with its single primary key (the fork)

2) the grab of forks is a select for update that specifies some random list of between 2 and 20 primary keys in this table, and the "grab" is exactly one select for update on this random fork list relative to the 100,000 different primary keys. By the way, the order of the lists of keys in the select statement is random.

3) there are many more than 5 thinkers, say up to 50 "simultaneous" transactions grabbing intersecting forks

4) the think time is updating other columns for the rows of the primary keys in step 2. The think time is never too long.

Question: with innodb, is deadlock or starvation due to unfairness possible at all in the above scenario? Are there any things to watch out for? (isolation level read committed.)

Purpose: use this table and the primary keys of the locking rows to represent dependencies for complex update scenarios, so that before doing a sequence of updates to various related tables (tables in consistent order) and rows within, I determine the set of dependencies that equate to the set of primary keys of step 1, and then lock those dependencies with select for update at the outset of the transaction. (Of course, after the select for update, I requery the dependencies to make sure they did not change.)

Andy Nuss
  • 745
  • 1
  • 7
  • 20

1 Answers1

0

I don't know what you mean by "unfairness", but yes, a deadlock would be possible. Example: two thinkers try to take the same forks, say 1 and 2, but in opposite order. So thinker one "takes" fork one and thinker two takes fork two. After that, both would wait for the second fork "forever"...

You can overcome that problem easily by ordering the access to the forks. That way the first thinker would take fork one, the second would have to wait for the first thinker to finish and everything is fine.

Argeman
  • 1,345
  • 8
  • 22
  • How is this possible? The 2 thinkers do it in one select for update statement that specifies the same 2 forks. Does mysql, within the select for update statement, or any real update statement for that matter, have a sequence of individual write row locks that depend on the order of the keys in the list? – Andy Nuss Feb 13 '13 at 20:31
  • I have missed that it is only one select. But still, to be sure, I would put an "order by id" at the end of the select. Otherwise you can't be sure that innodb does that for you! – Argeman Feb 14 '13 at 08:36