ACID Properties By (Counter) Example
In the 1800s scientists first used the word atom to describe the smallest bits of an element. They picked that word because it meant un-cuttable or indivisible. The idea was that that’s as far as you can go; you can’t break down these bits further. Fast forward to the early 1900s and they found out that atoms actually can change (through radiation or splitting). But it was too late to change the language. Splittable or not, atoms are atoms.
This process of splitting atoms is so interesting that somehow the word atomic has come to refer this process of dividing atoms (e.g. atomic bomb, atomic energy).
Atomic Transactions in SQL Server
But when we talk about the word atomic as one of the ACID properties of transactions, the word regains its original meaning: indivisible. SQL Server transactions are always atomic. They’re all-or-nothing. To twist Meatloaf’s words, this means that two out of three is bad (It’s got to be three out of three or nothing). It’s often forgotten, but this applies to single statement transactions too; all of a statement (whether an update, delete or insert) will happen or not happen.
To guarantee atomicity, SQL Server uses a Write Ahead Transaction Log. The log always gets written to first before the associated data changes. That way, if and when things go wrong, SQL Server will know how to rollback to a state where every transaction happened or didn’t happen. There’s a lot more to it than that, but as a developer all I care about is that I can trust that my transactions don’t get split.
Example
Here’s an example from outside the I.T. industry. It’s a story about an all or nothing transaction. About two years ago, Samoa switched from driving on the right side of the road to the left (The NYT has a great article on it).
You can imagine the great effort that must go into a switch like this. And it has to happen all or nothing. The switch has to happen everywhere, all at once with no exceptions. Unlike other big projects that can usually be broken down into smaller phases, this one can’t.
Translated into SQL, this might be equivalent to:
BEGIN TRANSACTION UPDATE ROADS SET TrafficDirection = 'Left' WHERE Country = 'Samoa'; UPDATE TRAFFIC_LIGHTS SET TrafficDirectionMode = 'Left' WHERE Country = 'Samoa'; UPDATE INTERSECTIONS SET TrafficDirectionConfigurationMode = 'Left' WHERE Country = 'Samoa' COMMIT |
It’s not an I.T. example, but you get the idea. If this “transaction” were not atomic there would be trouble!
Counter Example
An example of failed atomicity (outside I.T.). One word: Standards.
Say you want to create a universal standard for something (say the Metric system) the main purpose is to create it to be the single standard to replace all others. If you fail in your goal, you’ve added to the problem!
Some more successful universal standards:
- http (over gopher etc…) for almost all web traffic
- Blueray (over hd-dvd) for hi-def movie formats
But consider the Metric system. It’s mostly successful because of its large adoption. But because there are a few stragglers, it’s not as successful as it could be. Translated into SQL:
UPDATE EVERYTHING SET Units = 'METRIC', Value = fn_Convert(Value, Units, 'METRIC') -- no where clause! |
This “statement” didn’t update everything. The “statement” wasn’t atomic and this continues to cause problems. One problem that comes to mind is the failed Mars Climate Orbiter mission.
Let’s be grateful for the all-or-nothing transactions we have in our databases!
That’s interesting about the Samoans–mandating a switch in habitual behaviors, particularly ones predicated on safety, seems like a risky decision for a govt to make.
Comment by Claire — July 28, 2011 @ 10:14 am
[…] ACID Properties By Example (And Counterexample) Part One: Atomic – Fantastic SQL content and artistic flair are things we’ve all come to expect from Michael J. Swart (Blog|Twitter) but this week we also get a little history lesson too. […]
Pingback by Something for the Weekend – SQL Server Links 29/07/11 — July 29, 2011 @ 7:55 am
Thanks Claire. When Sweden did it, it was to get in line with all their neighbouring countries (increasing safety). Samoa (an island nation) did it presumably because it was cheaper to import their cars from Australia, New Zealand or Japan.
Comment by Michael J. Swart — July 29, 2011 @ 8:36 am
Interesting article. If i’m well informed ATOM based databases are not very scalable like the nosql (and newsql) databases. It seems to me that there is a need for scalable databases that can work in networking integrated environments. Could SQL Server do that? Or is the cloud the answer? Or will there be a diversification in all kind of applications of databases?!
Comment by Hennie — August 2, 2011 @ 6:34 am
Hi Hennie,
That’s a huge topic there and probably not one I can answer too well here. Regular RDBMS databases (including SQL Server) are actually very scalable. It’s just that they’re not infinitely scalable. People forget that there’s a whole lot of room to scale up. Buying bigger, faster hardware is actually easier and cheaper in most situations.
But in rare cases, you may run out of space to scale up. NoSQL databases try to solve this problem by scaling out with multiple servers. Each server handling part of the load (and that’s where the cloud might come in). People deciding to use NoSQL must be aware of the RDBMS benefits they’re giving up and they’re pretty big benefits.
NoSQL will never replace RDBMSs. I believe in the end, there will be a diversification of all kinds of database systems. And I have a good hunch that the RDBMS will not become obsolete in my lifetime.
Comment by Michael J. Swart — August 2, 2011 @ 8:23 am
It was actually well before the 1800s, it was the ancient Greeks that named the atom (indivisible). That’s several centuries BC.
Comment by John Alan — December 23, 2012 @ 7:23 am
Fair enough John, My ten minutes of google research tell me you’re right. And that John Dalton didn’t coin the term, but revived the it to apply it to our modern understanding of the atom. http://www.etymonline.com/index.php?term=atom
It made cool reading. Thanks John.
Comment by Michael J. Swart — December 23, 2012 @ 7:17 pm
Micheal,
I like the way you dive into a subject and dig into the inner workings.
It is the attitude of Masters – understanding the structure of the soil, instead of merely gazing at the plants.
The illustrations are outstanding too!
I’ve only recently discovered your blog, and am still reading through it…
Comment by John Alan — December 24, 2012 @ 7:56 am
Thanks John!
If you notice anything as you read, feel free to leave a comment. I love feedback.
Michael
Comment by Michael J. Swart — December 27, 2012 @ 11:22 am
Hi Michael,its a amazing topic…your atom theory with example very nice
Comment by Murugan Kathisath — October 25, 2013 @ 6:17 am
Good one
Atomicity means either it ll take all its buddies with him or none…>>>and also thanks for insight about write ahead transaction log
Comment by Aditya C — November 21, 2013 @ 12:01 am
Great. My application is already set sports with my resources within the server of mine. Wherein your server is running with a client and your resources. Up till there is an up-link between your client and my application this theory would be empirical as Atomic Transactions. Application will ask your two then to do some work but if either of you two come with problem, we are going to forget it ever happened you observe. My Server will then bootstrap from Prepare To Prepared To Commit To Committee. The fact that it bootstraps on its own more than the number of days in the year is what drives the rate in annual review across the server..
Comment by Rior000 — March 13, 2015 @ 11:22 am
Rior000,
I really struggled with deciding to approve this comment. It’s either spam, or it’s a poorly translated comment on the blog post.
It sounds like you’re trying to discuss some concerns encountered with distributed systems. Mr. Rior000, if you’re a human, I invite you to post your comment again in your own language.
Comment by Michael J. Swart — March 13, 2015 @ 11:47 am
Check Out my Video on ACID https://www.youtube.com/watch?v=0BuYE2IkBzI
Comment by salony — December 15, 2018 @ 8:27 am