ColdFusion - CFTRANSACTION and Failed Inserts Due To Heavy Use

I was getting primary key violations, even though I was using cftransaction around the select max(id) and the insert sql statements, due to heavy application use - it is a counter used for statistical data for the entire heavily used application. So I sought out some solutions from others and found an article on "ColdFusion 9 ORM and Transactions - It Does Not Mean What You Think it Means." - Although I am looking to upgrade to ColdFusion 9 at some point, these solutions aren't currently available for the application for which I needed the solution.

The two solutions that came to mind were 1. remove the primary key from the counter table, and 2. create a transaction table that could catch the error (cftry, cfcatch) and run the insert in someone else's page load - I opted for the later.

The transaction table doesn't have a primary key, so there are no issues like this with that table. It cues insert transactions on insert failure to the other table, and retries on the next page load - I also added a way to track if it is a first try or subsequent try in the transaction table, in possible cases where the insert fails more than once. This makes sure there is just one successful subsequent retry. On a successful retry, the system deletes the entry from the transaction table.

Here's the algorithm.

if insert fails,
- load into transaction table.
on next page load from anybody, if items in transaction table,
- attempt to insert just one from the transaction table
- if failed, do leave in the transaction table
- if success, delete from transaction table

Although the transaction table has no primary key, the combination of data recorded, one being datetime with accuracy to about 1000th of a second, this approach creates a unique way to id the transaction for subsequent attempt to insert data. And, although this slows the page load down by adding another process, I don't foresee this as much of a problem.

Also, although there is only one transaction attempted per page load, the only point of failure for this technique is when more than half of the initial transactions fail, which would build data into the transaction table faster than in the original table. The current fail rate is not even close to half, more like one in 10,000, so I don't foresee this as an issue until (or if) we get to about 10 million page loads per day. Prior to that, we can simply add more servers to accomodate the heavier use.

ColdFusion 9 ORM and Transactions - It Does Not Mean What You Think it Means. - take a look at this article if you want more details.