How to implement the ACID model for a database?

Question

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

Answer 1

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

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.

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.

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.

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

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.