A brief word on ACID

Every database developer should have any knowledge about the term ACID. In fact, every 'real' database according to Cobb must comply with this definition. So what is ACID? ACID stands for: Atomicity, Consistency, Isolation and Durability.

• Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails.
• Consistency states that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.
• Isolation requires that multiple transactions occurring at the same time not impact each other’s execution.
• Durability ensures that any transaction committed to the database will not be lost.

Assuming that a mature database like Firebird does comply to ACID rules and that databases under Firebird are always consistent, what is the situation when replication is involved?

Databases using asynchronous replication, cannot operate through rules of ACID.

This means that operations which succeed in one database, can fail in another. In that way, information in the two databases are not equal anymore. This fact alone is the biggest weakness of asynchronous replication. Therefore, asynchronous replication is not as simple as applying a tool to two different databases. Deep functional knowledge is needed when applying this replication technique.

On the opposite, databases connected through synchronous replication are still complying to the rules of ACID. This doesn't mean that something cannot go wrong, but that's a more technical matter.

The popular database server MySQL, at least versions until 3.x, do not comply to ACID, hence MySQL is not a real database as ACID concerns.