Application Mutex via MySQL InnoDB row locks

Question

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.

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):

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)

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).

Appreciate your help...

Answer 1

One can also use MySQL's and MariaDB's GET_LOCK (and RELEASE_LOCK) functions:

• https://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_get-lock (outdated link)
• https://dev.mysql.com/doc/refman/8.0/en/locking-functions.html#function_get-lock
• https://mariadb.com/kb/en/library/get_lock/

The functions can be used to realize the behavior described in the question.

---

Acquiring a lock my_app_lock_1.

SELECT GET_LOCK('my_app_lock_1', 1000); -- lock's name 'my_app_lock_1', timeout 1000 ms
+---------------------------------+
| GET_LOCK('my_app_lock_1', 1000) |
+---------------------------------+
|                               1 |
+---------------------------------+

Releasing the lock:

DO RELEASE_LOCK('my_app_lock_1'); -- DO makes a result set ignored

---

Please note (the quotes from MariaDB's documentation):

• Names are locked on a server-wide basis. If a name has been locked by one client, GET_LOCK() blocks any request by another client for a lock with the same name. This allows clients that agree on a given lock name to use the name to perform cooperative advisory locking. But be aware that it also allows a client that is not among the set of cooperating clients to lock a name, either inadvertently or deliberately, and thus prevent any of the cooperating clients from locking that name. One way to reduce the likelihood of this is to use lock names that are database-specific or application-specific. For example, use lock names of the form db_name.str or app_name.str.

• Locks obtained with GET_LOCK() do not interact with transactions.

Answer 2

Answering my own question here. A variation of this is what we eventually end up doing (in PHP):

<?php
$conn = mysql_connect('localhost', 'name', 'pass');
if (!$conn) {
  echo "Unable to connect to DB: " . mysql_error();
  exit;
 }
if (!mysql_select_db("my_db")) {
  echo "Unable to select mydbname: " . mysql_error();
  exit;
 }
mysql_query('SET AUTOCOMMIT=0'); //very important! this makes FOR UPDATE work                                                                                           
mysql_query('START TRANSACTION');
$sql = "SELECT * from my_mutex_table where entity_id = 'my_mutex_key' FOR UPDATE";
$result = mysql_query($sql);
if (!$result) {
  echo "Could not successfully run query ($sql) from DB: " . mysql_error();
  exit;
 }
if (mysql_num_rows($result) == 0) {
  echo "No rows found, nothing to print so am exiting";
  exit;
 }

echo 'Locked. Hit Enter to unlock...';
$response = trim(fgets(STDIN));
mysql_free_result($result);
echo "Unlocked\n";
?>

To verify it works run from two different consoles. Time performance is a bit worse than standard file lock based mutexes but still very acceptable.