pf » Confirming Transaction support

Want to know if your ColdFusion database driver supports transactions (the <cftransaction> tag)? I was wondering how I might test this, and I came up with a solution. The code I wrote essentially creates a dead lock if transactions are supported by the db driver, if the timeout is reached an exception is thrown, and we know that our database and driver support transactions.

The Code: (warning I wouldn't run this against a live database, because it does cause a deadlock)

<cftransaction>
  <cfquery datasource="#ds#">
  	UPDATE table 
	SET column = 'value'
	WHERE id = 1
  </cfquery>
  
  <cftry>
    <cfhttp url="http://localhost/deadlock.cfm" 
	  method="get" timeout="5" throwonerror="true">
    <cfcatch type="any">
 	  Transactions work!
    </cfcatch>
  </cftry>
 
  <cfquery datasource="#ds#" name="data">
  	SELECT column FROM table
	WHERE id = 1
  </cfquery>
</cftransaction>

<cfdump var="#data#">

Now create a file called deadlock.cfm if possible put this file on a different server, the <cfhttp> call above should call this file.

<cfquery datasource="#ds#" timeout="8">
  	UPDATE table 
	SET column = 'deadlock'
	WHERE id = 1
</cfquery>

If the page says "Transactions work!", then transactions ofcourse seam to be working. I used this method to check transaction support of PostgreSQL 7.2.3 running on Redhat 8, using ColdFusion 5 on Windows with the PostgreSQL 07.02.0005 ODBC driver. And they do indeed work.

I may also check the mySQL odbc drivers, connecting to a mySQL 4.x database, mySQL 4.x supports transactions using the Berkley DB, or InnoDB file formats (not the default MyISAM table format), but I still need to install 4.x it on my server. If anyone has this setup, or has already tested please let me know.