帮忙解决一个锁的问题

现在有个accounts表,DDL为

CREATE TABLE `accounts`
(
    `account_id`     int(11) NOT NULL AUTO_INCREMENT,
    `account_number` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `mt4_server_id`  int(11)                                DEFAULT NULL,
    `balance`        decimal(10, 2)                         DEFAULT NULL,
    `update_date`    date                                   DEFAULT NULL,
    PRIMARY KEY (`account_id`),
    KEY `idx_account_number_mt4_server_id_account_id` (`account_number`, `mt4_server_id`, `account_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 6
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

然后对下面的两个update语句进行测试

// update1
UPDATE accounts SET balance = 0 WHERE account_number NOT IN ('115') AND mt4_server_id = 14 ORDER BY account_id;
// update2
UPDATE accounts SET balance = 0 WHERE account_number IN ('115') order by account_id;

测试1 事务隔离级别为 REPEATABLE READ:

  • update1执行期间,update2会遇到锁!

测试2 事务隔离级别为 READ COMMITTED:

  • update1执行期间,update2能够执行成功,因为115所在行没有被上锁。当然update2的115换成其他114或者其他会呗上锁。
  • 进一步测试将idx_account_number_mt4_server_id_account_id的index删除掉,再次执行,update2执行失败,因为115行也被上锁了

我的问题是

当前生产环境的mysql隔离级别是REPEATABLE READ,在不更改隔离级别的前提下。是否能够实现以下需求。

  • update1更新期间,udpate2也能被成功执行
    也就是说update1执行的时候不要将115的所在行上锁!进而成功执行update2操作!如果可能那么我该如何实现,如果不可能那就算了吧!

问题很简单,有兴趣的朋友也可以在本地试一试!

最佳答案

该问题已解决! 根据需要调整index。使用explain语句调整。尽量只锁定更新的行。

1周前 评论
讨论数量: 7

我觉得你需要检查一下你的 MySQL 版本。

我查找到的资料:www.cnblogs.com/ykwang/p/14781104....

2周前 评论
daxiaobuzhaodiao (楼主) 2周前

用 explan 看下用了什么索引

我觉得是触发了区间锁。

可以尝试使用


// update1
UPDATE accounts SET balance = 0 
WHERE account_id in (select account_id from accounts WHERE account_number NOT IN ('115') AND mt4_server_id = 14 ) 
ORDER BY account_id;
Copy
// update2
UPDATE accounts SET balance = 0 
WHERE account_id in (select account_id from accounts WHERE account_number IN ('115') )
order by account_id;

所有数据都建议用 id 来更新

2周前 评论
daxiaobuzhaodiao (楼主) 2周前

你这就是我之前写的一篇文章deadlock, 解除死锁的关键就在于不能并发影响同一行的数据 RC 虽然能缓解,但是还是存在数据风险

1周前 评论
daxiaobuzhaodiao (楼主) 1周前

该问题已解决! 根据需要调整index。使用explain语句调整。尽量只锁定更新的行。

1周前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!