Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In his book "An Introduction to Database Systems" C.J.Date wrote a chapter about the ACID principles called "Dropping ACID". In this chapter he calls Correctness(Consistency) "essentially meaningless" and the other principles at most "a desideratum".

This book is from 2004 and everywhere i go on the internet i see ACID principles as the guidline for RDBMS application programming. Does that mean that Date's ideas to ACID were not accepted? Or is ACID from a theoretical point of view not as mandatory as it seems when reading internet documentation about database programming?

I hope my question makse sense and is in the right forum.

share|improve this question
3  
The question makes sense but how do you expect to be answered without having read the actual book? Can you add a small paragraph or summary of Date's justification? –  ypercube Mar 24 at 16:17

3 Answers 3

up vote 8 down vote accepted

Content of the book

The author analyzes the ACID concept from a very theoretical point of view.

The essential part might be the first sentence of the last paragraph of the chapter:

Overall, then, we conclude that the transaction concept is important more from a pragmatic point of view than it is from a theoretical one.

Another important part is his introduction of the "multiple assignment" operator (p. 487), which he uses as a solution for some problems which are addressed by practical DBMS by supporting transactions. This operation does not seem to work in MySQL, Oracle nor MSSQL. (Make it possible to update several tables with one query.)

His statement is: If we had this multiple assignment operator, deferred checks of constraints are not needed and should be applied immediately and not at the end of a transaction. The latter one results in making the C of ACID useless, as the database is then always consistent (if the C stands for consistency) or it's correctness is unenforceable (if the C stands for correctness).

Concerning *I*solation he claims that this should mean all transactions should have absolutely no knowledge of other transactions might be running. This is likely not doable. He further tells that most DBMS provide "isolation level" settings, that undermine this concept in it's base.

*D*urability he writes could only apply to the most outer transaction, if nested transactions are supported.

*A*tomicity according to him is only needed because there is need for transactions. If the multiple assignment operator is available as atomic, one would not need atomic transactions.

What to take away

This is a very fundamental discussion about the ACID concept. Without judging if his critics is correct or not, there is a living landscape of DBMS, which is based on transactions.

C.J. Date himself writes that "we now have a better understanding of some of the assumptions on which that research was based". This means it's one of the fundamental aspects, which one cannot change easily in living DBMS. Remove transactions and push in atomic multiple assignment operators and no enterprise will want to update it's architecture, because this is a lot of change.

Thus you have read a theoretical chapter proclaiming how one could improve the properties of DBMS conceptually, and which would erase some problems fundamentally. But only because something might be superior, it will not be used in practice immediately.

You have basically reached the border between someone with a mere theoretical point of view and the practical world, in which there is more than theory, but an existing world, which has to wrap around.

share|improve this answer
    
+1 for actually getting CJ Date excerpts together with take away. –  RolandoMySQLDBA Mar 24 at 18:52
    
+1 Wow, awesome answer that makes me understand Date a lot clearer. –  Martin Mar 25 at 12:11

The Consistency part of ACID has to do with the state of data before, during and after a transaction.

When it comes to referential integrity, foreign keys should be properly connected, with no keys missing. This means that every operation surrounding cascading rules, triggers, and constraints must be all-or-nothing (go from start to finish, or wind all the way back so information looks the same before and after). In many cases, consistency may go beyond the scope of the RDBMS and be enforced at the application level. Why?

Business Logic may become more and more complex and require application coding that takes consistency away from RDBMS, especially if Stored Procedures are not powerful enough and the busniess logic can be done by the client.

One can say that consistency for ACID fully applies to doing everything inside the RDBMS (cascading rules, triggers, constraints, BI stored in the DBMS). Apart from this, any use of BI makes consistency for ACID meaningless. After all, you can take Oracle and SQL Server and mangle data just as much as you can with MySQL and SQLite once the consistency of data is handled at the application level.

Plenty of forethought needs to go into DB Design and Application Design. Therefore, you can either say

  • Data is in the Eye of the Holder (DBMS)
  • Data is in the Eye of the Coder (Application)

For further perspectives, please see my old post What are the arguments against or for putting application logic in the database layer? (and the other answers provided) to see what entanglements DBA and Developers have that define consistency as well my other old post Should you design the database before the application code is written? (and the other answers provided).

share|improve this answer
    
Thank you for linking those two posts here. Very interesting discussions going on there –  Martin Mar 25 at 12:12

I guess the point is that in real life SQL databases cannot fully implement the principle to its theoretical extents because of engineering constraints and application functional and non-functional requirements. Think "dirty reads", nested transactions, deferred constraints, process and thread synchronization limitations etc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.