Confirming Transaction support

April 14, 2003

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)

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

<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

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.

4 people found this page useful, what do you think?


identified by yourPassword';Then after that you can create daetbasas with a non-root account.My set-up will be creating new blank DBs, copying over standard tables used by our custom tools chosen by the developer, crating a new MySQL user accouht that can only access that new database, and creting a DSN for that DB so our remote developers can take advantage of out pre-made tools, set up a new mysql db, etc, and not have to call us every time.This is all through a admin website so we can control who gets what.Great write up, just know that you will have issues with a non-root account, and most dba's will never allow root access remotely.

Post a Comment


Spell Checker by Foundeo

Recent Entries


did you hack my cf?