Nested Transactions | Voice of the DBA

One of the very common expectations from many SQL developers involves transactions. Many developers (database or application developers) think they can open a transaction, do something, open an inner transaction (nested), and then commit or rollback the inner transaction separate from the outer one.

If you’ve worked with explicit transactions and experimented with this a bit, then you know that this doesn’t work. Recently Brent Ozar wrote a post on this as he had a client think that committing the inner transaction would release locks. It doesn’t.

Knowing whether work gets committed or not is important to data integrity. We often need to ensure that multiple things happen or nothing happens. That’s key, and if we want to decide that thing A can happen without thing B, those are two transactions. In most cases, where we’d want the behavior I described at the top, these don’t need to be nested. They’re just two transactions.

Understanding how data modifications work is important, especially if you work across different platforms and you need to ensure there is some level of durability. Some platforms use different locking strategies, some limit transactions even more, and digging into the details is important.

As technical people, we know there are many ways to solve problems, and we often spend a lot of time ensuring that users of our systems have options. We would assume our users will learn and understand how the options work, which is no different that what we ought to do ourselves. Don’t assume. Ensure you know how the database will behave if you depend on it behaving a certain way.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

This entry was posted in Editorial and tagged sql server. Bookmark the permalink.