CFTRANSACTION: Am I using it incorrectly?

[bentdesigns]

Just so we don't get too far off track, keep in mind the code samples below aren't my true code. I'm stripping things down for illustration purposes only - I realize they're bad examples and lack CFQUERYPARAM.

So, to give a little background first... I started my ColdFusion programming career over a decade ago building fairly large e-commerce applications. Not Amazon scale, but not mom-and-pop shopping carts by any means.

In those days I often used CFTRANSACTION for situations where multiple INSERT or UPDATE queries relied on each other. For example, a form that inserts a new product into a database table, and inventory counts into a separate table.

For example:

<cftransaction>

<cfquery datasource="mydatasource" result="product_inserted">

INSERT INTO products (sku, price, title)

VALUES ('555-555', 2.50, 'Spider-Man T-Shirt')

</cfquery>

<cfquery datasource="mydatasource">

INSERT INTO inventory (product_id, inventory)

VALUES (#product_inserted.IDENTITYCOL#, 50)

</cfquery>

</cftransaction>

This insures that both tables are written to. If a query fails for some reason, the other won't be committed to the database.

Somewhere along the line however I started what I believe is a futile (and possibly bad) practice.

In some applications I have a ColdFusion template which (when passed an ID number via a URL variable) allows the user to edit a record via a form.

The first thing the page does is check for the required URL variable, and then pulls the records from the database. It then displays that record in a form for editing.

When the form is submitted, the page obviously checks for the URL variable again, the required form fields, and then queries to make sure the record exists (as you don't want to continue with the UPDATE if the URL variable isn't a valid record, right?

So, the processing page for the form update might have code like this:

<cftransaction>

<cfquery name="find_product" datasource="mydatasource">

SELECT *

FROM products

WHERE id = #url.sku#

</cfquery>

(some code here that checks the form data for proper type, etc.)

<cfquery datasource="mydatasource">

UPDATE products

SET myfield = #form.myfield#,

       anotherfield = #form.anotherfield#

WHERE id = #find_product.sku#

</cfquery>

</cftransaction>

You see what I did there? Somewhere along the line in my programming history I just suddenly started putting CFTRANSACTION tags around blocks of code that used multiple queries, usually a SELECT statement and then an UPDATE statement that was then updating the record found via the SELECT statement. I started treating CFTRANSACTION as some sort of "lock", thinking that it was somehow ensuring that the SELECT and UPDATE statement were uninterrupted by another other user who may be invoking the same page, thus avoiding a race conflict for the record being edited. Please someone set my mind at ease and tell me this is actually not accomplishing that, and all I'm doing is slowing down my DB processes?

If my hunch is correct, and I've had a futile/bad habit for years, what would be the proper method for avoiding the above scenario?

[BKBK · Accepted solution]

In my opinion, what you have been doing is correct. What you describe is a fitting use-case for cftransaction. The cftransaction tag tells the database management system to handle 2 or more queries as a single transaction. They either all succeed, or all fail, together.

You are also correct in treating cftransaction as "some sort of lock". The tag's isolation attribute determines the level of locking.

For example, isolation="read_uncommitted" is the lowest isolation level. It allows 'dirty reads', whereby one transaction may read the, as yet, uncommitted changes made by other transactions. The highest isolation level is 'serializable'. It generally prevents a transaction from reading the data being changed by other transactions, until the changes have been committed or rolled back, and all the locks released.

Coldfusion has no say in this. Responsibility for database locking is with the database management system, and each database brand has its own locking rules.

If you specify no value for the isolation attribute, the database will use its own default isolation level. The default for Oracle and SQL Server is 'read_committed'. That of MySQL (InnoDB) is 'repeatable_read'.

[bentdesigns]

Thank you BKBK...

So using my example previous example where I am accessing a ColdFusion page via URL variable that pulls a record and then updates it based on form values provided by a user:

<cftransaction>

<cfquery name="find_product" datasource="mydatasource">

SELECT *

FROM products

WHERE id = #url.sku#

</cfquery>

(some code here that checks the form data for proper type, etc.)

<cfquery datasource="mydatasource">

UPDATE products

SET myfield = #form.myfield#,

      anotherfield = #form.anotherfield#

WHERE id = #find_product.sku#

</cfquery>

</cftransaction>

There is nothing in my use of CFTRANSACTION here that would prevent another user using the same page at the same time from updating the "products" table at the same time as another user, correct?

[Dave Ferguson]

Couple things...

First, and the most important, use queryparam tags unless you want to get sql injected.

Secondly, do all of your data validation processing outside of the transaction.  The only code you should have inside the transaction is sql queries.

Also, don't use select * .  Only get the columns you need.  It will increase your query performance.

[bentdesigns]

fergusondj,

Thanks for the advice, but as I mentioned in my original post the example codes are stripped down so as not to be too long - I left things like CFQUERYPARAM out just to keep it short.

[BKBK]

Adobe Forums User wrote:

There is nothing in my use of CFTRANSACTION here that would prevent another user using the same page at the same time from updating the "products" table at the same time as another user, correct?

If committed reads are vital to the business logic, then use <cftransaction isolation="serializable">.