数据库事务--它们是如何工作的?[英] Database transactions - How do they work?

本文是小编为大家收集整理的关于数据库事务--它们是如何工作的?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在尝试了解有关数据库交易的更多信息,我发现了写交易和想到一些问题的经验法则.

经验酸规则:

交易必须是:

  1. 原子 - 它是一个工作单位,不依赖于先前的 遵循交易.
  2. 一致 - 数据要么是投入或回滚,否 "中间"的情况有东西 已经更新了,没有.
  3. 孤立 - 没有交易看到电流的中间结果 交易.
  4. 耐用 - 即使数据已投入,值也持续存在 系统立即崩溃.

我想知道它们如何在引擎盖下工作,因此我可以更好地理解编写此类交易时需要考虑的因素.我想具体的细节在可用的数据库实现之间会有所不同,但是某些规则将始终存在.

  1. 数据库如何处理并发交易,而仍然支持原子规则?
    • 是否有按顺序处理的交易队列?
    • 如何处理所有其他人的冗长交易?
  2. 是否对内存中完成的表进行更新,因此,如果提交提交之前确实发生了崩溃,则数据库没有更改?
    • 或是否有一些中间表可以更新以生存这种崩溃?
  3. 尽管正在进行交易,但是否全部读取并写入对受影响表的访问?
    • 或数据库允许写入,但交易将覆盖所有变更?

推荐答案

  1. 有很多不同的方法,包括交易排队,乐观的并发控制等.这实际上是一个非常复杂的问题,有一些书面书籍:

    http://www.amazon.co.uk/databases-transaction-processing-processing-application-eriented-eented-eented-apphack/dp/0201708728/ref=sr_1_1_1_ie=utf = = upetf = uptf = utiff = utiff8 3

  2. 这取决于数据库中的登录级别.如果保留严格的写入日志,则在系统崩溃的情况下,数据库可以恢复到一致的状态.

  3. 这取决于并发的类型.乐观的并发不涉及不锁,但是如果交易完成后,数据库的状态已更改,则放弃并重新启动.这可以加快碰撞很少见的DB.锁定的级别也不同:行,表,甚至整个DB.

这些是非常复杂的问题,如果您想能够完全回答这些书,我建议购买书或参加并发系统讲座系列: - )

其他推荐答案

您的定义上有一些挑剔:

原子 - 它是一个工作单位,不依赖于先前和之后的交易.

更正确的原子定义不会提及任何"以前或以下"交易.原子性是单一交易本身的属性,即在最终倒计时中,其所有动作都持续存在,或者根本没有.换句话说,"只有一半的交易"可以持续存在.

但是,这个概念是由嵌套交易,保存点以及用户要求明确回滚的能力等概念模糊的.从某种意义上说,这些确实允许"仅是交易的一半",这是按照明确的用户的请求而坚持的.

一致 - 数据要么是投入或回滚,没有"中间"的情况,没有更新的东西,而且没有任何东西.

这种解释是完全错误的.一致意味着事务处理器(在这种情况下为DBMS引擎)不能以违反任何已声明的约束(交易处理器)所意识到的声明的约束状态离开系统(数据库).参见,例如,"数据库系统简介",chpt 16.

隔离 - 不交易看到当前交易的中间结果.

nitpicking:除了当前以外,没有交易看到中间状态(状态,不是真正的结果).请注意,交易处理引擎的"隔离水平"通常定义侵犯I属性的程度!

耐用 - 即使系统崩溃后,数据也持续存在.

.

,但由于嵌套交易的可能性,这种属性也有些模糊.即使内部交易已承诺并完成,包含的交易仍然可以撤消,该交易自身完全回滚.

其他推荐答案

实际详细信息可能会在某种程度上取决于哪个DB服务器,但是本文可能对您感兴趣:事务处理备忘单

本文地址:https://www.itbaoku.cn/post/597536.html

问题描述

I'm trying to learn more about database transactions, I found the ACID rule of thumb for writing transactions and thought of a few questions.

The ACID rule of thumb:

A transaction must be:

  1. Atomic - it is one unit of work and does not dependent on previous and following transactions.
  2. Consistent - data is either committed or roll back, no “in-between” case where something has been updated and something hasn’t.
  3. Isolated - no transaction sees the intermediate results of the current transaction.
  4. Durable - the values persist if the data had been committed even if the system crashes right after.

I was wondering how they work under the hood, so I can better understand the factors that need to be considered when writing such a transaction. I guess the specific details will vary between the database implementations that are avaliable, but certain rules will always be in place.

  1. How does the database handle concurrent transactions whilst still supporting the Atomic rule?
    • Is there a queue of transactions that is processed in order?
    • How is a lengthy transaction that is holding up all others handled?
  2. Are updates to tables done in memory so if a crash does occur before commit, there is no alteration to the database?
    • Or are there some intermediate tables that are updated to survive such a crash?
  3. Whilst a transaction is in progress, is all read and write access to the affected tables prevented?
    • Or would the database allow writes but the transaction would overwrite all changes upon commit?

推荐答案

  1. There are many different ways, including transaction queueing, optimistic concurrency control etc. This is actually a very complex question, there are books written about it:

    http://www.amazon.co.uk/Databases-Transaction-Processing-Application-Oriented-Approach/dp/0201708728/ref=sr_1_3?ie=UTF8&s=books&qid=1281609705&sr=8-3

  2. It depends on the level of logging in the database. If strict write-ahead logs are kept then in the case of a system crash, the database can be wound back to a consistent state.

  3. It depends on the type of concurrency. Optimistic concurrency involves no locks, but if the state of the db has changed once the transaction has finished, it is abandoned and restarted. This can speed up dbs where collisions are rare. There are also different levels of locking: row,table, or even the entire db.

These are very complex questions, I'd advise buying a book, or attending a concurrent systems lecture series if you want to be able to fully answer them :-)

其他推荐答案

A few nitpickings on your definitions:

Atomic - it is one unit of work and does not dependent on previous and following transactions.

A more correct definition of atomicity would not mention any "previous or following" transactions. Atomicity is a property of a single transaction taken by itself, namely that in the final countdown, either all of its actions persist, or none at all. In other words, it shall not be the case that "only half a transaction" is allowed to persist.

The concept is, however, blurred by concepts such as nested transactions, savepoints, and the ability for the user to request explicit rollbacks up to a taken savepoint. These do allow, in a certain sense, that "only half the actions of a transaction" persist, allbeit at the explicit user's request.

Consistent - data is either committed or roll back, no “in-between” case where something has been updated and something hasn’t.

This interpretation is totally wrong. Consistent means that the transaction processor (in this case, a DBMS engine) cannot leave the system (the database) in a state of violation of any declared constraint that it (the transaction processor) is aware of. See, for example, "Introduction to database systems", Chpt 16.

Isolated - no transaction sees the intermediate results of the current transaction.

Nitpicking : no transaction other than the current is allowed to see intermediate states (states, not really results). Note furtermore that the "Isolation levels" of transaction processing engines typically define the degree to which the I property can be violated !

Durable - the values persist if the data had been committed even if the system crashes right after.

But this property too is blurred a bit by the possibility of nested transactions. Even if an inner transaction has committed and completed, the containing transaction can still undo that commit by itself rolling back completely.

其他推荐答案

The actual details would probably depend somewhat on which DB server it is, but this article might be of interest to you: Transaction Processing Cheat Sheet