MySQL独占锁(用于更新)正在锁定整个表[重复]

我有一个MySQL表(使用InnoDB作为存储引擎)来存储用户事务。

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL,
  `correlation_id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `currency` char(3) NOT NULL,
  `time_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `transaction_amount` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `correlation_id_unique` (`correlation_id`), 
  ADD INDEX (`user_id`);

我在多线程环境中工作,希望确保:

• 没有两个线程可以同时为同一用户插入事务
• 如果线程正在为用户插入事务,则其他线程无法读取该用户的事务

我想出了以下解决方案:

1. 当线程想要为用户插入事务时,获取对应于该用户的行的排他锁

   BEGIN;
   
   -- Acquire an exclusive lock on the rows with user_id=1
   SELECT * FROM transactions WHERE user_id = 1 FOR UPDATE;
   
   -- Insert transactions
   ...
   
   COMMIT;
   

2. 当线程想要读取用户余额(通常通过对用户的所有事务求和)时,它首先获取与该用户对应的行的共享锁

   SELECT SUM(transaction_amount) 
   FROM transactions 
   WHERE user_id=1 
   LOCK IN SHARE MODE;
   

但是,独占锁似乎锁定了整个表 不仅是SELECT返回的行。。。对于UPDATE语句。这里有一个例子。

线程1:

mysql> select user_id, transaction_amount from transactions;
+---------+--------------------+
| user_id | transaction_amount |
+---------+--------------------+
| 1       |                 10 |
| 1       |                 -2 |
| 2       |                  5 |
| 2       |                 10 |
+---------+--------------------+
4 rows in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM transactions WHERE user_id = 1 FOR UPDATE;
+----+----------------+---------+----------+---------------------+--------------------+
| id | correlation_id | user_id | currency | time_created        | transaction_amount |
+----+----------------+---------+----------+---------------------+--------------------+
|  1 | 1              | 1       | CHF      | 2018-03-06 09:54:28 |                 10 |
|  2 | 2              | 1       | CHF      | 2018-03-06 09:54:28 |                 -2 |
+----+----------------+---------+----------+---------------------+--------------------+
2 rows in set (0.01 sec)

螺纹2:

-- Retrieve transactions of user 2
mysql> SELECT * FROM transactions WHERE user_id = 2 LOCK IN SHARE MODE;

[[Hangs]]

阅读后 MySQL's documentation ,我本以为这会奏效:

选择。。。锁定共享模式

在读取的任何行上设置共享模式锁。 其他会话可以读取行 ,但在事务提交之前无法修改它们

选择。。。用于更新

对于搜索遇到的索引记录,锁定行和任何关联的索引项,就像为这些行发出UPDATE语句一样。阻止其他事务更新 那些行 ,从执行选择。。。锁定在共享模式下,或从特定事务隔离级别读取数据。

现在,我发现 this topic ,声明在我的情况下 user_id 字段应该有一个索引,它确实有索引。

我觉得这个问题可能是由这个请求引起的 SELECT * FROM transactions WHERE user_id=1 未使用索引:

EXPLAIN SELECT * FROM transactions WHERE user_id=1 FOR UPDATE;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | transactions | NULL       | ALL  | user_id       | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

有什么想法吗?