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

问题描述

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

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

推荐答案

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的数据库服务器，但是如果您对表使用 InnoDB 存储引擎(或将default-storage-engine选项设置为InnoDB(请参见 default-storage-引擎选项))，您将能够在数据库上执行事务安全操作.

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命令(基本上是所有DDL语句，而另一些则请参见

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

您可能也有兴趣阅读与标准SQL在MySQL事务处理方面的区别

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屋！