2

I have an automated invoicing web app and I'm trying to build in some failsafes and a structure that will, under no circumstance, allow an invoice to be double charged.

All things working perfectly, the scheduled task runs and everything charges as it's supposed to. But there are a few scenarios we know of that can cause problems.

I've had a DB server go down in the middle of a process, which lead to a number of duplicate charges. Also if for whatever reason, the file needs to be checked in a browser, a developer hits refresh before the file is done processing, duplicate charges will occur.

There are a few similar scenarios to protect against, so you can see the need for a way to handle this given the sensitivity of the transactions (imaging seeing your power company auto charging you twice on your bank statement).

Current configuration:

  • Query

  • API POST For Transaction

    <!--- Parse Reply String --->
    if response = "1"
    
     - Insert Into Payments
    
     - Update Invoice Amount Due
    
     - Insert Into Transactions
    
     - Email Accepted Notification
    
    else
    
     - Email Declined Notification
    

There's a few more smaller processes that happen between a few of these, as well as quite a few if statements but those are the heavy lifting parts.

What I would like to learn and hear about are what would be the best way to handle safeguarding this with considering the Third Party API for the gateway being there?

How would you break it up?

One idea I had was flagging each process after it ran to give a record of where it was when it failed, and build out the query to handle it accordingly. Not sure how efficient or more likely how inefficient it would be.

  • Can you detect whether it's a duplicate? So: lock, select * from invoices WHERE month=januari, if not found: add, unlock. – Luc Franken Jan 10 '14 at 15:24
  • I could add the transactionID to the invoice, and query it against the transactions table. Which should be done. But if a DB server goes down in the middle of a process, charges in the gateway won't get recorded, and will be pulled again when it comes back on line. Happened the other week. – Steve Ontologic Jan 10 '14 at 16:20
2

In complex ERP systems when a record needs to be protected there is usually a locking mechanism in place somewhere. In most systems this involves either a DB table or a flag on the table itself.

So one way of doing this is to have an table (say, InvEdit) that contains the invoice ID. The first thing you do is to put the invoice id's of the invoices you wish to edit in the table. Next you do the work on a per invoice basis as a single transaction. The last act of the transaction would be to remove the invoice from your invEdit table. You would naturally also want to change something about the invoice so that it would not be put in the invEdit table again (but you are probably already doing that - updating a status or a date or whatever).

If a transaction fails (because the DB goes down or whatever) that data is not commited and the Invoice ID remains in the invEdit table. If it succeeds it is removed. If the DB goes down OR if the item is refreshed or the Web server crashes or whatever you can pick up where you left off - from the "queue" provided by your invEdit table.

This is just one approach of course. I'm sure that others can think of more.

0

If all of the queries are required to run in order for a transaction to be considered complete, then you need to the entire thing in a transaction, and roll it back if there is a problem. You should do something like this:

<cftransaction action="begin">
    <cftry>

         - Insert Into Payments

         - Update Invoice Amount Due

         - Insert Into Transactions

         <cftransaction action="commit" />

        <cfcatch type = "any">
            <cftransaction action="rollback" />
            <cfrethrow />
        </cfcatch>
    </cftry>

</cftransaction>

If everything in your inserts, updates, etc happens correctly, then the transaction will commit. If there are any errors, then the transaction will be rolled back, or the commit will simply never happen.

The transaction should also lock the table so that any requests to modify it should be blocked. Before you start doing the inserts and updates you should have a check to make sure that they should be done.

  • Thank you. What if a DB server crashes mid process? Does cftransaction handle the commit or roll back if there is no DB server there? – Steve Ontologic Jan 10 '14 at 16:33
  • That's up to your database server how it finishes up uncommitted transactions during a crash. What should happen, depending on the server, is that any non-committed transactions in the log should be rolled back when the server restarts. Once CF loses the connection to the database server, there's nothing it can do to help you. – Dan Short Jan 10 '14 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.