如何实现数据库的ACID模型 [英] How to implement the ACID model for a database

问题描述

我如何实现我的数据库的ACID属性 -
A-原子性，C-一致性，I-隔离，D-耐久性。
正在使用的数据库系统 - MySql。

How do i go about achieving the ACID properties for my datbase - A- Atomicity, C- Consistency, I- Isolation, D- Durability. Database system being used - MySql.

推荐答案

Well ACID不是一个可以实现的模型，

Well ACID is not a model that you can implement, but rather a set of rules that a database server must conform to in order to be able to handle transactions in a safe way.

MySQL不是一个符合ACID标准的数据库服务器，而是一个设计符合ACID的数据库服务器。如果您对表使用 InnoDB 存储引擎（或通过将 default-storage-engine 选项设置为 InnoDB （请参阅 default-storage-engine option ）），您将能够对您的数据库执行事务安全操作。

MySQL is not an ACID compliant database server by design, but if you use the InnoDB storage engine for your tables (or better as your database server default storage engine by setting the default-storage-engine option to InnoDB (see default-storage-engine option)), you will be able to perform transaction-safe operations on your database.

建议您将InnoDB设置为默认存储引擎，因为重要的是您不要在单个事务（例如InnoDB和MyISAM表）中对具有不同存储引擎的表进行混合操作。否则，您可能会损坏您的数据，因为如果您回滚交易，您将无法回滚所有操作。

I recommend you set InnoDB as your default storage engine because it is important that you don't mix operations on tables with different storage engines within a single transaction (InnoDB and MyISAM tables for example). Otherwise you might corrupt your data because you won't be able to rollback all your operations if you rollback your transaction.

那么，如何确保您的操作ACID兼容？只需在事务中进行分组操作，即可将从数据的一致状态更改为另一个，并在结束时提交（如果一切顺利），或者如果发生错误则回滚。要实现这一点，你需要通过发出一个 START TRANSACTION 语句告诉数据库服务器何时开始事务，当你的事务以 COMMIT 或 ROLLBACK 语句。

That said, how do you ensure that your operations are ACID compliant? Well simply by grouping operations within transactions to go from one consistent state of your data to another, and commit at the end if everything went well, or rollback if something went wrong. To achieve this you need to tell the database server when your transaction start by issuing a START TRANSACTION statement, and when your transaction end with a COMMIT or ROLLBACK statement.

通常一个好办法是禁用自动提交模式使用 SET AUTOCOMMIT = 0 命令启动脚本，尽管它不是强制的，因为 START TRANSACTION 命令实际上禁用自动提交模式。

It is generally a good practice to disable the autocommit mode at the beginning of your scripts by using the SET AUTOCOMMIT=0 command, although it's not mandatory since the START TRANSACTION command actually disable the autocommit mode.

还要记住，一些语句发出一个隐式的 COMMIT 命令和其他一些，请参见导致隐性提交的声明 ）。

Also keep in mind that some statements issue an implicit COMMIT command (basically all DDL statements, and some others, see Statements That Cause an Implicit Commit).

您可能也有兴趣阅读有关MySQL中处理交易的标准SQL的差异交易和原子操作。

You might also be interested in reading about the differences from standard SQL with regard to treatment of transactions in MySQL Transactions and Atomic Operations.

这篇关于如何实现数据库的ACID模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！