Erik Darling has started a new podcast:
In the inaugural episode, Joe Obbish and Erik Darling talk about how developers use and abuse transactions, and some surprises you might run into for them.
Click through for the video.
3 CommentsCategory: Transactions
Erik Darling continues a series on stored procedures:
Transactions and error handling often go hand-in-glove, to make better use of
XACT_ABORT, manually manage rollbacks, and try to make the best use of all those ACID properties that database people seem to care deeply about.
Click through for Erik’s advice around transaction handling.
Comments closedPaul White was assured that there would be no math:
This is all very familiar, of course, but it is also dumb. Why on earth should we have to worry about internal formulas? It seems ridiculous to have to provision or grow a transaction log in pieces just to get a reasonable VLF outcome.
Wouldn’t it be better to be able to specify a fixed size for VLFs instead?
Starting with SQL Server 2022, there is now a way though it is undocumented and unsupported for the time being at least.
Read on to see what that option is, how it works, and what the limitations are. Looking at the side effects, I’d say this is probably not something you want to jump on right now.
Comments closedBut anyway, you should avoid implicit transactions as much as possible. They most often show up in the Microsoft JDBC driver queries that only people who hate you use.
When I first started to realize how bad they are, I wrote a bunch of checks into the Blitz scripts (I’ll cover those next week) that check for them.
I also opened an issue to add the check to sp_WhoIsActive, because it’s rather popular, I hear.
Click through to see how you can track them now. Also, Erik has been showcasing community procedures like sp_WhoIsActive, sp_PressureDetector, sp_QuickieStore, and sp_HumanEvents all month and he’s doing great work there.
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 closedKevin 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 closedI 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 closedWe 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 closedJosh Darnell reminds us to check constructor defaults:
Despite the fact that the SQL Server default isolation level is
READ COMMITTED, the default isolation level when usingTransactionScopeisSERIALIZABLE.This problem has been around a long time. David Browne (Microsoft) wrote about it in checks watch 2010!
Read the whole thing.
Comments closedRyan 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