The not so ACID transaction properties

If you work with Database Servers you know that ACID its not implemented/active by default! And if you have it implemented you have a really poor performance on your database server!

Lets examine each one of these properties:

A = Atomicity

Definition by wikipedia: “Atomicity requires that each transaction is “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.

WRONG!

– “… and the database state is left unchanged“. Unless you configure your database server with ISOLATION LEVEL SERIALIZABLE this is not true. By default most SGBD are configured with Read Commited, with allows some changes like phantom reads.

C = Consistency

Definition by wikipedia: “The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including but not limited to constraintscascadestriggers, and any combination thereof.”

RIGHT for schema state, WRONG for value state (explained in atomicity above)

I = Isolation

Definition by wikipedia: “The isolation property ensures that the concurrent execution of transactions results in a system state that could have been obtained if transactions are executed serially, i.e. one after the other. Each transaction has to execute in total isolation i.e. if T1 and T2 are being executed concurrently then both of them should remain unaware of each other’s presence

WRONG! Only in dreams! its possible, yes, but in the way you encounter DEADLOCKS and Poor performance. If you want a performance database server you must drop the isolation to the lowest level possible! Its the price to pay for having fewer locks in the middle of your transactions.

D = Durability

Definition by wikipedia: “Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter).”

RIGHT! Well, I give this one, and I’m glad I could, what was a database server without it! 😉 However, actual high performant database servers have some kind of memory cache where there is a risk of loosing data, but, as far as my knowledge goes, it only happens in some NoSql databases… I hope…

 

 

 

No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Bad Behavior has blocked 140 access attempts in the last 7 days.

Hyper Smash