August 5, 2019 at 2:26 pm
I do a lot of ad hoc update, insert, delete statements and was wondering if I should encapsulate using BEGIN... ROLLBACK... COMMIT to give me an out if something goes wrong like below:
BEGIN TRANSACTION
UPDATE B
SET expired = -1
FROM GCDF_DB..BillingThirdNotice B
INNER JOIN GCDF_DB..Certs C
ON B.PeopleID = C.PeopleID
WHERE C.certificationExpireDate < GETDATE()
AND B.billMonth = 3
AND B.processed = 0
--ROLLBACK TRANSACTION
COMMIT TRANSACTION
August 5, 2019 at 2:37 pm
As long as you don't wander off after BEGIN TRANSACTION and before COMMIT/ROLLBACK TRANSACTION and potentially cause an awful lot of blocking on that database :).
August 5, 2019 at 3:14 pm
I do a lot of ad hoc update, insert, delete statements and was wondering if I should encapsulate using BEGIN... ROLLBACK... COMMIT to give me an out if something goes wrong like below:
BEGIN TRANSACTION
UPDATE B
SET expired = -1
FROM GCDF_DB..BillingThirdNotice B
INNER JOIN GCDF_DB..Certs C
ON B.PeopleID = C.PeopleID
WHERE C.certificationExpireDate < GETDATE()
AND B.billMonth = 3
AND B.processed = 0
--ROLLBACK TRANSACTION
COMMIT TRANSACTION
If you're going to do it, I recommend using a variable/parameter to determine whether to ROLLBACK or COMMIT.
DECLARE @Testing BIT = 1;
BEGIN TRANSACTION;
UPDATE B
SET expired = -1
FROM GCDF_DB..BillingThirdNotice B
INNER JOIN GCDF_DB..Certs C
ON B.PeopleID = C.PeopleID
WHERE C.certificationExpireDate < GETDATE()
AND B.billMonth = 3
AND B.processed = 0;
IF @Testing = 1
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
I would also recommend that you start using semi-colons to terminate your statements.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 5, 2019 at 3:29 pm
For ad hoc stuff, I don't use a programmatic method to commit or rollback. Someone at one of the companies I used to work for did such a thing. There was actually a fault in SQL server that made the same mistake in the check-fo-commit that was made in the code and it took 40 people a week to undo all the damage it caused.
For ad hoc stuff and at the very least, I always check for a row count count prior to even thinking about doing an update. I have both commit and rollback commented out. If the manually derived rowcount and the additional check both agree, I'll double click on commit and press {f5}. If not, same thing on the rollback. It has saved my skin and the skin of others more times than I can count.
Some will complain that people forget to do a commit or rollback on long running stuff and the transaction blocks a lot of people. That certainly is a valid concern and does require some training of people that if you're running such a thing, you either need to schedule an outage or you need to actively watch the code and make the decision as soon as the run completes.
IF you're done a manual rowcount prior to the run and it's a long run, we have made provisions to automatically commit or rollback but we don't generally allow the system to programmatically determine what the row count should be. It always has to be hard coded in the code that's doing the update.
The fault that occurred (it was in SQL Server 2000 and had a hot fix for it) was that, under just the right conditions, the WHERE somecolumnname IS NULL was ignored both for the SELECT and the UPDATE.
The reason it took so long to recover was because that's when the DBAs discovered that there backups hadn't actually worked correctly for more than 3 weeks and the reporting system, which was a SAN snapshot of prod, had already gone through its nightly update process. Many, many lessons learned by that one. Fortunately, I wasn't a DBA back then.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply