Transaction Processing ACID Test
Reliability was not the hallmark of the web until money began changing hands. Since then transactions need to reliable in the most rigorous way. The ACID test has been the gold standard for determining transaction processing reliability since the 1970's.
- A is for Atomicity which asks if a transaction can end having completed only a portion of its work?
- C is for Consistency. Updates made by transactions must observe all the integrity constraints built in to the database.
- I is for Isolation which asks if concurrent transactions will run gracefully and avoid interfering with each other.
- D is for Durabilty which requires that once a transaction is committed the updates will survive subsequent failures to the database or server.
Isolation is the hardest to achieve in web database applications. The typical web database update is a postback, subsequent to a submit. There can be a long time delay between reading the data and posting the update. What if someone else makes a change to your data after you read it? Will your update undo theirs?
The Consencis framework isolates concurrent transactions from each other by implementing optimistic concurrency control. As your volume grows there is the remote possibility that you will receive one of our (in)famous error messages and have to resubmit an update because someone updated a record while you were working on it. This is way better than unknowingly stomping on their update. It is also better than pessimistic locking schemes that kill performance by tying up rows, sectors or entire tables. Amazon and EBay agree that optimistic concurrency control is the best approach for web based transaction processing systems.
Atomicity requires that packages of related updates be programmed as SQL Server transactions. For example, without transaction processing, if you post the debit and something goes wrong before the credit is posted the data will be corrupted. So if something goes wrong part of the way through, we roll back all the changes. The design of SQL Server provides most of the heavy lifting, we just take advantage of it.
Consistency and Durability are achieved in the design of MS SQL Server. The database prevents changes inconsistent with its internal rules. We use these rules extensively in our database design including declarative referential integrity, cascades, and triggers. Durability comes from the architecture of the database itself. A power failure immediately after a commit can leave the database in an incomplete state but recovery procedures with log files assure that when the database comes back online any and all committed work is restored.
We sweat the details to make it right for you!
