Running Out of INT

Uncommitted Data: the messy gym floor of databases

One of, if not the largest, pet peeves of mine when it comes to the gym is people who can't put away equipment when they're done. A plate is taken from rack near the benchpress all the way across the gym to the turf and then just left there for some other user to put away. The worst is what I encounter nearly daily. Kettlebells and dumbbells that are pulled from the rack and then left out in front of it! Why? Why are you unable to extend the tiniest bit of effort to return the item to the rack?

So this got me thinking this morning about how left out weights, bars, straps, etc. are much like transactions in our database that don't get "put away".  If you read one of my previous posts on indexing, we're going to stick with our weight rack/table structure example. Today we have our kettlebell rack where each shelf can represent an 8k data page within our table.

Shelf = 8k data page in table

The kettlebells here are our data. Let's see a user walks up and wants to update a value (do a movement for the purposes of this example). The weight is pulled from the shelf after the work was complete, it's just left on the floor. Waiting, patiently to be returned to whence it came. This is how a BEGIN TRAN statement works.

When you issue a BEGIN TRAN statement in your query, the database engine is going to acquire a lock on the table and not allow anything else to occur until it's either committed or rolled back. It's a safety mechanism in a way. Let's say you're going to update some data in your table. If you issue the following command with no BEGIN TRAN you got a problem!

UPDATE dbo.users

SET LastName = 'Smith'

What's wrong here? No WHERE clause. You just accidentally updated every record in your Users table to have the last name "Smith". If we'd slapped a BEGIN TRAN  in front of that, yeah, it would still update all the records in the table, but the data hasn't been hardened yet. In that example, we've run the script and realized quickly "Oh No! We just updated 10,000 records in stead of 1. What do we do?"

Issue a ROLLBACK command. That will undo your script and the data has been preserved. The table lock will also be released permitting SELECT and other statements to continue.

Conversely, if you run a COMMIT statement, now whatever changes you've made are written to the table and the lock has been released.

However, if a user runs a statement with BEGIN TRAN then they either walk away, or simply forget to commit the data or roll it back, it leaves us this:

Data just hanging out, waiting to be put back so someone else can use it. Ok, I know in the case of our gym example, anyone can come up and pick one up off the floor, but for the purposes of this reference point, hang with me.

You start getting a line of people wanting to use the kettlebells, but are unable to because someone didn't put them back. They began a movement with them and walked away or are just hanging out in front of the rack admiring their success! Still preventing new users from coming to get the data they want.

Couple of Gotchas

Here a couple of notes I want you to remember about BEGIN TRAN

• ROLLBACK is single threaded. This means that if you issue a gigantic DELETE or UPDATE modifying many rows and the primary operation goes parallel (use multiple CPU Cores) but you cancel and rollback, that operation only uses 1 CPU core. So your rollback could theoretically take longer than what you were attempting in the first place.
• If you issue multiple BEGIN TRAN statements in a single session, the @@Trancount will increment, but if you rollback or commit, it will do that for all open transactions in your session. Personally speaking, I prefer to keep my transactions to 1. And if I'm ever in doubt, run that SELECT @@Trancount to see how many I've got open.

The Bottom Line

BEGIN TRAN statements are a phenomenal way to protect yourself. How many times have I run some sort of update or delete query only to realize right away that I either forgot the Where clause or just flat out had something wrong. The BEGIN TRAN saved me! Roll that transaction back like it never happened.

Just like in the gym, when you're done with your data/weight, put it back in the table where it belongs so others can have a great experience!