Transaction Basics and ACID
I began to write a post on InnoDB transactions, but there was so much background material that I decided first to write a post introducing transactions, and then one on how InnoDB implements them. If there is good response from these two posts, I will continue with other posts on the major storage engines and their transactional characteristics.
When reading through literature about RDBMSs (Relational Database Management Systems), you will see the terms transaction(s), and ACID-compliance.
A transaction is a sequence of statements that are executed on a RDBMS. For a transaction to be completed and database changes made permanent, a transaction has to be completed in its entirety. If a transaction is not completed for any reason, it is rolled back to the original state of the database before the transaction began execution. The characteristics that define a transaction are called ACID. ACID stands for: Atomicity, Consistency, Isolation, and Durability.
According to Wikipedia’s article on ACID, it is “a set of properties that guarantee that database transactions are processed reliably.” It is useful to understand the different parts of ACID, so here they are briefly:
- Atomicity
- The ability of the database to guarantee that either every part of the transaction is performed or none is performed.
- Consistency
- The database remaining in a consistent state before the start of a transaction and after the transaction is finished.
- Isolation
- The ability of the database to make operations in a transaction appear isolated from other concurrent operations.
- Durability
- The guarantee that once the user has been notified of success, the transaction will persist through all conditions, including system failure. Durability is frequently accomplished through writing all transactions to a log file. This log file can be “replayed” in the event of a system failure. It is only after the transaction is written to this log file that the transaction can be considered safe.
So now you have some background. Next time, we will take a look at how the InnoDB storage engine implements transactions.
July 1st, 2008 at 1:55 am
You trigger my curiosity so I want to InnoDB home page and noticed that InnoDB is a subsidiary of Oracle.
But at performance pages, they compare InnoDB to ‘Other databases’ and follow a comparative grid results granting InnoDB a tenfold advantage over ‘Others DB’. Yup I could not believe my eyes : ‘Other DB’.
July 1st, 2008 at 2:00 pm
[…] is a storage engine that uses MVCC (described shortly) to provide ACID-compliant transactional data storage using row-level locking. MVCC stands for Multi-Version Concurrency Control. It is how InnoDB […]
July 2nd, 2008 at 5:47 pm
Let me point out one thing. Because of space I intentionally didn’t point out that there are different isolation levels that are available. However, it is useful to point out that the transaction example I used doesn’t work as given at all isolation levels. For the reader who is interested in these isolation levels here is a good post written by my co-worker Sheeri http://sheeri.com/content/isolation-%2526amp%3B-concurrency
July 24th, 2008 at 12:07 pm
[…] a database table. This is done to ensure two parts of the ACID equation, durability and atomicity. I discussed these traits previously so I won’t cover them […]