ACID Database Transactions
You often hear the term “ACID” to describe a database transaction. This term dates back to 1983 and is an acronym that describes four components that should be part of every database transaction: atomicity, consistency, isolation and durability. While these terms may seem complicated, they are actually quite easy to understand, as explained later in this article.
And understanding these principles is important. If you are compliant with these principles, and respect ACID properties you can ensure the integrity of data stored in your database even when unexpected errors and events (like power failures) occur. Following these principles is especially critical in banking and ecommerce applications and any applications where health and safety are involved, like air traffic control.
Storing data according to ACID principles is especially import not that big data is so valuable. In fact, back in 2017 the economist stated that data has become more valuable than oil! Accordingly, it is critical that data is reliably stored, eliminating the possible of corruption. And ACID principles apply to most modern relational database systems including SQL, MongoDB, PostgreSQL, MySQL on desktop systems, local network servers and cloud-based servers like Microsoft Azure and AWS (Amazon Web Services). In fact, most relational databases are built on ACID foundations.
Database Atomicity
A transaction in a database may be composed of several different operations. For example, if you log into your bank account and transfer money to another account, two main operations would be debit of your account and the credit of another account. Database Atomicity ensures that these operations are treated as a single transaction in a timely manner. And if any part of the transaction fails your database is left unchanged, ensuring that changes to a database are all or nothing. So, in other words every part of an operation needs to succeed before any changes are committed to your database. This means that if an error does occur, any changes made to the database as part of the transaction will be rolled back. Using the banking example, if your database didn’t offer atomicity, you could find yourself in a situation where money was debited out of your account, but it wasn’t credited to another account if the transaction failed in the middle of processing. Obviously, this is a situation you want to avoid.
Database Consistency
Database consistency ensures that all data entered in your database is valid. This means that all rules governing the data in your database are respected, and all data in your database can be relied on to be true. A very simple example of ensuring database consistency would be the storage of valid addresses. For example, you would never want to add Los Angeles as a city name, with Texas as the corresponding state, as this is not valid information. Please note you can also decide what is valid in your database based on your own business rules, business logic and your own constraints.
Consistency also ensures that information in your database is never left in a half-completed state. For example, if you are transferring money between back accounts, the database will never be in a state where the debit has been made, but the credit hasn’t.
Finally, in a consistent database all references will be valid. For example if one record points to another record, consistency ensures that the other record exists.
Database Isolation
Database isolation controls when other database users see changes made by other users. Part of the process in large scale database systems like SQL is locking data fields to prevent multiple people from attempting to modify data at the same time. Or in technical terms you want to prevent “race conditions”.
In a banking example, the importance of this principle is obvious. If a bill payment and a monetary transfer are initiated at the same time, the bank account needs to have enough money to fund both transactions. This means that both transactions need to be isolated from each other, so they both don’t try and debit the account at the same time (if the transactions were not isolated, there might not be enough funds in the account to complete both transactions).
Another application where database isolation is important is in ecommerce applications. If you have multiple people trying to buy the same item, you need to ensure that the transactions complete in order to ensure that stock is available for every purchaser.
There can also be different levels of isolation, depending on the database operation. For example, it could be perfectly valid to read database values at the same time, while updating values needs to occur in isolation. Using an appropriate isolation level for a transaction can increase system performance and reduce bottlenecks.
Data Durability
The final ACID principle is data durability. Durability in the context of a database simply means that changes made to the database are permanent. This means that all database changes need to be committed to “non-volatile” memory in a stable storage area. In non-volatile memory database changes are guaranteed to be saved even in the event of a power failure or other system related problem. Please note that classic in memory databases can not ensure data durability, since information is stored in volatile memory.
ACID in No SQL Databases
Generally speaking, No SQL database do not implement ACID principles. Implementing ACID can increase overhead, and it’s generally not needed in No SQL database where information is stored as independent documents, with limited scheme definitions, instead of independent relational structured tables. In a No SQL database, since an entire document is often saved and modified at the same time in a single transaction, a No SQL transaction may have significantly less steps than a relational database transaction. This architecture makes ACID compliance less import in a No SQL database.
ACID Principles in Tracker Ten
Our own desktop Tracker Ten database system automatically supports ACID transactions. When you make change to a Tracker Ten record, all information is saved at the same time. Tracker Ten fields also support consistency checks, ensuring that entered data is valid. Durability is also accomplished by saving your database to disk.
