DB Transactions With ColdFusion

…posted by davidjmedlock:

Time flies all too quickly. I’ve been remiss in my posting here and I apologize. I did plan to write a quick note about the security of scheduled tasks, but I have yet to figure out a solid way of authenticating who ever is accessing them. At any rate, I did find this excellent article on Macromedia regarding database transactions.

For anyone who has ever developed serious applications, you may have encountered times when you performed many database alterations all in a row only to have one of them fail and ultimately ruin your afternoon (or weekend, even…) I strongly encourage the use of transactions as a means of error handling in your application. And in ColdFusion, they’re extremely easy to use.

With a framework such as .Net, you have to create a transaction object and attach it to a connection object and then begin the transaction and then when you’re finished with your queries, you have to commit the transaction. If you fail to do everything in just the right manner, you’re application will explode. With ColdFusion it’s really quite simple:





If any query inside the tag were to bomb at any point, all the queries would be reversed, including any update, delete, or insert actions you had previously performed. I’ve used this many times in my applications and trust me, it can save you a lot of headaches. This also helps if the queries you run tend to be numerous or very bulky and put your application in peril of timing out, which I’ve had happen a few times.

So, there’s a little something more for your error handling and overall application stability toolbox. Enjoy.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • http://www.ryanguill.com Rynoguill

    thanks david! ive only used cftransaction a couple of times, and honestly had forgotten about it. i could a few places i could use this

  • Kyle

    Doesn’t somehow lock up the tables that are being processed? How does that affect other users who are accessing those same tables (even same rows)?

  • http://www.delyrical.com davidjmedlock

    [QUOTE=Anonymous]Doesn’t somehow lock up the tables that are being processed? How does that affect other users who are accessing those same tables (even same rows)?[/QUOTE]
    You are probably correct on that. It does not seem like it would lock the entire table, though. If you’re using SQL Server, you could use (NOLOCK) on the tables you’re SELECTING from so as to avoid the locks. But, when updating and inserting, you DO want it locked so someone else doesn’t try to get to it at the same time. At any rate, there are times that you most definitely want a row locked and so I really don’t have a problem using cftransaction in these types of cases.

    Excellent point, though. Thanks for the comment.

  • Peter R

    let’s say you don’t use the cftransaction tags, when are the queries in a cf template committed? we’re using oracle… and I’m assuming if there’s no cftransaction tags the default behavior would be auto commit.

    Thanks,
    Peter