数据库事务可以防止竞争条件吗? [英] Do database transactions prevent race conditions?

问题描述

我并不完全清楚数据库系统中的事务是做什么的.我知道它们可用于完全回滚更新列表(例如，从一个帐户中扣除资金并将其添加到另一个帐户中)，但仅此而已吗?具体来说，它们可以用来防止竞争条件吗?例如:

It's not entirely clear to me what transactions in database systems do. I know they can be used to rollback a list of updates completely (e.g. deduct money on one account and add it to another), but is that all they do? Specifically, can they be used to prevent race conditions? For example:

// Java/JPA example
em.getTransaction().begin();
User u = em.find(User.class, 123);
u.credits += 10;
em.persist(u); // Note added in 2016: this line is actually not needed
em.getTransaction().commit();

(我知道这可能可以写成单个更新查询，但情况并非总是如此)

(I know this could probably be written as a single update query, but that's not alway the case)

这段代码是否受到竞争条件的保护?

Is this code protected against race conditions?

我最感兴趣的是 MySQL5 + InnoDB，但也欢迎提供一般性的答案.

I'm mostly interested in MySQL5 + InnoDB, but general answers are welcome too.

推荐答案

TL/DR:事务本身不能阻止所有竞争条件.在所有现实世界的数据库实现中，您仍然需要锁定、中止和重试处理或其他保护措施.事务不是您可以添加到查询中的秘密武器它们免受所有并发影响.

TL/DR: Transactions do not inherently prevent all race conditions. You still need locking, abort-and-retry handling, or other protective measures in all real-world database implementations. Transactions are not a secret sauce you can add to your queries to make them safe from all concurrency effects.

您的问题是指中的我>ACID - 隔离.学术上纯粹的想法是事务应该提供完美的隔离，这样结果就好像每个事务都是串行执行的一样.实际上，在真正的 RDBMS 实现中很少出现这种情况.功能因实现而异，并且可以通过使用较弱的隔离级别(如READ COMMITTED)来削弱规则.在实践中您不能假设事务可以防止所有竞争条件，即使在 SERIALIZABLE 隔离下也是如此.

What you're getting at with your question is the I in ACID - isolation. The academically pure idea is that transactions should provide perfect isolation, so that the result is the same as if every transaction executed serially. In reality that's rarely the case in real RDBMS implementations; capabilities vary by implementation, and the rules can be weakened by use of a weaker isolation level like READ COMMITTED. In practice you cannot assume that transactions prevent all race conditions, even at SERIALIZABLE isolation.

一些 RDBMS 的能力比其他的强.例如，PostgreSQL 9.2 和更新版本具有相当好的 SERIALIZABLE 隔离，可以检测大多数(但不是全部)事务之间可能的交互，并中止所有冲突事务，但只有一个冲突事务.因此它可以非常安全地并行运行事务.

Some RDBMSs have stronger abilities than others. For example, PostgreSQL 9.2 and newer have quite good SERIALIZABLE isolation that detects most (but not all) possible interactions between transactions and aborts all but one of the conflicting transactions. So it can run transactions in parallel quite safely.

很少，如果有的话³，系统具有真正完美的SERIALIZABLE隔离，可以防止所有可能的竞争和异常，包括锁升级和锁排序死锁等问题.

Few, if any³, systems have truly perfect SERIALIZABLE isolation that prevents all possible races and anomalies, including issues like lock escalation and lock ordering deadlocks.

即使有很强的隔离，一些系统(如 PostgreSQL)也会中止冲突的事务，而不是让它们等待并串行运行它们.您的应用程序必须记住它在做什么并重新尝试事务.因此，虽然事务阻止了与并发相关的异常被存储到数据库中，但它是以一种对应用程序不透明的方式完成的.

Even with strong isolation some systems (like PostgreSQL) will abort conflicting transactions, rather than making them wait and running them serially. Your app must remember what it was doing and re-try the transaction. So while the transaction has prevented concurrency-related anomalies from being stored to the DB, it's done so in a manner that is not transparent to the application.

可以说，数据库事务的主要目的是提供原子提交.在您提交事务之前，更改不会生效.当您提交时，就其他事务而言，所有更改都会在同一时刻生效.任何事务都不能只看到一些事务所做的更改^1,2.类似地，如果您ROLLBACK，那么任何其他事务都不会看到该事务的任何更改；就好像您的交易从未存在过一样.

Arguably the primary purpose of a database transaction is that it provides for atomic commit. The changes do not take effect until you commit the transaction. When you commit, the changes all take effect at the same instant as far as other transactions are concerned. No transaction can ever see just some of the changes a transaction makes^1,2. Similarly, if you ROLLBACK, then none of the transaction's changes ever get seen by any other transaction; it's as if your transaction never existed.

这是ACID中的A.

另一个是耐久性 - ACID 中的 D.它指定当您提交事务时，它必须真正保存到存储中，以便在出现断电或突然重启等故障时幸免于难.

Another is durability - the D in ACID. It specifies that when you commit a transaction it must truly be saved to storage that will survive a fault like power loss or a sudden reboot.

参见维基百科

与使用锁定和/或高隔离级别相比，Hibernate、EclipseLink 等 ORM 使用 乐观并发控制(通常称为乐观锁定")以克服较弱隔离级别的局限性，同时保持性能.

Rather than using locking and/or high isolation levels, it's common for ORMs like Hibernate, EclipseLink, etc to use optimistic concurrency control (often called "optimistic locking") to overcome the limitations of weaker isolation levels while preserving performance.

这种方法的一个关键特性是它让您可以跨多个事务处理工作，这对于用户数量多且与任何给定用户的交互之间可能有很长时间延迟的系统来说是一个很大的优势.

A key feature of this approach is that it lets you span work across multiple transactions, which is a big plus with systems that have high user counts and may have long delays between interactions with any given user.

除了文本链接，请参阅 PostgreSQL 文档章节关于锁定、隔离和并发.即使您使用不同的 RDBMS，您也会从它解释的概念中学到很多东西.

In addition to the in-text links, see the PostgreSQL documentation chapter on locking, isolation and concurrency. Even if you're using a different RDBMS you'll learn a lot from the concepts it explains.

¹为了简单起见，我在这里忽略了很少实现的READ UNCOMMITTED 隔离级别；它允许脏读.

¹I'm ignoring the rarely implemented READ UNCOMMITTED isolation level here for simplicity; it permits dirty reads.

²正如@meriton 指出的，推论不一定正确.幻读发生在SERIALIZABLE以下的任何内容中.正在进行的事务的一部分没有看到一些更改(通过尚未提交的事务)，然后正在进行的事务的下一部分确实看到了其他事务时的更改提交.

²As @meriton points out, the corollary isn't necessarily true. Phantom reads occur in anything below SERIALIZABLE. One part of an in-progress transaction doesn't see some changes (by a not-yet-committed transaction), then the next part of the in-progress transaction does see the changes when the other transaction commits.

³ 好吧，IIRC SQLite2 是通过在尝试写入时锁定整个数据库来实现的，但这并不是我所说的并发问题的理想解决方案.

³ Well, IIRC SQLite2 does by virtue of locking the whole database when a write is attempted, but that's not what I'd call an ideal solution to concurrency issues.

这篇关于数据库事务可以防止竞争条件吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！