MySQL 独占锁(FOR UPDATE)锁定整个表-6ren

MySQL 独占锁(FOR UPDATE)锁定整个表

转载作者：行者123 更新时间：2023-11-29 02:42:14

<分区>

我有一个 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`);

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

任何两个线程都不能同时为同一个用户插入事务
如果一个线程正在为一个用户插入交易，则没有其他线程可以从该用户读取交易

我想出了以下解决方案:

当线程要为用户插入事务时，获取该用户对应行的独占锁

BEGIN;

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

-- Insert transactions
...

COMMIT;

当线程想要读取用户余额时(通常是通过对用户的所有交易求和)，它首先获取与该用户对应的行的共享锁
```
SELECT SUM(transaction_amount) 
FROM transactions 
WHERE user_id=1 
LOCK IN SHARE MODE;
```

但是，排他锁似乎锁定了整个表，而不仅仅是 SELECT...FOR 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 ，我希望这会起作用:

SELECT ... LOCK IN SHARE MODE

Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits

SELECT ... FOR UPDATE

For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels.

现在，我找到了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)

有什么想法吗？