ACID
Acid is an acronym that stands for a set of properties that guarantee that database transactions are processed reliably; to measure reliability, engineers look at atomicity, consistency, isolation and durability.
- To illustrate these points, let's consider bank transfers.
- Atomicity:
- An atomic system must guarantee that the entire transaction happens or the entire transaction doesn't happen. That is, if only a part of the transaction happens, it's in some cases worse than if the transaction didn't happen at all.
- EG: When you transfer money from your checking to your credit card, it would be a problem if your checking was deduced the total amount but your credit card was not debited the total amount. Then, it would look like you simply lost money. This is an atomicity error, as only part of the transaction occurred
- Consistency:
- While significant ambiguity exists about this principle, it generally means that the database store similar transactions in the same manner without issues. In addition, it also means that the constraints of the database itself cannot be violated once a transaction commits.
- EG: After making a successful transfer of money from your checking account to your credit card, you attempt another transfer of a similar nature and it fails. That, or the database incorrectly refers to the initial balance in your account before the previous transaction. This is a consistency error, as transactions of similar nature should have no issues.
- Isolation:
- Isolation refers to how and when a transaction becomes visible to other users and systems (transaction schedule). A lower isolation level increases the ability of many users to access data at the same time, but increases the number of concurrency effects users might encounter. Conversely, a higher isolation level reduces the types of concurrency effects that users may encounter but increases the chance that one transaction will block another.
- EG: You decide to buy a car unbenounced to your spouse and, coincidentally, your spouse decides to do the same unbenounced to you. You're both using the same bank account. Let's say your spouse pays for the car a couple of minutes before you purchase yours. When you go to purchase yours, you get an error "not enough funds", so you go to your online bank account and see that all of the funds are indeed available. This is an error of isolation as your spouse's transaction is not yet viewable to you.
- Durability:
- Durability is one of the more straightforward properties, outlining the need for redundancy and durability of the system to retain information even in the event of a power outage. Storing data permanently helps prevent against data loss in the event of crashes, systematic errors etc.
- EG: The bank's database crashes and loses all records of your account funds, thereby leading you to lose however much money you had in their system with no record to refer to.
http://stackoverflow.com/questions/3740280/acid-and-database-transactions
http://searchsqlserver.techtarget.com/definition/ACID
http://www.dbrnd.com/2015/05/acid-properties-in-database-system/