StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POApplication Mutex via MySQL InnoDB row locks
text
Body
copied!<p>My application consists of a couple of Apache servers talking to a common MySQL box. Part of the application lets users create one hour appointments in the future. I need a mechanism which prevents different users coming from the different Apache instances at the same time, book the same one hour appointment slot. I've seen a similar "inter-system mutex" solution implemented on Oracle databases (basically 'select ... for update') but haven't dealt with the details on doing the same with MySQL. Would appreciate any advise, code or documentation references, best practices, etc. Did try to google around but mostly discussions about the MySQL internal mutexes come up. </p> <p>These are my MySQL settings I thought relevant (my code will have try-catch and all and should never bail without unlocking what it locked but have to account for what happens in those cases as well):</p> <pre><code>mysql> show variables like 'innodb_rollback_on_timeout'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_rollback_on_timeout | OFF | +----------------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_lock_wait_timeout'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 100 | +--------------------------+-------+ 1 row in set (0.00 sec) mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) </code></pre> <p>Any alternate solutions (outside of MySQL) you could recommend? I do have a memcached instance running as well but gets flushed rather often (and not sure I want to have memcachedb, etc. to make that fault tolerant).</p> <p>Appreciate your help...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload