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.
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!
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!