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 what TRY/CATCH does:
A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.
The devil is in the details. If you scroll further down in the documentation – look, I’m not saying you’re actually going to do that, because we both know damn well that you don’t read the documentation – there are gotchas like:
TRY…CATCH constructs do not trap the following conditions:
- Warnings or informational messages that have a severity of 10 or lower.
- Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
- Attentions, such as client-interrupt requests or broken client connections.
- When the session is ended by a system administrator by using the KILL statement.
So to recap, TRY/CATCH doesn’t catch small problems or big problems – only medium-sized problems.
Okay, that’s a bit of a turkey.
In our scenario this week, I’m causing the error by canceling the query (a client-interrupt request) – but no, we can’t just say, “Never abort your queries.” I’m pro-choice: people deserve the right to end their queries at any stage of their development. I also believe in the death penalty: DBAs deserve the right to kill your queries when they’re causing terrible harm to society at large.
The business is asking us to make sure that the data gets into both the Parent and Child tables, or neither. We’re gonna need something more powerful than TRY/CATCH.
Let’s try a transaction instead.
Microsoft’s documentation on SQL Server transactions starts out with the most important stuff:
A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
That, not try/catch, is probably a better fit for what we’re trying to do. Let’s frame our two insert statements with transactional code:
DROP TABLE IF EXISTS dbo.Parent;
DROP TABLE IF EXISTS dbo.Child;
CREATE TABLE dbo.Parent (ID INT IDENTITY(1,1), DateAdded DATETIME2);
CREATE TABLE dbo.Child (ID INT IDENTITY(1,1), DateAdded DATETIME2);
CREATE OR ALTER PROC dbo.Insert_Transaction AS
BEGIN TRANSACTION; /* THIS IS NEW */
INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
WAITFOR DELAY '00:00:30';
INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());
COMMIT TRANSACTION; /* THIS IS NEW */
And as before, run the proc, wait a few seconds, cancel it, and then see what we’ve got:
/* Let that run for a few seconds, then cancel it. Then: */
SELECT * FROM dbo.Parent;
SELECT * FROM dbo.Child;
As with yesterday’s demo, I’m not going to show you the results here. Instead, guess what’ll happen before you run it:
- We’ll have no new rows in either Parent or Child, or
- We’ll have a new row in Parent, but not Child, or
- We’ll have new rows in both Parent and Child
And then run it yourself to see whether your hunch was correct. Just as a reminder, only do these experimentations in a development environment, not production, especially if you happen to have RCSI or SI enabled in production.
To understand what’s happening and why, check out the next post in this series.