Michael J. Swart

July 27, 2011

ACID Properties By Example (And Counterexample) Part One: Atomic

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , , — Michael J. Swart @ 12:00 pm

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

It's interesting: For most people, the word atomic only refers to a process where atoms are split.

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:

SET TrafficDirection = 'Left'
WHERE Country = 'Samoa';
SET TrafficDirectionMode = 'Left'
WHERE Country = 'Samoa';
SET TrafficDirectionConfigurationMode = 'Left'
WHERE Country = 'Samoa'

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:

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!

July 21, 2011

Small changes to my blog MichaelJSwart.com

Filed under: Miscelleaneous SQL — Michael J. Swart @ 9:29 pm

So if you’re here at MichaelJSwart.com (as opposed to your RSS reader) you may have noticed a couple small new things.

  1. I’ve updated my portrait that stares back at you on every single page. (Sorry about not being handsomer).
  2. I’ve added a new way to browse old articles … by illustration. It feels good to flex my HTML muscles again.
  3. The other thing is that I’ve reclaimed some whitespace. (But you won’t notice that).

The site is about as simple as I can make it. I had considered overhauling the whole thing, but with a couple small new changes, it feels new again.

And one more thing…

One non-blog related thing: the SQL Server Blocked Process Report Viewer is not in beta any more! I released version 1.0 this month. It now supports SQL Server 2005 to 2011 (so there you go Aaron C!)

July 20, 2011

ACID Properties By Example (And Counterexample) Part Zero

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , — Michael J. Swart @ 12:00 pm

ACID Properties By (Counter) Example

So I’m introducing a small series about ACID properties as it applies to databases. (For other acid properties, talk to a chemist or Timothy Leary).

When talking about databases, ACID is an acronym that stands for Atomic, Consistent, Isolation and Durable. These are important properties of a database system’s architecture. Specifically these properties refer to how database transactions are designed.

In fact this stuff is important in any transaction processing system (TPS). These systems (not just database systems) use a server-client architecture and they first became popular in the 1960s. These systems are successful because they allow multiple clients to modify and share data concurrently all while enforcing data integrity! Not too shabby.

So most servers (including database servers) were built with this architecture in mind. It’s interesting that NoSQL databases don’t attempt to provide ACID transactions. Each of these NoSQL databases ignore one or more of these properties and attempt to offer something else in its place (but that’s a story for another day).

With SQL Server, these properties are enforced by default. But as it happens, you can relax these ACID properties in SQL Server if you want. We’ll see that it turns out to be easy (maybe too easy?) to write SQL that ignores some of these properties. The hope is that after reading this series, you’ll

  • be aware of the properties
  • understand why database transactions behave the way they do,
  • and be aware of any consequences if you’re tempted to give up any of these properties.

How This Series Is Organized

So I started this series as a single blog post, but it was getting a bit long for a single article. I wanted to come up with some examples (and counterexamples) other than the too common example of a money transfer between two bank accounts.

What you’ll see in this series is

  • a description of each ACID property.
  • A bit about how each property is handled in SQL Server,
  • An example from real life (but not necessarily an I.T. example!)
  • A counterexample from real life (but again, not necessarily an I.T. example!)

Stay tuned!

July 6, 2011

Make Your Life Easier With Fun Denali Tricks

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , — Michael J. Swart @ 6:37 pm

I’ve given SQL Server Denali a spin and I have to say that I really like it. I want to demonstrate how I use some of the new features to make my life easier. How much easier?
The world needs more Olive Snook

The Scenario

Using the Adventureworks database, I want to perform the following:

SELECT top 30 ModifiedDate, rowguid, EmailAddress 
into myNewTable
from Person.EmailAddress

And I want to distribute a script to other databases so I can’t depend on the Person.EmailAddress table. So the two things I want to do in my script are:

  1. script the creation of the table and
  2. load the data.

SQL Server Denali actually makes this easy and I don’t even need to use SQL Server’s existing scripting features. Here’s how:

Using dm_exec_describe_first_resultset

First I use the new dynamic management function dm_exec_describe_first_resultset. Aaron Bertrand explains this feature well at his post SQL Server v.Next (Denali) : Metadata enhancements. You can use it to describe the columns of a query. This is perfect for what I need here and I base my script on the results of this query (modified a bit from Aaron’s script):

SET @sql = N'SELECT top 30 ModifiedDate, rowguid, EmailAddress 
from Person.EmailAddress
    CASE column_ordinal 
        WHEN 1 THEN '' ELSE ',' END 
        + name + ' ' + system_type_name + CASE is_nullable 
        WHEN 0 THEN ' not null' ELSE '' END
        @sql, NULL, 0
    ) AS f

But it’s really inconvenient to remember that syntax isn’t it? That’s why I like to use Code Snippets!

Code Snippets

Code snippets are something new in Denali. You may be familiar with SQL Snippets as offered by Mladen Prajdić using his SSMS Tools Pack. Having used both, I would say that Mladen’s SQL Snippets are much much easier to manage than Denali’s Code Snippets (at least as of CTP1). Denali does have code snippets that surround selected text though. I demonstrate with this snippet:

DescribeResultSet.snippet is a code snippet you can download and import into SSMS. It’s an alternative to remembering the syntax above. Here’s the way it works, it’s pretty simple. Select the query you’re curious about so that it’s highlighted. Then use the snippet to incorporate your query into a metadata query as shown in the code sample above.

Like I said, Snippets are pretty powerful, but SQL Server’s Code Snippets have still got a few quirks that will hopefully be ironed out by RTM:

  • For example according to the docs, the surrounds with shortcut key combo is supposed to be Ctrl+K Ctrl+S, but with CTP1 it’s not hooked up yet.
  • Writing snippets is a pain in the Denali, If you plan to write many snippets, stick with Mladen Prajdić’s SSMS Tools Pack

Okay, that’s great, now what about getting the data into the table?

Block Editing

Well that requires an Insert statement. That’s easy enough to write, but the VALUES clause is harder to write. It can be pretty finicky creating the literals. But SQL Server Denali runs on Visual Studio 2010! And that means we can use all the nifty tricks that VS2010 offers. One of those is enhanced Box Selection features. You select a rectangular box of text by hold down the alt key while dragging a mouse over a selection. Once you do that and start typing, the things you type will be inserted on each line of text.

This makes formatting blocks of data easy as pie. Thanks Denali!

Seeing The Whole Thing In Action

Great! That’s how to script data into a new table. And it’s so much simpler using Denali.

If you care to see it in action, I’ve got a screen capture showing what I mean. It’s got no audio (yet) or annotations.


Powered by WordPress