ACID are properties of a transaction, that are often implemented by relational databases, in order to guarantee reliable transactions. Let’s first define what it means to be a transaction.
What is a Transaction?
A transaction is way of encircling a block of SQL code, to define a start and a finish. This block of code can be 1 or many individual SQL statements. By using a transaction, we can group the statements inside of this block. At the end of this block, we can decide whether to commit the result of our block, or rollback to the state of the data right before the transaction began. Therefore, a transaction can only end in one of two states: committed or rolled back.
Committing or rolling back a transaction is decided by some conditional statement. This conditional can be either on a row count, or error thrown, or something else. The client can decide if they want to implement their conditional logic in SQL or clientside. Regardless, the client will need to send a COMMIT or ROLLBACK statement for the particular transaction.
Atomic
Atomicity states that a transaction will be execute as a single units. This means that if a transaction has 2 statements and 1 of them fails, regardless of which statement failed, the entire transaction will be rolled back.
Consistent
Consistency states that a transactions will always transform the database state from one valid state to another. This is done by ensuring database cosntraints are respected.
Isolation
Isolation states that transactions are independent from one another. Transactions may be blocked by other transactions however, there will be no interference between two transactions. Suppose two transaction reach the DB at the same time. Transaction A is updaing row XZY while transaction B is reading row XYZ. Either B will execute before A or B will execute after A. B will not execute while A is being executed. In this way, the transactions will be executed serially. How transactions behave when they occur simultaneously, can be tuned using isolsation levels.
Durable
Durability states that once a transaction is committed, the state is permanent. This means that neither a system failure or a power outage can affected a committed transaction. To ensure this, databases write to durable storage (e.g. disk) before applying the changes to the database.
Tradeoff
Though most relational databases guarantee ACID properties, some do not. The reason for that is performance. In order to ensure all of the above, there is an overhead for executing a transaction. VoltDB for example allows tuneable consistency.
ACID properties can also be found in non-relational databases however, the cost is complexity. In a distributed environment, it’s very difficult to abide by ACID principals. One such database is MongoDB.