How to make a database ACID-compliant
Everything about data science starts with data. Without proper and accurate data, data science is like a luxury car with no gas. A well-maintained, easily accessible, scalable, and hard-to-fail database is essential to provide access to data.
In order to make sure a database does not fail us, we need to design and create it properly. Database systems are designed to meet a set of properties known as ACID. Atomicity, Consistency, Isolation, and Durability are the properties that constitute ACID.
Before we start the discussion on ACID properties, we need to have an understanding of database transactions. A transaction basically represents a change in the database. It can contain a single or multiple operations. For instance, transferring money from a bank account to another contains multiple operations. First, the money is withdrawn from the sender's account. Then, it is deposited into the receiver’s account.
A transaction is treated as a whole even if it contains multiple operations. After a transaction is completed, the state of the database changes.
We know what a database transaction is now. Let’s start with the ACID properties.
Atomicity
Atomicity is best explained via actual atoms. An atom is the smallest unit that forms a chemical element. It consists of protons, neutrons, and electrons but treated as a complete structure. We cannot have half an atom. We either have an atom or don’t.
We can actually split an atom through a process called nuclear fission but it is an extraordinary process and releases a great amount of energy. For our discussion on databases, we will stick with an atom being a single, complete unit.
Atomicity refers to the entirety or integrity of a database transaction. An atomic transaction either happens or does not happen at all. We cannot have partially executed transactions.
The money transfer transaction clearly demonstrates the atomicity. When the money is withdrawn from the sender’s account, it must be deposited into the receiver’s account. Otherwise, we would have a big problem. An atomic transaction ensures this action is executed as a…
