ACID Transactions
What is an ACID Transaction? This is a typical question when interviewing for a DBA position. Interestingly, not all data storage engines support ACID transactions, especially most of the newer NoSQL tools.
In a short, non-technical definition, an ACID transaction is one where you are assured that modifications you make to data are fully completed. For a more detailed discussion check out the wikipedia link http://en.wikipedia.org/wiki/ACID.
Are users of NoSQL data stores at risk of having corrupted or un-reliable data? The answer depends on the requirements of the system storing the data. If you were making a transfer from one bank account to another, you wouldn’t want to see a debit or credit from one account without seeing simultaneously the opposite transaction on the other account. Some data is not that tightly coupled. Sometimes it is ok for one fact to lag behind that of another.
In some engines data is stored on multiple servers and is not immediately consistent. Unlike an ACID transaction where data must be fully committed on all replicas before completing the transaction, the non-ACID engines allow data to be inconsistent from one replica to another. The data may be posted to one replica first, and ultimately be applied to the other replicas. However, they are allowed to be out of synchronization for an undetermined period of time.
Sometimes the NoSQL engines without ACID capability will vote regarding what the most accurate data is. The USA Space Shuttles used this technique having five computers simultaneously solving the same problem, and then voting for the best solution.
Why would we not simply use ACID transactions for everything? The short story is performance. When you need to modify data quickly, do you always have time to wait for that data to be committed to all replicas? Sometimes you can commit it and walk away knowing it will ultimately get to all necessary destinations.
Are you using non-ACID storage engines? What environments have you found to be well aligned to this technology? Share your thoughts in this conversation in comments below, or drop an email to me at btaylor@sswug.org.
Cheers,
Ben