TRY/CATCH, XACT_ABORT, rollback behavior, and transaction outcomes.
19 associated posts18 primary posts
If you're using TRY/CATCH to do exception handling in T-SQL, you need to be aware that there are a lot of things it doesn't catch. Here's a quick example.
Let's set up two tables - bookmarks, and a process log to track whether our stored proc is working or not:
Come join me in my cabin aboard the Hurtigruten Nordlys, sailing up the coast of Norway, and we'll cover your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/E_JNbimkUb0
HONK HONK! It's time for a fast round of answers to concise questions y'all posted and upvoted at https://pollgab.com/room/brento.
https://youtu.be/DhTqXJ1fTBw
To find out, let's set up a simple status log table:
[crayon-6a6eb81b6de22460642823/]
And then let's try a two-part transaction:
[crayon-6a6eb81b6de2b353364948/]
Right now, SQL Server shows that I have 2 open transactions:
What Happens If I Roll Back?
But what does "2 open transactions" mean, really? If I do a rollback, what gets rolled back? Let's find out:
[crayon-6a6eb81b6de2d836696877/]
The results:
I hopped down to Cabo San Lucas and sat at the End of the World to answer the questions you posted & upvoted at https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=KnhFkaCVv_8
Anybody can write a stored procedure with a little help from Google. This session is about how to write stored procedures that have a high likelihood of performing well and are easy to troubleshoot.
This fast-paced, all-demo session from SQLBits will NOT cover how to write a query, syntax, or performance tuning. This is about good best practices after you've written the first one - things like how to catch errors, how to pass in multiple values, how to debug without the debugger, and more.
This week's series on error handling has been an eye opener for many of us. We've laughed. We've cried. We've screamed in horror. We've read the documentation. I don't blame you. This is a really confusing topic, and for many of us, it's the first time we've actually looked at the mechanics of how transactions,…
CAN YOU BELIEVE THAT HOT MESS IN YESTERDAY'S POST?!?
I know, right? You thought that by combining try/catch with a transaction, you'd get robust error handling. Instead, you ended up with half the tables populated, and a leftover open transaction. You'd already forgotten Tuesday's post in which I pointed out that TRY/CATCH doesn't catch low severity or high severity errors.
In yesterday's epic cliffhanger, you might have been shocked to discover that a plain ol' transaction does not solve our problem - and in fact, it makes things worse. With yesterday's code, we still got rows inserted into the Parent table, no rows in the Child table, and a nasty surprise that our session will have to deal with down the road.
In yesterday's post, we tried a TRY/CATCH, and those of us who actually ran the demo scripts were disappointed. We found that a TRY/CATCH alone doesn't fix it: we end up getting rows in the Parent table, but no rows in the Child table. It turns out there are some gotchas in Books Online's summary of…
Let's say we have two tables, Parent and Child, and we need to guarantee that they both get populated at once. We'll write a single stored procedure to do both inserts: [crayon-6a6eb81b6f137572901030/] I put a WAITFOR in there, but that isn't the problem - I'm just using that to demonstrate why the code isn't production-ready.…
David Tovee asked a great question in yesterday's Mastering Query Tuning class. He asked his fellow students, "How many of you actually use TRY/CATCH?"
I turned it into a Twitter poll because I wanted to check a wider audience:
You've got a staging table with millions of rows, and you want to join that over to a production table and update the contents. However, when you try to do it all in one big statement, you end up with lock escalation, large transaction log usage, slow replication to Availability Groups, and angry users with pitchforks gathered in the Zoom lobby.
Do table variables and variables respect transactions? If I set the value of a variable during a transaction, and I roll it back, what happens?
[crayon-6a6eb81b7036e562031448/]
To fix this error, patch to SQL Server 2016 SP2, CU6 or newer (including SQL Server 2017), and then turn on trace flag 460. You can enable it at the query level or at the server level.
First, let's see the error happen: let's create a table with small fields, and then try to insert more data than it holds.
[crayon-6a6eb81b70fcc962447831/]
Baby's car is longer than 20 characters, so when the insert statement runs, we get an error:
[crayon-6a6eb81b70fd4889574488/]
Hey Beavis
That's the first time I've used "annals" correctly. I will be 40 sooner than later.
Sometimes when we blog, we get inspiration from clients, students, other bloggers, questions on Stack Exchange, nightmares, or just drinking heavily.
We were once asked in class what TRY/CATCH doesn't handle besides object existence errors.
It's well documented in Books Online (BOL). If you're like me, then tl;dr. Are we even calling it Books Online these days? I still say "bookmark lookup" instead of "key lookup". I suppose I'll be saying Books Online for quite some time too. At least these days it really is online.
It’s Friday afternoon. You, the DBA, are looking forward to a long weekend. You’re going to walk out the door in 30 minutes and enjoy some family time. The phone rings and you pick it up. On the other end, Joe Developer says, “There’s a query that’s been running for a long time. Can you kill it?”
We work with SQL Server every day, happily sending and receiving millions of packets of data across the network. Have you stopped to think about what happens when something goes wrong?
The Situation
