MySQL的锁定和并发 [英] Locking and concurrency with MySQL

问题描述

我目前正在对所有表使用带有InnoDB存储引擎的Mysql.

I'm currently using Mysql with InnoDB storage engine for all tables.

所以，我想知道这是否是一个真正的问题，是否有解决方案.

So, I'm wondering if this is a real problem and if there's a solution for it.

例如，我将使用数据库事务向用户收费:1.检查他的余额2.减去他的余额3.将此余额记入某处4.提交

For example, I will charge a user using a database transaction: 1. check his balance 2. subtract his balance 3. credit this balance somewhere 4. commit

如果更新发生在#1之后和2&之前，将会发生什么?3.如果用户提款或购买其他东西，导致其余额为零.这将导致失去平衡.据我了解，步骤1仅会导致共享锁，而不会阻止写入等.

What would happen if an update happens just after #1 and before 2 & 3. If the user withdraws or purchases something else that results his balance to be zero. This would have led to to lose that balance. From what I understand, step #1 would only cause a shared lock and would not block writes, etc.

有一个通用的解决方案吗?

Is there a common solution for this?

推荐答案

您的标签表明您了解答案是什么-锁定.关系数据库(通常)实现事务的ACID属性，以确保数据的一致性.实际上，出于性能原因，有时会放宽这些限制，但是大多数数据库都提供了实现此目标的一些方法.

Your tags suggest that you understand what the answer is -- locking. Relational databases (generally) implement the ACID properties of transactions, which ensure consistency of data. In practice, these are sometimes relaxed for performance reasons, but most databases offer some method to achieve this goal.

在MySQL中，锁定机制取决于基础存储引擎.InnoDB提供了几个选项，这些选项在文档中.

In MySQL, the locking mechanisms depend on the underlying storage engine. InnoDB offers several options, which are described in the documentation.

要获得这些锁定，您基本上有两个带有 SELECT 的语法选项:

To achieve these locks, you basically have two syntactic options with a SELECT:

select . . . for update
select . . . lock in share mode

请注意，这些语句应在显式事务中使用.

Note that these statements should be used in an explicit transaction.

这篇关于MySQL的锁定和并发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！