Protecting your database with ACID
At any given instant, millions of database operations are being performed all over the world. Be it banks, stock markets, e-shops — each one of them stores their data in databases. Every time someone wants to make a change to the database, a transaction occurs. A transaction can be defined as a single logical unit of work that consists of several operations accessing and modifying the contents of a database. For institutions that use databases, it is vital to ensure their stability in order to protect their clients‘ important data. In relational databases such as PostgreSQL, this is done by following the ACID model.
ACID is one of the oldest and most important concepts in database design. The acronym stands for atomicity, consistency, isolation and durability. If these principles are not followed, the whole database can crash. Let’s look at each of the properties in more detail. We can illustrate each of them using a simple banking example.
Atomicity
Atomicity means that each transaction is treated like a single unit. Think of it as an atom — the individual parts cannot be separated from one another. Also known as the all-or-nothing rule, a transaction only succeeds if all of its contained operations succeed. If any operation fails, the database is returned to its previous state (the changes made by then are rolled back).
For instance, if a person wants to move money in a bank from account A to account B, the transaction consists of substracting a certain amount of money from account A and adding the same amount to account B. It can’t happen that only the account A’s amount of money is changed or that only part of the amount is added to account B and the rest is lost in the void. Atomicity ensures that either all of the operations are completed or none of them.
Consistency
Consistency states that only valid data can be stored in the database. It is assumed that before a transaction begins, the database is in a consistent state. After a successful transaction, the database is consistent again. If any error occurs, e.g. attempting to write data of the wrong type or a system failure, the whole transaction will be rolled back. Additionally, consistency ensures that the sum of values in the database is constant.
This means that, if we look back at the banking example, if amount N is moved from account A, then the same amount N must be moved to account B so that the total of all accounts remains the same. It can’t be the case that one of the transactions substracts $500 from account A and adds $700 to account B. Consistency also ensures that you can’t put a value such as ‘thousand’ or ‘hello world’ in a column which requires numbers. If you tried to do that, the database would be returned to its state before the transaction began.
Isolation
Even though sometimes several transactions run at the same time, they must appear as if they were performed sequentially so they don’t interfere with each other. The database is always consistent before and after executing a transaction, but that doesn’t have to be the case in the middle of one. If transactions weren’t isolated, they might attempt to access inconsistent data, which would lead to errors in the database.
Imagine that both Alice and Bob want to send money to your account. They might try to do it simultaneously. If their transactions weren’t isolated, they might interfere with each other by trying to modify data at the same time, which could potentially cause errors. By forcing the transactions to run one after another, this is prevented. Note that isolation doesn’t have any influence over which transaction runs first, it simply ensures that they don’t run at the same time.
Durability
Durability ensures that once changes have been made to the database, they are permanent. Even in the case of system failure, it must be possible to recreate the database to the state it was before the failure happened without any loss of data. This is acheived by backing up and by logging all transactions.
Loss of data can be the consequence of many things, from hardware being physically damaged to software being corrupt to someone accidentally deleting the database. You can surely imagine how big of a problem this could cause in banking, for example. Thankfully, if the database follows the durability principle, all the data will be backed up in another place so that it can be restored and clients don’t lose their money.
Conclusion
These four principles are very important in database design. They can be enforced using various techniques which are beyond the scope of this article, e.g. shadow-paging. Most people who work with databases don’t really have to worry about them, since many databases, such as SQL Server, MySQL or PostgreSQL follow the ACID model by design.
All institutions that work with sensitive data — not only banks, but also hospitals, insurance companies etc. — should and do implement the ACID principles. A database that is properly designed has a very low chance of malfunctioning. Therefore, your money is most likely safe and you don’t have to replace your account with a piggy bank.
