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

问题描述

我想更多地了解数据库交易，我发现ACID经验法则用于编写交易和思考一些问题。

ACID经验法则：

交易必须是：

1. Atomic - 它是一个工作单元，不依赖于以前和
   后的事务。


2. 一致 - 数据提交或回滚，没有
   


3. 隔离 - 没有交易看到当前
   交易的中间结果。
li>

4. 持久 - 如果数据已经提交，即使
   系统崩溃，值仍然存在。

< blockquote>

我想知道它们如何工作，所以我可以更好地理解在编写这样的事务时需要考虑的因素。我想具体的细节将在可用的数据库实现之间变化，但某些规则将始终存在。

1. 数据库处理并发事务，同时仍然支持原子规则？
   
   
   
   
   • 是否有按顺序处理的交易队列？
   
   
   • 如何延长交易？所有其他处理？
   
   


2. 更新表在内存中完成，所以如果在提交之前发生崩溃， ？
   
   
   
   
   • 还是有一些中间表被更新以生存这样的崩溃？
   
   


3. 当事务正在进行时，是否阻止对受影响的表的所有读写访问？
   
   
   
   
   • 或者数据库是否允许写入，但事务会在提交时覆盖所有更改？
   
   

感谢

解决方案

1. 有很多不同的方法，包括事务队列，乐观并发控制等。这其实是一个非常复杂的问题，有关于它的书：

   
   
   

   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. 这取决于数据库中的日志记录级别。如果保存严格的预写日志，那么在系统崩溃的情况下，数据库可以恢复到一致的状态。




3. 并发类型。乐观并发涉及没有锁，但是如果事务完成后db的状态已更改，那么它将被放弃并重新启动。这可以加速dbs，其中碰撞是罕见的。还有不同的锁定级别：行，表，甚至整个数据库。

这些都是非常复杂的问题，我建议购买一本书，或参加系统演讲系列，如果你想能够完全回答他们： - ）

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?

Thanks

解决方案

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 :-)

这篇关于数据库事务 - 它们如何工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！