Monthly Archives: December 2014

Transaction Isolation Level Blues

Have you ever had a mental block in one particular area when learning something? It might be the simplest thing, but for some reason your brain turns to Teflon when you try to store the information. For example, I have a degree in Math, so I am pretty good at arithmetic, but for the life of me I cannot remember what eight plus five is. I always have to break out my phalanges to get the answer.  Why the Hell can I remember what phalanges means and not a simple thing like eight plus five?!

I have this same problem when it comes to Transaction Isolation Levels in SQL Server. I can remember that there are five of them, Read Uncommitted, Read Committed, Repeatable Read, Snapshot & Serializable, but I cannot remember the little nuances that set them apart. It’s total Teflon. So I decided it was time to come up with a little song to help me remember. My older sister is a preschool teacher and she says that if you learn something as a song, it sticks with you for life. Here’s hoping that is true!

This is sung to the tune of George Thorogood’s Bad to the Bone.

At the time I am used
No Shared locks are issued
Not blocked by X locks
It is Loosey-Goosey
Just call me crazy
No restrictions abound
I could tell right away
It was Read Uncommitted

Bad to the bone
Bad to the bone
B-B-B-B-Bad
B-B-B-B-Bad
B-B-B-B-Bad
Bad to the bone

Not breakin’ any rules
Going by the book
Not readin’ any uncommitted
Transactions it’s true
I am the default baby
Transactions alone
I’m Read Committed
That’s what I do

Bad to the bone
B-B-B-Bad
B-B-B-Bad
B-B-B-Bad
Bad to the bone

No readin’ ’til committed
Can’t read dirty data either
I use shared locks baby
And hold ’til committed
I’m the repeatable read baby
Yours and yours alone
Data’s all yours honey
And I’m bad to the bone

B-B-B-B-Bad
B-B-B-B-Bad
B-B-B-B-Bad
Bad to the bone

When I query data
Kings and Queens step aside
Every bit I meet
It’s mine it’s all mine
Serializable baby
Range blocks on keys that’s me
HOLDLOCK does the same thing baby
Serializable oo-ee

Bad to the bone
B-B-B-B-Bad
B-B-B-Bad
B-B-B-Bad
Bad to the bone

(Extra verse)
There’s no write blockin’
While I’m readin’
No locks less I’m recoverin’
You can’t switch to me
But I can switch to you
I’m a snapshot baby
A photo just for you

Bad to the bone
B-B-B-B-Bad
B-B-B-Bad
B-B-B-Bad
Bad to the bone

If your brain is Teflon when it comes to Transaction Isolation Levels, then I hope this helps. If not, I hope you got a good laugh and please don’t tell George Thorogood what I did to one of his best songs (and one of my favorites).

By the way, eight plus five is .. thirteen.

Giving Back T-SQL Tuesday #61

First off I wanted to thank Wayne Sheffield (Twitter | Blog) for hosting this month’s T-SQL Tuesday party and Adam Machanic (Twitter | Blog) for starting this party five years ago. I can’t believe it’s been five years.

TSQL2sDay150x150

This month’s theme is Giving Back to the SQL Community.

This is a great topic, it’s kind of like the Dickens’ holiday classic, A Christmas Carol. It gives me an opportunity to reflect on what I have done in the past, what I am doing now and what more I could be doing to give back to the community that has helped me so much in my career.

Past Giving

Member of the Abstract Review Committee (2010-2014)
Member of the Nomination Committee (2012)
“Speaker Wrangler” for Triad SQL, the local PASS chapter in Greensboro, NC (2012-2014)
PASS Ambassador at Summit (2010-2014)
24 Hours of PASS moderator (2012-2014)
PASS Summit Buddy (2013)
Chosen as a mentor in Steve Jones’ and Andy Warren’s The Mentoring Experiment (2012)

Present Giving

“Speaker/Sponsor/Swag Wrangler” for Triad SQL, the local PASS chapter in Greensboro, NC
Program Manager with the Program Committee for the PASS Summit.

Future Giving

I will continue my work (as long as they will have me) with the Program Committee. I absolutely love this volunteer position, it allows me to do something I enjoy while helping the community. It’s a win-win. I will also continue my position with Triad SQL. I have stepped it up a notch by wrangling not only speakers but sponsors and swag as well for 2015.

I enjoy moderating the 24 Hours of PASS too. It always gives me an opportunity to connect with some of the speakers that I’ve never had interaction with before. Just this year, I moderated for Gail Shaw (Twitter | Blog) and then got to meet her in person at Red Gate’s SQL in the City event in Seattle just before the Summit. How cool is that?!

I have a blog, in fact you’re reading it right now (wink wink), but I don’t write nearly enough. Part of me feels, “Surely someone has written about this before, so why should I clutter up cyberspace with my drivel?” and the other part of me feels, “Maybe my post can help someone who couldn’t quite make heads or tails of something they found via Google.” So for next year, I am setting a goal to write/blog at least once a month. I bet T-SQL Tuesday can provide the subject matter for those months where nothing interesting happens at work.

My favorite way, by far, to give back to the SQL Community is by being a PASS Ambassador during the PASS Summit. I get to see all the eager faces ready to stuff their brains to the point of overflowing. I get to help someone find the registration desk so their adventure can begin. I also get some of the first hugs of the Summit just by smiling and answering questions. It really is the best. So, as long as this program exists and I’m attending the Summit, I will continue to be a PASS Ambassador.

Lastly, I may even try speaking this year.  This is a huge deal for me, so I make no promises, other than to think about speaking.

How are you giving back?