August 1, 2017 at 4:54 pm
My goal here is i delete only data that is inserted, if i only inserted and not able to delete for some reason i should rollback and if insert fails then it shouldn't insert anything at all. Which one is better in your implementations.
Begin Tran
Insert into DB1.dbo.table1 ( column1)
select column1 from Db2.dbo.table2
where column1='a'
Delete from Db2.dbo.table2
where column1='a'
Commit Tran
Begin Try
Insert into DB1.dbo.table1 ( column1)
select column1 from Db2.dbo.table2
where column1='a'
Delete from Db2.dbo.table2
where column1='a'
End Try
Begin Catch
if @@transaction > 0
Update logging table with error messages
Rollback tran
End Catch
August 1, 2017 at 6:12 pm
Transactions:
BEGIN TRAN
COMMIT TRAN /* ROLLBACK */
Error handling:
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
Used together:
BEGIN TRY
BEGIN TRAN;
INSERT INTO DB1.dbo.table1 ( column1)
SELECT column1 FROM Db2.dbo.table2
WHERE column1='a';
COMMIT TRAN;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
ROLLBACK TRAN;
END CATCH
August 2, 2017 at 5:53 am
Joe Torre - Tuesday, August 1, 2017 6:12 PMTransactions:
BEGIN TRANCOMMIT TRAN /* ROLLBACK */
Error handling:
BEGIN TRYEND TRY
BEGIN CATCHEND CATCH
Used together:
BEGIN TRY
BEGIN TRAN;
INSERT INTO DB1.dbo.table1 ( column1)
SELECT column1 FROM Db2.dbo.table2
WHERE column1='a';
COMMIT TRAN;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
ROLLBACK TRAN;
END CATCH
Major kudos for one of the best, most succinct and to the point while still being dead on and accurate answers I've seen in a long time. Absolutely made my day. Thanks.
Heck, I'll even do an emoji for this one (and I hate those things): 😀
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 2, 2017 at 6:18 am
curious_sqldba - Tuesday, August 1, 2017 4:54 PMWhich one is better in your implementations.
Neither. They're both inadequate for your requirement, and the second one will throw an error occasionally (ROLLBACK TRANSACTION has no corresponding BEGIN TRANSACTION)
http://www.sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2017 at 9:34 am
What Gail said.
These are separate constructs. Transactions ensure that code is all run together or none of it. If you have a single update, there's a transaction for that one statement. If you have more than one thing that needs to be grouped, use a transaction.
TRY..CATCH is for error handling.
August 2, 2017 at 11:03 am
I will need both, should be able to rollback and log an error if it fails. Looks like Torres solution might work, where i have begin tran wrapper under begin try.
August 2, 2017 at 12:18 pm
curious_sqldba - Wednesday, August 2, 2017 11:03 AMI will need both, should be able to rollback and log an error if it fails. Looks like Torres solution might work, where i have begin tran wrapper under begin try.
If you are logging to a SQL table be sure to log the error after the rollback or you won't log the error to the error table.
August 2, 2017 at 1:32 pm
This is really what i am trying to do, should do following:
i) Any error on Insert - Should rollback insert , not execute delete & log the error
ii) Any error on delete - should rollback insert&delete & log the error
I think this should work, any better way to handle this. Thanks in advance :).
WHILE 1 = 1
BEGIN
SET xact_abort, nocount ON --Added this for better error handling
IF OBJECT_ID('tempdb..#T1') IS NOT NULL DROP TABLE #T1
SELECT top 1000 a.col1,
a.col2
Into #T1
FROM DB2.dbo.Table1 a
JOIN DB1.dbo.Table2 c
ON a.Col1= c.col1
AND a.col2=c.col2
Begin Transaction
BEGIN try
SET TRANSACTION isolation level READ uncommitted -- This is fine for me, need this to avoid blocking
INSERT INTO DB1.dbo.Table1
(
a.col1,a.col2
)
SELECT a.col1,a.col2
FROM DB2.dbo.Table1 a
Join #T1 b
on a.col1=b.col1
and a.col2=b.col2
DELETE FROM a
FROM DB2.dbo.Table1
a
JOIN #T1 b
ON a.col1=b.col1
and a.col2=b.col2
Commit transaction
END try BEGIN catch
UPDATE DB3.dbo.ErrorLogging
SET errmsg= Error_message(),
severity = Error_severity(),
state = Error_state(),
errornumber = Error_number(),
session_id=@@spid
WHERE id = 15
RETURN;
END catch
IF @@ROWCOUNT = 0 BREAK;
END;
August 2, 2017 at 3:16 pm
There's no point in setting the isolation level. You have no select queries, so there's nothing for it to affect. Data modifications always take exclusive locks.
You don't need a try-catch block if you have XACT_ABORT on, and if you want to handle errors yourself (logging table), you don't want XACT_ABORT on. See the blog post I referenced earlier for how XACT_ABORT works.
You don't have a rollback in the catch. If either the insert or delete throw errors, the procedure will exit with an uncommitted transaction
See the blog post I mentioned earlier for some details on how and what you need to do.
The @@rowcount will ALWAYS be 0 at the point you check it, as it's the row count of the previous statement, and the only way that execution could get to that point is if the TRY block was exited successfully, meaning the last statement was COMMIT TRANSACTION, and that affects no rows, hence @@Rowcount will be 0.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2017 at 3:39 pm
GilaMonster - Wednesday, August 2, 2017 3:16 PMThere's no point in setting the isolation level. You have no select queries, so there's nothing for it to affect. Data modifications always take exclusive locks.You don't need a try-catch block if you have XACT_ABORT on, and if you want to handle errors yourself (logging table), you don't want XACT_ABORT on. See the blog post I referenced earlier for how XACT_ABORT works.
You don't have a rollback in the catch. If either the insert or delete throw errors, the procedure will exit with an uncommitted transaction
See the blog post I mentioned earlier for some details on how and what you need to do.The @@rowcount will ALWAYS be 0 at the point you check it, as it's the row count of the previous statement, and the only way that execution could get to that point is if the TRY block was exited successfully, meaning the last statement was COMMIT TRANSACTION, and that affects no rows, hence @@Rowcount will be 0.
I guess if try/catch is taking care of error handling and rollback why do i need begin tran?
There is a select to insert the data and hence is the SET option
Declare @rowcount int
WHILE 1 = 1
BEGIN
SET nocount ON --Added this for better error handling
IF OBJECT_ID('tempdb..#T1') IS NOT NULL DROP TABLE #T1
SELECT top 1000 a.col1,
a.col2
Into #T1
FROM DB2.dbo.Table1 a
JOIN DB1.dbo.Table2 c
ON a.Col1= c.col1
AND a.col2=c.col2
Begin Transaction
BEGIN try
SET TRANSACTION isolation level READ uncommitted -- This is fine for me, need this to avoid blocking
INSERT INTO DB1.dbo.Table1
(
a.col1,a.col2
)
SELECT a.col1,a.col2
FROM DB2.dbo.Table1 a
Join #T1 b
on a.col1=b.col1
and a.col2=b.col2 DELETE FROM a
FROM DB2.dbo.Table1
a
JOIN #T1 b
ON a.col1=b.col1
and a.col2=b.col2
select @rowcount=@@ROWCOUNT
Commit transaction
END try
BEGIN catch
UPDATE DB3.dbo.ErrorLogging
SET errmsg= Error_message(),
severity = Error_severity(),
state = Error_state(),
errornumber = Error_number(),
session_id=@@spid
WHERE id = 15
Rollback transaction
RETURN;
END catch
IF @ROWCOUNT = 0 BREAK;
END;
August 2, 2017 at 3:50 pm
Yes, there is a SELECT as part of the INSERT. Locks will be taken to avoid potential data changes to the data being selected.
August 3, 2017 at 2:31 am
curious_sqldba - Wednesday, August 2, 2017 3:39 PMI guess if try/catch is taking care of error handling and rollback why do i need begin tran?
Because you can't rollback without having a transaction.
Try + catch = error handling.
Begin transaction + commit/rollback = transaction, as Joe explained earlier, and as my blog post explains
You want to put the insert into the logging table after the rollback (as mentioned in the referenced blog post), otherwise it will also be rolled back
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2017 at 8:52 am
Joe Torre - Tuesday, August 1, 2017 6:12 PMTransactions:
BEGIN TRANCOMMIT TRAN /* ROLLBACK */
Error handling:
BEGIN TRYEND TRY
BEGIN CATCHEND CATCH
Used together:
BEGIN TRY
BEGIN TRAN;
INSERT INTO DB1.dbo.table1 ( column1)
SELECT column1 FROM Db2.dbo.table2
WHERE column1='a';
COMMIT TRAN;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
ROLLBACK TRAN;
END CATCH
Joe shouldn't this be : . ? Thanks
Begin Tran
Begin Try
Insert
select
delete
Commit Tran
End Try
Begin Catch
Rollback Tran
Update error logging
End Catch
August 3, 2017 at 9:02 am
curious_sqldba - Thursday, August 3, 2017 8:52 AMJoe Torre - Tuesday, August 1, 2017 6:12 PMTransactions:
BEGIN TRANCOMMIT TRAN /* ROLLBACK */
Error handling:
BEGIN TRYEND TRY
BEGIN CATCHEND CATCH
Used together:
BEGIN TRY
BEGIN TRAN;
INSERT INTO DB1.dbo.table1 ( column1)
SELECT column1 FROM Db2.dbo.table2
WHERE column1='a';
COMMIT TRAN;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
ROLLBACK TRAN;
END CATCHJoe shouldn't this be : . ? Thanks
Begin Tran
Begin Try
Insert
select
delete
Commit Tran
End TryBegin Catch
Rollback Tran
Update error logging
End Catch
Joe wasn't updating a logging table, so it didn't matter in his example that the rollback was after the select.
Switch the order of your begin transaction and begin try. It doesn't make a behavioural change, but it's easier to read if things are nested consistently
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2017 at 3:25 pm
I was explaining the two different concepts from a T-SQL perspective, transactions and error handling. If I want to add an address for a new vendor only if i subsequently add the vendor to the vendor table, this is a case I can put both in a transaction. They will commit together or are rolled back together. Transactions are used to support atomicity in a logical number of steps allowing to program what happens on a case by case in each step based on error and the particular error.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply