Highlighted

CFTRANSACTION not working anymore in our environment

Community Beginner ,
Jun 25, 2020

Copy link to clipboard

Copied

Greetings,

 

I just discovered based on incomplete recording in PROD database that CFTRANSACTION tag is not working anymore. Not sure if that specific to our environment or global issue.  Don't know if CF upgade or MS SQL upgrade caused that.. I know that it was working in CF11 environment, we have switched to CF18 about a year and half ago. 

 

My environment:

ColdFusion 2018 

Version 2018.0.09.318650

Edition- developer(but it doesn't work in enteprize also)

Microsoft SQL Server Enterprise
Platform NTx64
Version 13.0.5081.1

 

Example of simple test code below:

 

<cftransaction  action="begin" >

<cfquery name="Test1" datasource="XXX">
INSERT INTO TrTest1 (TestString)
VALUES('Recording 1')
</cfquery>

<cfquery name="Test2" datasource="XXX">
INSERT INTO TrTest2 (TestString)
VALUES('Recording 2')
</cfquery>

 

<cftransaction action="rollback" />

 

Rollback is not working, records are inserted Can anybody verfy that or point if that simple test case incorrect. It is pretty much a disaster. I hope it is connected with some setting for MS SQL.

 

Regards,

Simon Litvak

UC Berkeley

 

Views

54

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

CFTRANSACTION not working anymore in our environment

Community Beginner ,
Jun 25, 2020

Copy link to clipboard

Copied

Greetings,

 

I just discovered based on incomplete recording in PROD database that CFTRANSACTION tag is not working anymore. Not sure if that specific to our environment or global issue.  Don't know if CF upgade or MS SQL upgrade caused that.. I know that it was working in CF11 environment, we have switched to CF18 about a year and half ago. 

 

My environment:

ColdFusion 2018 

Version 2018.0.09.318650

Edition- developer(but it doesn't work in enteprize also)

Microsoft SQL Server Enterprise
Platform NTx64
Version 13.0.5081.1

 

Example of simple test code below:

 

<cftransaction  action="begin" >

<cfquery name="Test1" datasource="XXX">
INSERT INTO TrTest1 (TestString)
VALUES('Recording 1')
</cfquery>

<cfquery name="Test2" datasource="XXX">
INSERT INTO TrTest2 (TestString)
VALUES('Recording 2')
</cfquery>

 

<cftransaction action="rollback" />

 

Rollback is not working, records are inserted Can anybody verfy that or point if that simple test case incorrect. It is pretty much a disaster. I hope it is connected with some setting for MS SQL.

 

Regards,

Simon Litvak

UC Berkeley

 

Views

55

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jun 25, 2020 0
Adobe Community Professional ,
Jun 25, 2020

Copy link to clipboard

Copied

What's the transaction isolation level in both old and new database environments?

 

Dave Watts, Eidolon LLC

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2020 0
Community Beginner ,
Jun 25, 2020

Copy link to clipboard

Copied

Hi Dave,

 

I didn't specified isolation level in my CFTRANSACTION. So is your question about MS SQL settings?  If yes, I need to contact DBA. I don't manage databases.

 

Regards,

Simon

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2020 0
Adobe Community Professional ,
Jun 25, 2020

Copy link to clipboard

Copied

Yeah, you should definitely know what the default transaction isolation level is in your databases. Otherwise, you can't really be sure what CFTRANSACTION is going to do! Usually, it's "read committed" I think for SQL Server.

 

Dave Watts, Eidolon LLC

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2020 0
Adobe Community Professional ,
Jun 25, 2020

Copy link to clipboard

Copied

Hmmm, I'm not sure how that would work. Were you perhaps aiming for something like the following?

<!--- Experiment with true, then with false, and see what happens --->
<cfset isRolledBack=true>

<cftransaction>

<cfquery name="Test1" datasource="XXX">
INSERT INTO TrTest1 (TestString)
VALUES('Recording 1')
</cfquery>

<cfquery name="Test2" datasource="XXX">
INSERT INTO TrTest2 (TestString)
VALUES('Recording 2')
</cfquery>

<cfif isRolledBack>
        <!--- Nested transaction --->
	<cftransaction action="rollback" />
</cfif>

</cftransaction>

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2020 0
Community Beginner ,
Jun 25, 2020

Copy link to clipboard

Copied

Hi BKBK,

 

I think, that is not going to have any difference with my code.  I just have uncoditional rollback.

 

Regards,

Simon

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2020 0
Adobe Community Professional ,
Jun 25, 2020

Copy link to clipboard

Copied

I think you do need to post a more complete code sample, as your original one shouldn't really work at all. CFTRANSACTION requires an opening and a closing tag, you can't just have one CFTRANSACTION tag at the top and another later on doing a rollback. At least, it's not supposed to work that way. Maybe it did before, but ... it wasn't supposed to.

 

Dave Watts, Eidolon LLC

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2020 0
Community Beginner ,
Jun 25, 2020

Copy link to clipboard

Copied

Thanks a lot Dave and BKBK,

 

Interestin, </cftransaction> end tag actually is required, but no errors issued if it not there. It works properly with BKBK test case.  Do you know if there any issues if <cfstoredeproc> tag is part of cftransaction?

Our PROD code that brakes includes those tags inside.  I will build test case with those also.

 

Regards,

Simon

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2020 0
Adobe Community Professional ,
Jun 25, 2020

Copy link to clipboard

Copied

CFSTOREDPROC tags should work just like CFQUERY tags within a CFTRANSACTION block, as long as they don't explicitly change the isolation level themselves within the code of the stored procedure (which is theoretically possible I guess but I've never seen that).

 

Dave Watts, Eidolon LLC

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2020 0
SimonGSW LATEST
Community Beginner ,
Jun 25, 2020

Copy link to clipboard

Copied

To make sure, I build a test case with stored procedures calls.

 

<cftransaction action="begin" >

 

<cfstoredproc procedure="TransactionTest1" datasource="XXX" returncode="No">
<cfprocparam type="In" dbvarname="@ValueString" value="Recording 1" cfsqltype="cf_sql_varchar">
</cfstoredproc>

<cfstoredproc procedure="TransactionTest2" datasource="XXX" returncode="No">
<cfprocparam type="In" dbvarname="@ValueString" value="Recording 2" cfsqltype="cf_sql_varchar">
</cfstoredproc>

 

<cftransaction action="rollback" />
</cftransaction>

 

That works also.  So the problem was the missing closing </cftrasacion> tag. No errors, but it no action also. 🙂

 

Case closed. Thanks a lot everyone.

 

Regards,

Simon

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 25, 2020 1