A Short PSA On Transaction Count, ROLLBACK, and COMMIT In SQL Server

A Short PSA On Transaction Count, ROLLBACK, and COMMIT In SQL Server

Video Summary

In this video, I, Erik Darling from Darling Data, wanted to share a quick public service announcement about a common misconception in SQL Server coding practices. I noticed an instance where a developer had used a `WHILE` loop for transaction management, which led to confusion and unnecessary complexity. In reality, the code could have been simplified using an `IF` statement. The video demonstrates how, when you start 1000 transactions but only need one rollback, it’s more efficient and clearer to use a simple `IF` condition instead of a nested `WHILE`. By showing both scenarios—using a `ROLLBACK` within the loop and moving the logic outside—the video highlights the importance of understanding SQL Server’s transaction handling. Whether you’re a seasoned developer or just starting out, this example serves as a reminder to keep your code simple and effective.

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we are going to do a very short PSA on an important difference. That’s a public service announcement. We are not going to be testing our prostate hormone chemical levels. That’s a different channel. Because I saw this the other day, working with some client code. And I looked at it and I said, huh, what happened to you? Where you would feel the need to do this? Now, it was a bit of a developer misconception. This while would have been perfectly fine as an if. Because what happens is SQL Server doesn’t really support nested transactions in this way. Let’s just run the demo. So if I run this, we’re going to begin a transaction 1000 times. And then here we’re going to say, while the transaction count is greater than zero, we’re going to print the current transaction count. And then we’re going to roll back a transaction. When we do this, note that we only print the number 1000. You only need one rollback.

If we’re going to print the current transaction count. If we were to change this to say, commit. Then we would end up with results that look like this. Now notice for commit, we do de increment, decrement. I’m not sure what the right word is there. The transaction count by one, every time we issue a commit. And if I had the print under the commit transaction, you would see this zero, of course. But when you begin a transaction, you do need to pair that with a commit transaction. If you begin a transaction, even if you begin 1000 transactions, you only need to roll back once. So if you’re ever curious out there, maybe you’re not sure what to do with rollbacks and commits. Maybe you stumble upon some code like that, that really should have just been if transcontent. If transcount is greater than zero, we will roll back our transaction. Maybe you’re working with the same people I did. And they just haven’t fixed all that code yet. Tough to say. Anyway.

Thank you for watching. I hope you enjoyed yourselves. Technically, I hope you didn’t learn anything. Because you would be a dangerous human being if you learned something here. But anyway. Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.