#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    3
    Rep Power
    0

    Want to always commit a certain CFQUERY Insert within a CFTRANSACTION


    I have a situation where I would like to always COMMIT a CFQUERY Insert statement into a Log table within a CFTRANSACTION block whether the transaction succeeds or fails. I am a Coldfusion newbie, so I am uncertain how best to proceed.
    It seems from what I have researched that I might have 2 possible ways to do this.
    1. Use CFTHREAD to kick off an asynchronous process to do this Insert. I'm uncertain if this would be immune from being Rolledback in a failed transaction, however.
    2. Use Setsavepoint within the CFTRANSACTION and explicitly commit only that short block of code, obviously, without committing anything outside the Savepoint. I am unclear from the documentation if this would work as I intend or not.
    We are running Coldfusion version 9.
    I would appreciate any comments or advice here. Perhaps there is a better way of doing this altogether that I am overlooking??
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    If you want one of the updates to happen even if the transaction fails, then it shouldn't be part of the transaction. A transaction is an atomic unit, meaning it all succeeds or it all fails. So unless I'm missing something, you want to run this query either before or after the transaction, not within it.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    3
    Rep Power
    0
    Kiteless, I hear you and understand that. My issue then is the variables I need to output to my log table are multiple levels deep inside the Transaction. So, I guess my only choice is to pull the variables I need up the tree (so to speak) until I'm outside the transaction? Is that doable without losing the variable data??
    I am pulling up some of the data a level while I was testing solutions with cfset caller.xxxxx function.... Not crazy about doing that, but I don't know a better way currently, being a Newbie and all..
    Any further thoughts??


    Originally Posted by kiteless
    If you want one of the updates to happen even if the transaction fails, then it shouldn't be part of the transaction. A transaction is an atomic unit, meaning it all succeeds or it all fails. So unless I'm missing something, you want to run this query either before or after the transaction, not within it.
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    Without seeing everything you're doing, it's hard to say. But it sounds like what you want to do is have the method running the transaction return some value or an object containing the data you need, and then use that to do your insert.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    3
    Rep Power
    0
    OK, I guess I'll work that direction, then. Appreciate the input....

    Originally Posted by kiteless
    If you want one of the updates to happen even if the transaction fails, then it shouldn't be part of the transaction. A transaction is an atomic unit, meaning it all succeeds or it all fails. So unless I'm missing something, you want to run this query either before or after the transaction, not within it.

IMN logo majestic logo threadwatch logo seochat tools logo