将整个应用程序封装在一个数据库事务中 [英] Encapsulate entire application in a database transaction

问题描述

什么是理论&在数据库事务中运行整个 php 应用程序的实际缺点，以确保脚本中引起的任何意外错误或异常会恢复数据库中所做的每个更改，从而避免由于未完成的脚本而导致某些表已更新的状态还有一些不是?这显然不是也不会被推荐为好的做法，但我想详细了解原因.

What are the theoretical & practical draw backs of having an entire php application running inside a database transaction, to ensure that any unexpected error or exception caused in the script reverts every change done in the database, thus avoiding a state where due to an unfinished script some tables have been updated and some others not? This obviously doesn't seem to be nor would ever be recommended as good practice, but I would like to understand in detail why.

推荐答案

数据库事务确保多个操作遵守 ACID 属性:

Database transactions ensure that multiple operations obey the ACID properties:

• 原子 - 这是您在问题中提到的属性，即交易中的每个操作要么成功，要么全部失败；

• Atomic - this is the property to which you allude in your question, whereby either every operation in the transaction succeeds or else they all fail;

Consistent - 此属性确保数据库状态始终有效；

Consistent - this property ensures that the database state is valid at all times;

Isolated - 此属性确保并发事务(即来自不同连接)的发生就像它们是串行执行的一样；和

Isolated - this property ensures that concurrent transactions (i.e. from different connections) occur as though they were executed serially; and

持久 - 一旦提交，更改是永久性的.

Durable - once commited, changes are permanent.

为了确保隔离的第三个属性，像MySQL这样的RDBMS系统执行锁定以确保，例如，一个事务不会写入记录而另一个正在从中读取(当记录被锁定时，其他事务必须等待锁定被释放才能继续).

In order to ensure the third property of Isolation, RDBMS systems like MySQL perform locking to ensure that, for example, one transaction does not write to a record whilst another is reading from it (when a record is locked, other transactions must wait for the lock to be released before they are able to proceed).

如果你的事务过长，会导致过度锁定和过度等待.它甚至可能导致不必要的死锁，即事务正在等待彼此持有的锁，并且在至少回滚一个锁之前无法继续.

If your transactions are unnecessarily long, they will lead to excessive locking and excessive waiting. It could even lead to unnecessary deadlocks, whereby transactions are waiting on locks held by eachother and cannot proceed until at least one is rolled back.

出于性能(和持久性)的原因，因此您应该努力将事务保持在绝对必要的范围内，以保持一致性.

For reasons of performance (and durability), you should therefore strive to keep transactions as small as is absolutely necessary to maintain consistency.

这篇关于将整个应用程序封装在一个数据库事务中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！