Tip of the Day — ACID Compliance
When working with databases that allow for transactions you will often see the term ACID compliant. ACID is an acronym for:
Atomic - all (SQL) code is successfully executed or is canceled as a unit
Consistent -a database in a consistent state when a transaction begins is left in a consistent state by the transaction
Isolated - one transaction does not affect another
Durable - all changes that finish successfully are recorded properly in the database. Changes are not lost.
Transactions are groups of one or more SQL statements that have a beginning (denoted by BEGIN or START TRANSACTION) and an ending (denoted by COMMIT). As Roland points out in the comments transactional support can also be achieved by using autocommit mode. This is important when dealing with things such as financial information. MySQL provides transaction support on multiple storage engines and ACID compliance along with it.
A simple transactional example might be beneficial.
Suppose two people wanted to move $4,000 from one account to another for the purchase of a car. Also assume that the bank database had a simple account table structure like the following:
create table accounts ( account_id int not null auto_increment primary key, balance float ) type = InnoDB;
Now to move the money from the account of customer one to the account of customer two we might do something like this in my very bad pseudo-code:
select balance from accounts where account_id = id_of_customer_one;
make sure that balance is greater than 4,000;
update accounts set balance = balance - 4000 where account_id = account_id_of_customer_one;
update accounts set balance = balance + 4000 where account_id = account_id_of_customer_two;
The problem with this is that if this is an interruption between the two update statements there is a problem. Suppose the server crashes or the power goes off. Now there is a problem. One account has 4,000 less dollars, but the other account didn’t receive it. The solution is a transaction. Adding a transaction makes our code look like this:
select balance from accounts where account_id = id_of_customer_one;
make sure that balance is greater than 4,000;
begin;
update accounts set balance = balance - 4000 where account_id = account_id_of_customer_one;
update accounts set balance = balance + 4000 where account_id = account_id_of_customer_two;
commit;
Now with the addition of the transaction statements the two updates will either commit or fail (and do what is called rollback..going back to the original state). If the power fails or the server crashes in between the updates the rollback will be performed when the server comes back up and everything will be as it was when the transaction began.
It is important that the created table is of the type that supports transactions. Innodb (like this example table) does support transactions. MyISAM does not support transactions and would just ignore the BEGIN and COMMIT statements.
5 Comments so far
Leave a reply
“Transactions are groups of SQL statements that have a beginning (denoted by BEGIN or START TRANSACTION) and an ending (denoted by COMMIT).”
Well, not quite
Many people think that spanning multiple statements is a defining characteristic whereas it is just a capability for which transactions are a pre-requisite.
Many RDBMS-es support the so-called “autocommit” mode that wraps each individual *statement* in its own transaction that is implicitly committed if execution is successful. Many people think that this does not constitute a real transactions, as they span only one statement. However the ACID properties all apply.
The difference with not having transactions is easy to spot. a statement that processes multiple rows, let’s say INSERT…SELECT
Let’s assume one row being inserted would cause a constraint violation. Well, with an autocommitting transaction, the entire statement would be rolled back, whereas a non-transactional statement would bail out, leaving the rows inserted so far behind.
see:
http://rpbouman.blogspot.com/2007/02/mysql-transactions-and-autocommit.html
Thanks Roland,
The fundamental problem I have with “tip of the day” is that because I do it five days a week I don’t have time to go into a great deal of detail. Also, of course, I don’t have time to sit around and think about the post for a day or two before posting it. Ideally the post would even be shorter than this one. In the long run I might discontinue doing them if I find that I put out to much erroneous material. I am aware of (and understand) the autocommit mode of MySQL, but because of time/space constraints I didn’t go into it. And yes, taking this in consideration my definition of a transaction isn’t really accurate. That part I didn’t think through enough. I suppose I better make a modification to the post before I lead too many people astray. Thanks for pointing this out!!
By the way, your blog post on is very good.
Hi Keith,
I truly admire your productiveness and considering that you write one each day, I think that the quality is astounding! It takes me about a week to write one technical post and I think I will never be as productive you or some of the other bloggers.
Also, I have to admit I have quite a pedantic tique in that I don’t allow myself simplification by leaving out or skipping stuff. So I work very hard on creating definitions I put in my post, and usually have to spend a lot of time in making my definitions less verbose so they can still be an enjoyable read. (that’s what I try anyway)
I probably should learn to not point it out everytime I see some omission or mistake and I had by no means the intention of telling you off.
Actually I also would not want to say that this particular post was erroneous: you never said anything that was not true, you simply left out a part. In addition, many, many texts about transactions stress the fact that it allows multiple statements to be treated as one. Almost all texts trample over what it means to have implicit transaction spanning a single statement. So don’t take it too hard
You said something about discontinueing the tips of the day in the long run: please don’t! It’s always enjoyable to read. Don’t be afraid to get it wrong now and then. This is actually why I like blogs and blogging so much. By doing it a lot and by receiving feedback, everybody gains something, and that is what I like so much about it.
Looking forward to your next tip of the day,
Roland Bouman
Thanks Roland for the very kind comments.
Don’t let a few small errors or omissions deter you from writing! It’s great stuff that you’re producing. And we’re lucky to have Mr. Pedantic around to set us straight on the details that we do sometimes get wrong.