个人中心
gpt4 book ai didi

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

转载 作者：行者123 更新时间：2023-11-29 02:42:14 25 4
gpt4 key购买 nike

<分区>

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

有什么想法吗？

25 4 0
文章推荐： php - 在查询数据库时嵌套 where 子句时出现问题 - Laravel
文章推荐： java - Android，接受用户的值并在下一个 Activity 中显示它们
文章推荐： javascript - 无法检测到 Chrome Android 中的网页滚动结束
文章推荐： php - A non well-formed numeric value 遇到 laravel time calculate
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com