数据库事务是否防止竞争条件？ [英] Do database transactions prevent race conditions?

问题描述

我不完全清楚数据库系统中的事务。我知道他们可以用来完全回滚更新列表（例如扣除一个帐户上的钱，并将其添加到另一个帐户），但这是他们做的吗？具体来说，它们可以用于防止种族条件吗？例如：

  // Java / JPA示例
 em.getTransaction 
用户u = em.find（User.class，123）; 
 u.credits + = 10; 
 em.persist（u）; //注意在2016年添加：这行实际上不需要
 em.getTransaction（）。commit（）; 
  

（我知道这可能写成一个更新查询，

这个代码是否对竞争条件有保护？

我最感兴趣的是MySQL5 + InnoDB，

解决方案

TL / DR：交易不会固有地阻止所有种族条件。在所有真实数据库实现中，您仍然需要锁定，中止和重试处理或其他保护措施。 交易不是可以添加到您的查询中的秘密措施他们可以避免所有并发效应。

隔离

问题是 ACID 中的我 隔离。学术上纯粹的想法是交易应该提供完美的隔离，所以结果是相同的，如果每个交易顺序执行。实际上，在真正的RDBMS实现中很少出现这种情况;能力因实现而异，并且可以通过使用较弱的隔离级别（例如 READ COMMITTED ）来削弱规则。在实践中，即使在 SERIALIZABLE 隔离下，您也不能假设交易阻止所有竞争条件。

<一些RDBMS具有比其他RDBMS更强的能力。例如，PostgreSQL 9.2和更新版本有相当不错的 SERIALIZABLE 隔离，可以检测事务之间的最多（但不是全部）可能的交互，但是其中一个冲突的交易。

很少，如果任何 ³ ，系统有真正完美的 SERIALIZABLE 隔离，以防止所有可能的种族和异常，包括锁升级和锁定排序死锁等问题。

即使有些隔离系统（如PostgreSQL）将中止冲突的事务，而不是让它们连续等待和运行它们。您的应用程序必须记住它在做什么，并重新尝试该事务。因此，虽然事务已经防止了并发相关的异常存储到DB，但是这样做是对应用程序不透明的。

原子性

可以说，数据库事务的主要目的是提供 atomic commit 。在提交事务之前，更改才会生效。当提交时，所有更改对于其他事务都将在同一时刻生效。没有任何交易只能查看交易做出 ^1,2 的一些变更。同样，如果 ROLLBACK ，任何事务的更改都不会被任何其他事务看到;

这是 ACID 中的 A 。

b

耐用性

另一种是耐用性 - ACID 中的 D 。它指定当您提交事务时，它必须真正保存到存储，以便在诸如断电或突然重启的故障中生存。

一致性：

请参阅维基百科

乐观并发控制

除了使用锁定和/或高隔离级别之外，ORM（如Hibernate，EclipseLink等）使用 乐观并发控制 （通常称为乐观锁定）克服在保持性能的同时保持较弱的隔离级别的局限性。

这种方法的一个关键特性是它允许跨越多个事务的工作，这是一个很大的优势，

参考

除了文字链接，请参阅有关锁定，隔离和并发的PostgreSQL文档一章。即使你使用不同的RDBMS，你也会从它解释的概念中学到很多东西。

---

¹ 我忽略了很少实现的 READ UNCOMMITTED 隔离级别;它允许脏读。

² 正如@meriton指出的，推论不一定是真的。 幻影读取发生在 SERIALIZABLE 。正在进行的事务的一部分没有看到一些改变（通过尚未提交的事务），则正在进行的事务的下一部分 提交。

³ 好吧，IIRC SQLite2通过锁定整个数据库时尝试写入，但这不是我调用并发问题的理想解决方案。

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?

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

解决方案

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.

Isolation

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.

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.

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.

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.

Atomicity

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.

That's the A in ACID.

Durability

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.

Consistency:

See wikipedia

Optimistic concurrency control

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.

References

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.

---

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

²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.

³ 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屋！