What is ACID in a database?

Question

I’m not talking about web implementation indicator.

Besides the acronym, because it’s important?

Answer 1

• Totomicity

  It is something indivisible. Either everything that is in a transaction must be carried out successfully, or nothing must be carried out. At least nothing should be considered fulfilled. Without atomicity it becomes difficult if not impossible to maintain the other features, so the transaction is important.

• Consistance

  The database must have a transaction terminated in a consistent state, that is, it must respect all rules imposed in the database for all those involved in the transaction.

• Isolace

  One transaction cannot interfere with another while it is in operation. Only after your conclusion will your result become available for other transactions.

• Durability

  At the end of the transaction the result must remain in the data bank, whatever happens.

Motivation

Without these features the database is unreliable. An operation may end in half, or it may be in a state that will cause problems, or manipulate data that is not yet known whether it will be useful or final or it may lose what has been done.

Some databases say ACID when in fact they are just almost ACID :) They are ACID, but in the mucho, have some cases that they do not guarantee it, ie they say they are more or less pregnant :)

The concept is implemented to avoid racing conditions and similar problems. In link has an example of bank account that is the classic problem.

• If you need to update an account balance you need to ensure that everything that is done to change the balance must be done until the end, you cannot change in one table and not change in another, you cannot debit in one account and stop debiting in another (atomicity).

• At the end of the transaction you cannot leave the balance with a value that has been determined that is not allowed, for example: normal balance plus limit being negative, or the balance not having a transaction tied to it (consistency).

• You may not let other transactions see the credit until it is completed, as the transaction may not work or you may also have other credits or debits during the transaction that will affect the end result (isolation).

• You can’t help but keep the new balance value and everything that was done to get to it (durability).

Normally this is obtained by a system of journaling, write Ahead log or some similar way. It is important that the transactions of the transaction are made outside the normal access of the database and only enter at its end. The most used techniques are two Phase Locking, multi version concurrency control and snapshot Isolation.

Wikipedia article.

Databases called Nosql are not usually ACID (some are, some are partially, that is, they are not). Those who are do not offer all those advantages that say that Nosql has, there is no miracle. Or they are unreliable. Of course problems that overall performance and distribution are more important than the reliability of information so it is useful. As well as having problems like this, ACID databases can perform better in certain scenarios.

If you don’t know what you’re doing Nosql can be tragic and much slower. Nosql opts for GROUNDWORK which we can call the opposite of ACID, although it is not quite so.

See about the CAP. It shows that there is no miracle. Do not believe in false prophets who promise they will solve all problems and break the Theorem CAP.