Press "Enter" to skip to content

Category: Transactions

Distributed Transactions in T-SQL

Kevin Wilkie explains what distributed transactions are and why you probably don’t want to use them:

In the version of transactions that we going to discuss today, we’re going to discuss doing transactions on multiple servers!

A Distributed transaction is defined by HazelSet to be “a set of operations on data that is performed across two or more data repositories”. In even simpler terms, it’s a command run against data on more than one server.

Click through for the warnings about what might possibly go wrong.

Comments closed

Savepoints in Transactions

Kevin Wilkie continues a series on transactions in SQL Server:

All right, now that everyone’s back with us, we’ll talk more about everyone’s favorite – transactions. When they deal with transactions, most people only know how to begin one, then either commit it or roll it back. But there’s so much more you can do with a transaction!

This time I want to focus on savepoints for transactions. Yes, the same term you’ve been using in games for years can be used in the workplace!

I think I have actually made use of savepoints in production code…maybe twice? It always seems like whenever I might actually make use of one (rather than simply rolling it all back and starting over) that there’s some limitation which makes them not useful.

Comments closed

Transaction Modes in SQL Server

I have a video and blog post out:

What I want to do in today’s post is to cover the different sorts of transaction modes and get into the debate about whether you should use explicit transactions or rely on auto-committed transactions for data modification in SQL Server. This came from an interesting discussion at work, where some of the more recent database engineers were curious about our company policy around transaction modes and understanding the whys behind it. I didn’t come up with the policy, but my thinking isn’t too far off from the people who did.

But before I get too far off course, let’s briefly lay out some of the basics around transactions.

Read on for a good deal of info on the different transaction modes, including a bit on why implicit transactions (as opposed to autocommit transactions) are a bad thing in SQL Server.

Comments closed

Transaction Isolation Levels in SQL Server

Dan Jackson walks us through the different transaction isolation levels in SQL Server and what they mean for us:

We will start with a definition and then evolve it: the isolation level specifies how much one transaction must be protected from resource or data modifications made by other transactions.

Consider the case where user A is trying to read a list of products out of a table, meanwhile user B comes along and changes some of the product information in the table. As part of their same transaction, user A comes back to try and read the product table, but it has changed. Do you want user A to read the new information or not?

Isolation levels allow you to decide what would happen in scenarios like the one I’ve just described and so it should come as no surprise that they are described in terms of which concurrency side effects they allow.

Read on for a description of typically-undesirable side effects and the isolation levels which prevent them.

Comments closed

MSDTC and Availability Groups

Ryan Adams provides guidance on using distributed transactions against Availability Groups:

A paramount concept to understand is how to make the DTC highly available.  We can see from the precedence order that SQL Server will use the local DTC out of the box.  This makes it appear that everything is working, and it is, but it is not exactly highly available.

I see a lot of customers leave it configured this way because they either don’t know the ramifications or do not realize they are using the MSDTC (Linked Servers). Since it simply works out of the box, things get left this way until they end up with a suspect database and error messages that look like this:

“SQL Server detected a DTC/KTM in-doubt transaction with UOW  {598B7EDD-F7A1-9DC1-8D3E-303A4C93AAB4}.Please resolve it following the guideline for Troubleshooting DTC Transactions.”

Read the whole thing. There are a lot of small areas between processes where things can fail, and the combination of DTC + AGs is no different.

Comments closed

Dealing with a Massive Transaction Log File

Kevin Hill takes us through troubleshooting an oversized transaction log file:

I had a customer call in this week after struggling to resolve an issue for a couple of days,  Their Transaction Log file had grown to 400+GB and was filling the drive to the point that had to keep adding space to the drive to keep operations online.  This was for a core internal system.  There are no staff DBAs here, but plenty of very sharp sysadmins, developers, network folks, etc.

Note: I have written about the most common cause of this issue here, but this was not the resolution in this case. The embedded video is far and away number one in hits of all my SQL videos, showing just how common log file growth issues are.

I like the way Kevin does this. He frames the story, takes you through his actions, and gives you a chance to understand his troubleshooting process. Most problems become an order of magnitude easier to solve if you have a reasoned-through process (and enough practice to follow when three levels of management are staring at your screen during a crisis).

1 Comment

Distributed Transactions on Linux

Tejas Shah and crew announce distributed transactions with SQL Server on Linux:

With SQL Server 2017, a new era was heralded with SQL server being available to deploy on Linux (and Linux based container) systems. While all functionality of the SQL Server engine were brought over as is to SQL Server on Linux, some of the functionality which depended on Windows system processes such as distributed transactions (which relies on MSDTC service) were not brought over immediately.

Well, now your wait is over.

Comments closed

When the Transaction Begins

Josh Darnell points out that transactions don’t really begin with BEGIN TRAN:

Of course, no one reads the documentation. And even if they do, they certainly don’t continue on to the 4th paragraph of the “General Remarks” section. That’s like going to the second page of Google search results.

If one did trudge on through the docs, they would find this gem:

Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement. An application can perform actions such as acquiring locks to protect the transaction isolation level of SELECT statements, but nothing is recorded in the log until the application performs a modification action.

Well that’s interesting! Let’s check it out.

Four paragraphs? Where’s the tl;dr, Microsoft Docs?

Read the whole thing even though Josh just made a joke about nobody reading the whole thing and I followed along with it.

Comments closed

SQL Server Isolation Levels

Pamela M. takes us through a humorous journey covering transaction isolation levels in SQL Server:

READ COMMITTED:  One step up (and the default for SQL Server).    A query in the current transaction can’t read data modified by someone else that hasn’t yet committed.  No dirty reads.  BUT….data could be changed by others between statements in the current transaction, so the data may not look the same twice.  READ COMMITTED uses shared locks to prevent dirty reads, but that’s about all you get.  You still get non-repeatable reads and phantom reads here (more on phantom reads below).

Click through for the full list.

Comments closed