When it comes to learning databases there sure is a whole lot of ground to cover. A good starting point is to first become familiar with some of the key definitions. Let’s start with ACID.
ACID Definition
In a database, a transaction is a unit of work that accesses the contents of the database and can then potentially alter the data. ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that are applied to the database to ensure that consistency is maintained during transactions.
Atomicity
Atomicity ensures that the transaction must be complete. The transaction has to go from start to finish in its entirety. If the transaction is unable to either initiate or complete for whatever reason, then both sides of the transaction must not reflect any change.
Here is an example. Pretend that you are doing some online banking. You would like to take some money from your checking account and put it into your savings account. Accidentally, you entered an amount that exceeded what was in your checking account. Most likely, you would get an error message indicating this.
The error message might say something like, “Requested amount exceeds available balance.” So, you look at your checking account and the balance remains the same as before the request. Then you look to your saving account and then you notice that the balance has increased by the amount that you requested for the transfer. What gives?! This scenario is a good example of a database that is lacking in atomicity.
In the example there is now an inconsistent database state. The transaction failed at the start, so it also needs to fail at the end. Likewise, if a transaction succeeds at the start then it also needs to succeed at the end.
Consistency
Consistency means that during the course of a transaction the database must remain consistent. No data must be lost or created. Here is an example. Let’s say that a transaction is occurring within the database between A and B. The transaction hasn’t occurred yet. The sum of A and B is 300. After the transaction, the sum must remain 300.
So now the transaction occurs. 50 is taken from B and added to A. While the values of A and B have changed, the sum is still 300. The database has remained consistent.
What occurred between A and B is a READ WRITE transaction. You can learn more about that here.
Isolation
Isolation basically guarantees that all of the transactions will occur in isolation. In a database there are often transactions occurring simultaneously of each other. The database needs to be structured in such a way that concurrent transactions do not jeopardize the consistency of the database state.
This means is that if there are changes that are occurring in a particular transaction, those changes will not be visible by any other transaction until all of the changes from the transaction in progress are written to memory.
Durability
Durability ensures that once a transaction is committed, the data remains securely in the database. That means that any transactions or modifications that were made, need to be stored and written to disk. Even in the case of a system failure, the transactions and modifications need to persist.
ACID Compliance
All of the major relational databases follow the ACID principles. On the other hand, NoSQL databases often focus on high availability. This usually means that either consistency or durability is sacrificed.
