Transaction Not commiting when run on wrong DB

Question

Transaction Not commiting when run on wrong DB

Rechana Rajan

SSCertifiable

Points: 7721
More actions
September 28, 2016 at 6:25 am

#400794

The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for below
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246991 More actions · Answer 1

Rechana Rajan (9/28/2016)
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for below
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH

OK. What is your question, exactly?

Please post the full text of the error message, too.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 2

Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for below
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
OK. What is your question, exactly?
Please post the full text of the error message, too.

Thanks Phil for the reply. What to know why the transaction is not commiting . Got below error while executing but the transaction will be in uncommited state.

Msg 208, Level 16, State 1, Line 3

Invalid object name 'dbo.test'.

Phil Parkin SSC Guru Points: 246991 More actions · Answer 3

Rechana Rajan (9/28/2016)
Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for below
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
OK. What is your question, exactly?
Please post the full text of the error message, too.
Thanks Phil for the reply. What to know why the transaction is not commiting . Got below error while executing but the transaction will be in uncommited state.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.test'.

This looks like a parse-time error - I doubt that the code even ran (and therefore, the code in the CATCH block would not have been executed).

One extra level of protection you can add to your code (before the BEGIN TRY) is

SET XACT_ABORT ON;

It will greatly reduce the chance that your code leaves open transactions hanging around.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 4

Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for below
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
OK. What is your question, exactly?
Please post the full text of the error message, too.
Thanks Phil for the reply. What to know why the transaction is not commiting . Got below error while executing but the transaction will be in uncommited state.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.test'.
This looks like a parse-time error - I doubt that the code even ran (and therefore, the code in the CATCH block would not have been executed).
One extra level of protection you can add to your code (before the BEGIN TRY) is
SET XACT_ABORT ON;
It will greatly reduce the chance that your code leaves open transactions hanging around.

Thanks Phil. It would be great if I can get the reason why its in uncommited state so that i can share the reason with Devs.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

It's a parse error and try-catch cannot catch parse errors.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phil Parkin SSC Guru Points: 246991 More actions · Answer 6

Rechana Rajan (9/28/2016)
Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for below
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
OK. What is your question, exactly?
Please post the full text of the error message, too.
Thanks Phil for the reply. What to know why the transaction is not commiting . Got below error while executing but the transaction will be in uncommited state.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.test'.
This looks like a parse-time error - I doubt that the code even ran (and therefore, the code in the CATCH block would not have been executed).
One extra level of protection you can add to your code (before the BEGIN TRY) is
SET XACT_ABORT ON;
It will greatly reduce the chance that your code leaves open transactions hanging around.
Thanks Phil. It would be great if I can get the reason why its in uncommited state so that i can share the reason with Devs.

It is not. The code does not even run.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Phil Parkin (9/28/2016)
The code does not even run.

It does (try it, you'll end up with @@TRancount of 1), because of deferred compile.

When the batch is parsed, the table isn't there so its compilation is deferred until the first statement that refers to the table, that first parse doesn't throw an error (otherwise you could never create and use temp tables in the same batch). The Begin Try and Begin Transaction then execute, the table is referred to so the deferred compilation happens, the table still doesn't exist and so a parse error is thrown at that point, with an open transaction.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phil Parkin SSC Guru Points: 246991 More actions · Answer 8

GilaMonster (9/28/2016)
Phil Parkin (9/28/2016)
The code does not even run.
It does (try it, you'll end up with @@TRancount of 1), because of deferred compile.
When the batch is parsed, the table isn't there so its compilation is deferred until the first statement that refers to the table, that first parse doesn't throw an error (otherwise you could never create and use temp tables in the same batch). The Begin Try and Begin Transaction then execute, the table is referred to so the deferred compilation happens, the table still doesn't exist and so a parse error is thrown at that point, with an open transaction.

Thanks, Gail, for the detailed explanation.

I did try it, and @@TRANCOUNT was zero afterwards.

So I checked Tools/Options/Query Execution/SQL Server/Advanced

and see that I have

SET XACT_ABORT

checked ... so that was doing the sweeping up for me.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

joeroshan SSChampion Points: 10377 More actions · Answer 9

I just did a quick check myself, running the query (Of course id do not have table) and strangely found a sleeping session with open transaction.

I ran the following query in a different window to check

Select open_transaction_count, * from sys.dm_exec_sessions where session_id=(my session id)

However I think this should be harmless since no real locks are held.

I found that Phil's suggestion of using XACT_ABORT ON resolves this issue. Its a strange behavior though.

Edit: Didn't see Gail's explanation. Makes sense. Thanks

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Phil Parkin (9/28/2016)
So I checked Tools/Options/Query Execution/SQL Server/Advanced
and see that I have
SET XACT_ABORT
checked ... so that was doing the sweeping up for me.

You have the latest SSMS 2016? XACT_ABORT has been turned on by default, in the last few releases, which is a change I really don't agree with.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phil Parkin SSC Guru Points: 246991 More actions · Answer 11

GilaMonster (9/28/2016)
Phil Parkin (9/28/2016)
So I checked Tools/Options/Query Execution/SQL Server/Advanced
and see that I have
SET XACT_ABORT
checked ... so that was doing the sweeping up for me.
You have the latest SSMS 2016? XACT_ABORT has been turned on by default, in the last few releases, which is a change I really don't agree with.

Confirmed.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel