数据库事务的酸性

Data integrity in a database is important when it comes to transactions. The data must be verified correct and should persist after being stored. When designing a database it is important to take the ACID methodology into consideration. The acronym, coined by Andreas Reuter and Theo Härder, stands for 4 properties developers should follow: Atomicity, Consistency, Isolation and Durability. This allows a way to validate a transaction from the time of data input to the final output, which is stored in a database. This meets the requirements for error handling in the event of unforeseen circumstances (e.g. power failures, hardware malfunction, etc.) and bugs in code that could affect the results of a transaction.

当涉及事务时，数据库中的数据完整性很重要。 数据必须验证正确，并且在存储后应保持不变。 设计数据库时，重要的是要考虑ACID方法。 该首字母缩写词由Andreas Reuter和TheoHärder创造，代表开发商应遵循的4种特性： 原子性，一致性，隔离性和耐久性 。 这提供了一种方法来验证从数据输入到最终输出(存储在数据库中)之间的事务。 这满足了在无法预料的情况下(例如电源故障，硬件故障等)以及可能影响交易结果的代码错误的错误处理要求。

原子性 (Atomicity)

Multiple statements make up a transaction. These statements can be broken down into the parts of the transaction like the input, data processing, checking, storage and output. In order for a transaction to complete, all the steps in the process must be completed. It is like marking each step in the process with a checkmark if this were a list, indicating it has been completed. If a step in the process fails, then the transaction is considered a failure and should be rolled back or reset.

多个语句构成一个事务。 这些语句可以细分为事务的各个部分，例如输入，数据处理，检查，存储和输出。 为了完成交易，必须完成流程中的所有步骤。 如果这是一个列表，则表明该过程中的每个步骤都带有选中标记，这表明它已完成。 如果流程中的某个步骤失败，则该事务被视为失败，应回滚或重置。

Atomicity makes sure that before a transaction is committed, it must have completed successfully. Otherwise it must fail completely so that nothing in the process is left incomplete. Take for example a transaction that requires the input of the number of items a person wants to order. If that process completes, but fails to compute the amount, then the transaction should not complete or else the person will not be charged for the items they ordered. There must be steps to verify and validate each step in the process.

原子性确保在提交事务之前，它必须已成功完成。 否则，它必须完全失败，以使该过程中的所有内容都保持不完整。 例如，某项交易需要输入一个人要订购的商品数量。 如果该过程完成，但无法计算金额，则该交易应不会完成，否则该人将不会为他们订购的物品付费。 必须有步骤来验证和验证过程中的每个步骤。

Assume 3 Processes A, B and C. For a transaction t to complete, all processes must be validated and have completed.Therefore, it must follow an AND condition:t = ABC = 1The transaction cannot be an OR condition:t = A+B+C = 0

The reason a transaction must be validated is in order to keep the database in a consistent state. Any partial saving of data can lead to inconsistencies that can severely affect the accuracy of information. This also affects data integrity, so that is very important in enterprise business operations which relies on data accuracy for accounting purposes used for financial records and reporting.

必须验证事务的原因是为了使数据库保持一致状态。 任何部分保存数据都可能导致不一致，从而严重影响信息的准确性。 这也会影响数据完整性，因此在企业业务运营中非常重要，企业业务运营依赖于数据准确性来实现财务记录和报告的会计目的。

一致性 (Consistency)

A transaction must be one completed operation in order for data to be consistent. Validation rules must be put in place to ensure data consistency. If there is no consistency during the validation process, then the entire transaction should be nullified and reset. This allows the transaction to return to known state which allows initialization back to the first step in the process.

事务必须是一项已完成的操作，才能使数据保持一致。 验证规则必须到位，以确保数据一致性。 如果在验证过程中不一致，则应取消并重置整个事务。 这使事务返回到已知状态，从而允许初始化回到流程的第一步。

Take for example a database field called SSNUMBER(10). It can only accept a 10 digit numeric value (0 to 9), which must be enforced by a routine that rejects anything lower than 10 digits and cannot be greater than 10 digits. When used in an online application form, a routine must be included to check the validity of the data input. It will be very problematic down the road if field accepted more than 10 digits and allowed alphanumeric characters when it should only be numeric.

以一个名为SSNUMBER(10)的数据库字段为例。 它只能接受10位数字的值(0到9)，该值必须由拒绝低于10位且不能大于10位的任何东西的例程强制执行。 当以在线申请表的形式使用时，必须包含一个例程以检查数据输入的有效性。 如果字段接受的数字必须超过10位并且允许使用字母数字字符，那么这将是非常麻烦的。

Let A + B = 1000, where A and B are any integer that when added with each other results in 1000.A or B cannot be > 1000, but can be = to 1000 or 0.e.g. 
A = 0, B = 1000
A + B = 1000 (Accept)A = 1000, B = 1000
A + B = 2000 (Reject)I can create a simple checker to make sure that the input data for A or B can only be 0 to 1000, and when added together it cannot be > 1000.

When updating databases, routines called validation rules or check rules are implemented to make sure that the data is correct based on the conditions in the program. Like in the example of A + B = 1000, when committing data in a database it must follow the same property. If the results returned A + B = 2000, the transaction must be canceled and the system must be rolled back to a pre-transaction state.

更新数据库时，将执行称为验证规则或检查规则的例程，以根据程序中的条件确保数据正确。 就像在A + B = 1000的示例中一样，在数据库中提交数据时，它必须遵循相同的属性。 如果结果返回A + B = 2000，则必须取消该事务，并且必须将系统回滚到事务前的状态。

隔离 (Isolation)

In order to prevent conflicts in the database which could result in inconsistencies, there must be process isolation. This ensures that process t1 and t2 don’t attempt to execute at the same time and write data to the database. This is the purpose for queuing a process in an execution pipeline, which is performed in hardware by the CPU. However, in the application or program there must also be control method to make sure that processes do not interfere with one another.

为了防止数据库中可能导致不一致的冲突，必须进行进程隔离。 这样可以确保进程t1和t2不会尝试同时执行并将数据写入数据库。 这是为了使执行管道中的进程排队，该过程由CPU在硬件中执行。 但是，在应用程序或程序中也必须有一种控制方法，以确保进程之间不会相互干扰。

It may not be apparent, but the need for controlling transactions can affect business operations in finance and accounting applications. Let us say that we have a transaction t1 that adds $500 to Bob’s account from Alice’s account. Then there is another transaction t2 in which Bob transfers $1000 to Carol’s account. The following example shows the steps that need to be processed.

这可能并不明显，但是控制交易的需求可能会影响财务和会计应用程序中的业务运营。 假设我们有一个交易t1，从alice的帐户向Bob的帐户增加了$ 500。 然后还有另一笔交易t2，鲍勃将$ 1000美元转帐到Carol的帐户中。 以下示例显示了需要处理的步骤。

t1:
Subtract $500 from Alice's account 
Add $500 to Bob's accountt2:
Subtract $1000 from Bob's account
Add $1000 to Carol's account

If both transactions attempt to run at the same time, it can lead to an error. That is because if one attempt to overwrite each other, it can affect the account balances of the users. If in t1 Bob only has $800 in his account prior to the amount he received from Alice, and t2 was processed first, then Bob would not be able to send $1000 to Carol because he does not have enough funds (-$200). If both transactions arrived at the same time, it is like subtracting and adding from Bob’s account at the same time.

如果两个事务尝试同时运行，则可能导致错误。 这是因为如果尝试彼此覆盖，则可能会影响用户的帐户余额。 如果在t1中Bob的帐户中只有比从Alice收到的金额多800美元，并且先处理了t2，则Bob将无法向Carol发送1000美元，因为他没有足够的资金(-200美元)。 如果两个交易是同时到达的，那就像是同时从Bob的帐户中减去和增加。

In our example, Bob only has a balance b = $800 in his account prior to t1 and t2. When you subtract and add at the same time it results in the following:

在我们的示例中，Bob在t1和t2之前的帐户中只有余额b = $ 800。 当您同时减去和相加时，结果如下：

t1:
b (balance) = $800
a (amount received) = $500t1 = b + a = $1300t2:
b (balance) = $800
a (amount subtracted) = $1000t2 = b - a = -$200

According to t1, Bob’s balance is $1300. In t2 the balance is -$200, meaning Bob did not have enough funds to transfer $1000 to Carol’s account. How do you reconcile two different balances that result from the transactions running at the same time? Bob’s balance will not be settled automatically when this occurs, so this is what leads to bigger problems.

根据t1，鲍勃的余额为1300美元。 在t2中，余额为-200美元，这意味着鲍勃没有足够的资金将1000美元转入Carol的帐户。 您如何调节同时运行的交易产生的两个不同余额？ 发生这种情况时，Bob的余额将不会自动清算，因此这会导致更大的问题。

This is why the transactions must be isolated properly based on timestamps and record locks. Obviously Bob will not be able to transfer $1000 prior to receiving $500 in his account from Alice. The $500 added to Bob’s current balance will be enough to allow him to transfer $1000 to Carol. Therefore, t1 must be processed before t2. Isolating the two transactions based on the proper order by timestamp and locking each record to prevent simultaneous updates effectively maintains consistency in data.

这就是为什么必须基于时间戳和记录锁正确隔离事务的原因。 显然，鲍勃在从爱丽丝的帐户中获得500美元之前，将无法转移1000美元。 Bob的当前余额增加的$ 500，足以使他将$ 1000转移给Carol。 因此，必须在t2之前处理t1。 根据时间戳正确的顺序隔离两个事务，并锁定每个记录以防止同时更新，从而有效地保持了数据的一致性。

耐用性 (Durability)

Persistent data storage and backup tests a database system’s effectivity. This is part of the durability property of databases. Database management systems no longer reside in silos of information that reside in office server rooms. They are now on the network, either in local data centers or on the cloud. Making sure that the data is properly secured and managed by an administrator is the most important part of database administration.

持久的数据存储和备份测试了数据库系统的有效性。 这是数据库持久性属性的一部分。 数据库管理系统不再驻留在办公室服务器机房中的信息孤岛中。 它们现在位于本地数据中心或云中的网络上。 确保数据由管理员正确保护和管理是数据库管理的最重要部分。

Administrators must be able to commit data from buffer to persistent storage and perform proper backup schedules. This is also a part of business continuity planning procedures in the event of a system failure. When the server crashes or when the network if offline, the data must be backed up so it can be recovered later or accessed from another system. Servers should also have backup UPS (Uninterruptible Power Supply) to allow administrators time to gracefully shutdown a system in the event of a power failure to prevent data corruption caused by servers suddenly losing power and not committing data properly.

管理员必须能够将数据从缓冲区提交到持久性存储并执行正确的备份计划。 如果发生系统故障，这也是业务连续性计划过程的一部分。 当服务器崩溃或网络脱机时，必须备份数据，以便以后可以恢复或从另一个系统访问。 服务器还应具有备用UPS(不间断电源) ，以便管理员在电源出现故障时有时间正常关闭系统，以防止由于服务器突然断电且未正确提交数据而导致数据损坏。

Information is an important part of a business, and when it is lost it can be gone forever. Backup systems could malfunction, network connections could fail and host of other problems that administrators have to deal with. Transactions once recorded should persist in a non-volatile memory storage device with a backup (e.g. 3–2–1 Rule For Data Backup). The cost of lost data is much more than recovering data. An example here is losing a database of thousands of customers due to no backups. With a backup system the data can be recovered in a few hours at cost to an administrator or contractor. Without a backup system, the company will need to gather customer information again to create a new database which takes more time and higher costs. Modern databases can be recovered today using transaction logs, but a backup is a better guarantee for any type of data recovery.

信息是业务的重要组成部分，一旦丢失，信息便会永远消失。 备份系统可能会发生故障，网络连接可能会失败，并且还会出现许多管理员必须解决的其他问题。 一旦记录了交易，就应将其保存在带有备份的非易失性存储设备中(例如3–2-1数据备份规则 )。 丢失数据的代价远远超过恢复数据的代价。 这里的一个示例是由于没有备份而丢失了成千上万个客户的数据库。 使用备份系统，可以在几个小时内恢复数据，这需要管理员或承包商付费。 如果没有备份系统，公司将需要再次收集客户信息以创建新的数据库，这将花费更多的时间和更高的成本。 今天，可以使用事务日志来恢复现代数据库，但是备份是对任何类型的数据恢复的更好保证。

实作 (Implementation)

Here is a sample code (from Microsoft) of a SQL transaction with comments that describe how ACID properties are applied. This example shows how to roll back only the modifications made by a stored procedure if an active transaction is started before the stored procedure is executed.

这是SQL事务的示例代码(来自Microsoft )，带有注释，描述了如何应用ACID属性。 本示例说明了如果在执行存储过程之前启动了活动事务，则仅回滚存储过程所做的修改。

USE AdventureWorks2012;  
GO  
IF EXISTS (SELECT name FROM sys.objects  
           WHERE name = N'SaveTranExample')  
    DROP PROCEDURE SaveTranExample;  
GO  
CREATE PROCEDURE SaveTranExample  
    @InputCandidateID INT  
AS  -- Detect whether the procedure was called  
    -- from an active transaction and save  
    -- that for later use.  
    -- In the procedure, @TranCounter = 0  
    -- means there was no active transaction  
    -- and the procedure started one.  
    -- @TranCounter > 0 means an active  
    -- transaction was started before the   
    -- procedure was called.  
    DECLARE @TranCounter INT;  
    SET @TranCounter = @@TRANCOUNT;  
    IF @TranCounter > 0  -- Procedure called when there is  
        -- an active transaction.  
        -- Create a savepoint to be able  
        -- to roll back only the work done  
        -- in the procedure if there is an  
        -- error.  
        SAVE TRANSACTION ProcedureSave;  
    ELSE  -- Procedure must start its own  
        -- transaction.  
        BEGIN TRANSACTION;   -- Modify database.  
    BEGIN TRY  
        DELETE HumanResources.JobCandidate  
            WHERE JobCandidateID = @InputCandidateID;  -- Get here if no errors; must commit  
        -- any transaction started in the  
        -- procedure, but not commit a transaction  
        -- started before the transaction was called.  
        IF @TranCounter = 0  -- @TranCounter = 0 means no transaction was  
            -- started before the procedure was called.  
            -- The procedure must commit the transaction  
            -- it started.  
            COMMIT TRANSACTION;  
    END TRY  
    BEGIN CATCH  -- An error occurred; must determine  
        -- which type of rollback will roll  
        -- back only the work done in the  
        -- procedure.  
        IF @TranCounter = 0  -- Transaction started in procedure.  
            -- Roll back complete transaction.  
            ROLLBACK TRANSACTION;  
        ELSE  -- Transaction started before procedure  
            -- called, do not roll back modifications  
            -- made before the procedure was called.  
            IF XACT_STATE() <> -1  -- If the transaction is still valid, just  
                -- roll back to the savepoint set at the  
                -- start of the stored procedure.  
                ROLLBACK TRANSACTION ProcedureSave;  -- If the transaction is uncommitable, a  
                -- rollback to the savepoint is not allowed  
                -- because the savepoint rollback writes to  
                -- the log. Just return to the caller, which  
                -- should roll back the outer transaction.  -- After the appropriate rollback, echo error  
        -- information to the caller.  
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
    END CATCH  
GO

概要 (Synopsis)

Database management systems require data consistency for presenting accurate information in reports. This is important in today’s world of online transactions that use distributed databases. A system like ACID helps to provide a way to validate data for integrity in order to prevent inconsistencies in transactions.

数据库管理系统需要数据一致性，才能在报告中显示准确的信息。 在当今使用分布式数据库的在线交易世界中，这一点很重要。 诸如ACID之类的系统有助于提供一种验证数据完整性的方法，以防止交易中的不一致。

翻译自: https://medium.com/0xcode/the-acid-property-for-database-transactions-573643f5b7de