March 8, 2021 at 6:05 pm
Hi,
Can you please let me know what is the benefit of using the 'SET IMPLICIT_TRANSACTIONS ON' in the below code when we already have 'BEGIN TRAIN' which can be used to rollback if any error occurs? So, I see IMPLICIT is also being used sometimes, not sure what is the benefit when we already have the BEGIN TRAN. Please let me know. Thanks!
SET IMPLICIT_TRANSACTIONS ON
BEGIN TRAN
--COMMIT
--ROLLBACK
GO
USE TESTDB;
Truncate table T1
Truncate table T2
March 8, 2021 at 9:21 pm
Basically, the IMPLICIT transaction being turned on means you are going to need 1 additional "COMMIT/ROLLBACK" in your statement. IMPLICIT TRANSACTIONS being turned on is like putting an extra "BEGIN TRANSACTION" at the start of each query.
https://database.guide/how-implicit-transactions-work-in-sql-server/
It could be useful if you want an overall transaction that covers the entire query, but you also want a few smaller transactions inside the query that get committed as you go. This can be useful if you are doing built-in error handling and don't need to roll back the entire query on failure I expect... it is not a practice I normally use. My approach is always the "all or nothing" approach. Either the entire query is committed or the entire thing is rolled back. But I can imagine there are some use cases where you'd only want to change some of the data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 10, 2021 at 5:31 am
This was removed by the editor as SPAM
March 10, 2021 at 5:32 am
This was removed by the editor as SPAM
March 10, 2021 at 6:02 pm
Hi,
Can you please let me know what is the benefit of using the 'SET IMPLICIT_TRANSACTIONS ON' in the below code when we already have 'BEGIN TRAIN' which can be used to rollback if any error occurs? So, I see IMPLICIT is also being used sometimes, not sure what is the benefit when we already have the BEGIN TRAN. Please let me know. Thanks!
SET IMPLICIT_TRANSACTIONS ON
BEGIN TRAN --COMMIT --ROLLBACK
GO USE TESTDB;
Truncate table T1 Truncate table T2
I'm betting that an Oracle developer wrote this originally. In SQL, you can execute an update statement and it will automatically commit (Explicit Transaction). There is no concept like this in Oracle.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 10, 2021 at 8:57 pm
I wouldn't expect to have to use two ROLLBACKS. I thought one ROLLBACK set @@TRANCOUNT to zero. (See below.)
In any event, I've never seen a need to use SET IMPLICIT _TRANSACTIONS ON.
IF OBJECT_ID(N'tempdb..#table') is not null DROP TABLE #table
declare @TC int
CREATE TABLE #table (SomeData varchar(20))
SET IMPLICIT_TRANSACTIONS ON
SELECT 'After Set Implicit, @@Trancount='+str(@@TranCount)
BEGIN TRAN
SELECT 'After Begin Tran, @@Trancount='+str(@@TranCount)
INSERT INTO #table
SELECT 'X'
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
set @TC = @@TRANCOUNT
SELECT 'After Rollback @@Trancount='+str(@TC)
union all
SELECT 'After Rollback #table has'+str(count(*))+' rows.'
FROM #table
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 10, 2021 at 9:54 pm
You are right there The Dixie Flatline... the ROLLBACK rolls back all transactions.
Not sure why I thought it only did the most recent one.
COMMITs happen per transaction though. So if you wanted to have multiple commits in your statement that would work I think. But something you would also need to watch for as you could end up with a bunch of long-running queries and blow up your transaction log.
Personally, I like the implicit transaction approach where COMMITs happen automagically for me.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 10, 2021 at 10:05 pm
You are right there The Dixie Flatline... the ROLLBACK rolls back all transactions.
Not sure why I thought it only did the most recent one.
COMMITs happen per transaction though. So if you wanted to have multiple commits in your statement that would work I think. But something you would also need to watch for as you could end up with a bunch of long-running queries and blow up your transaction log.
Personally, I like the implicit transaction approach where COMMITs happen automagically for me.
Commits do not happen per transaction. Nested transactions do not actually work.
Unless I am misunderstanding what you are saying
DROP TABLE dbo.Junk
CREATE TABLE dbo.Junk
(
Junk_ID int identity,
Junk_Value varchar(200)
)
GO
BEGIN TRAN
INSERT INTO dbo.Junk(Junk_Value)
VALUES ('First Begin')
COMMIT TRAN
SELECT * FROM dbo.Junk
BEGIN TRAN
UPDATE dbo.Junk
SET Junk_Value = 'Second Begin'
SELECT * FROM dbo.Junk
BEGIN TRAN
UPDATE dbo.Junk
SET Junk_Value = 'Third Begin'
COMMIT TRAN
SELECT * FROM dbo.Junk
BEGIN TRAN
UPDATE dbo.Junk
SET Junk_Value = 'Fourth Begin'
SELECT * FROM dbo.Junk
COMMIT TRAN
SELECT * FROM dbo.Junk
ROLLBACK TRAN
SELECT * FROM dbo.Junk
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 11, 2021 at 3:10 pm
I am just editing this message because the more you look the stranger things seem. I would have agreed with your comment about nested transactions. But look what happens when you COMMIT the BEGIN TRAN, followed by a ROLLBACK.
The COMMIT reduces @@TRANCOUNT by 1, but the ROLLBACK undoes the insertion of a row, despite the prior commit.
Apparently the BEGIN TRANSACTION/COMMIT was nested within the outer transaction defined by SET IMPLICIT_TRANSACTIONS ON/ROLLBACK. (Or a commit doesn't really count unless it reduces @@TRANCOUNT to zero. I need to re-read some Paul Randal.)
To avoid complicating the logic, I would just avoid any use of SET IMPLICIT TRASACTIONS ON.
IF OBJECT_ID(N'tempdb..#table') is not null DROP TABLE #table
declare @TC int
CREATE TABLE #table (SomeData varchar(20) primary key)
SET IMPLICIT_TRANSACTIONS ON
SELECT 'After Set Implicit, @@Trancount='+str(@@TranCount)
BEGIN TRAN
SELECT 'After Begin Tran, @@Trancount='+str(@@TranCount)
INSERT INTO #table
SELECT 'X'
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
set @TC = @@TRANCOUNT
SELECT 'After COMMIT @@Trancount='+str(@TC)
union all
SELECT 'After COMMIT #table has'+str(count(*))+' rows.'
FROM #table
if @@TranCount > 0
ROLLBACK
set @TC = @@TRANCOUNT
SELECT 'After ROLLBACK @@Trancount='+str(@TC)
union all
SELECT 'After ROLLBACK #table has'+str(count(*))+' rows.'
FROM #table
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply