通过 MySQL InnoDB 行锁的应用程序互斥

Question

我的应用程序由几个与通用 MySQL 机器通信的 Apache 服务器组成。该应用程序的一部分允许用户创建未来一小时的约会。我需要一种机制来防止不同的用户同时来自不同的 Apache 实例，预订相同的一小时预约时段。我已经看到在 Oracle 数据库上实现了类似的“系统间互斥”解决方案（基本上是“选择...进行更新”），但还没有处理在 MySQL 上执行相同操作的详细信息。希望有任何建议、代码或文档参考、最佳实践等。我尝试用谷歌搜索，但主要是关于 MySQL 内部互斥体的讨论。

这些是我认为相关的 MySQL 设置（我的代码将具有 try-catch 和所有内容，并且在不解锁其锁定的内容的情况下决不应该放弃，但也必须考虑在这些情况下发生的情况）：

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)

任何替代解决方案（MySQL 之外）可以推荐吗？我确实也有一个 memcached 实例在运行，但经常被刷新（并且不确定我是否想要 memcachedb 等来实现容错）。

感谢您的帮助...

Answer 1

还可以使用 MySQL 和 MariaDB 的 GET_LOCK（和 RELEASE_LOCK）函数：

• https://dev.mysql.com/doc/refman/5.5/en/miscellaneous -functions.html#function_get-lock（过时的链接）
• https://dev.mysql.com/doc/refman/8.0/en/locking-functions.html#function_get-lock
• https://mariadb.com/kb/en/library/get_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 |
+---------------------------------+

释放锁：

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

---

请注意（引自MariaDB的文档）：

• <块引用>

  名称在服务器范围内被锁定。 如果某个名称已被一个客户端锁定，GET_LOCK() 会阻止另一个客户端对同名锁的任何请求。这允许客户端就给定的锁达成一致name 使用该名称来执行协作咨询锁定。但请注意，它还允许不在合作客户端集合中的客户端无意或有意地锁定名称，从而防止任何合作客户端锁定该名称。减少这种可能性的一种方法是使用特定于数据库或特定于应用程序的锁名称。例如，使用 db_name.str 或 app_name.str 形式的锁名称。

• <块引用>

  使用 GET_LOCK() 获取的锁不会与事务交互。

Answer 2

在这里回答我自己的问题。我们最终（在 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";
?>

为了验证它是否可以从两个不同的控制台运行。时间性能比基于标准文件锁的互斥锁要差一些，但仍然可以接受。