Professional Web Applications Themes

how to hand code a transaction - MySQL

Hi, How do I manually code an atomic transaction, given the following scenario? 1. User need to pay a fine. 2. User log into the system. 3. User click the pay button. 4. User pay by a custom payment methods called NETS (system operated through telephone line, in Singapore; it is not direct connection to a databse, but somekind of services.) 5. Payment/NETS transaction done; transaction cannot rollback after this. 6. System would update the database to indicate the fine have been paid. But step 6 might fail. How do I ensure that all the steps is in one single ...

  1. #1

    Default how to hand code a transaction

    Hi,

    How do I manually code an atomic transaction, given the following
    scenario?

    1. User need to pay a fine.
    2. User log into the system.
    3. User click the pay button.
    4. User pay by a custom payment methods called NETS (system operated
    through telephone line, in Singapore; it is not direct connection to a
    databse, but somekind of services.)
    5. Payment/NETS transaction done; transaction cannot rollback after
    this.
    6. System would update the database to indicate the fine have been
    paid.

    But step 6 might fail. How do I ensure that all the steps is in
    one single transaction?
    Is there any example/web page which show how to program a atomic
    transaction manually?

    Thanks

    regards,
    john.

    i676373@gmail.com Guest

  2. #2

    Default Re: how to hand code a transaction

    >How do I manually code an atomic transaction, given the following 

    You don't. ROLLBACK doesn't apply to the real world. I find it
    difficult to believe that you cannot find some way to (maybe manually,
    and with 6 levels of management approval) credit back an erroneous
    payment. You can try hard, though:

    - Make sure the database is available before starting the NETS transaction.
    - If the NETS transaction goes through but the database query fails,
    log that fact anywhere and everywhere you can: in a disk file, in
    email to the administrator of this system, to his pager, etc.
    This lets the administrator know that something is ing up, and
    also gives him information needed to fix it. Granted, the user might
    get confused and pay AGAIN.
     

    Databases let you do atomic transactions within a database. They
    don't let you do atomic transactions between two otherwise unconnected
    databases each capable of atomic transactions, or between a database
    and the real world.

    Gordon Guest

  3. #3

    Default Re: how to hand code a transaction

     
    >
    > You don't. ROLLBACK doesn't apply to the real world. I find it
    > difficult to believe that you cannot find some way to (maybe manually,
    > and with 6 levels of management approval) credit back an erroneous
    > payment. You can try hard, though:[/ref]

    I fail to see why you cannot do the rollback and notify
    the user of the failure?
     
    transaction. 
    >
    > Databases let you do atomic transactions within a database. They
    > don't let you do atomic transactions between two otherwise unconnected
    > databases each capable of atomic transactions, or between a database
    > and the real world.[/ref]

    Not true, this is where multi-database transactions come in. It has
    been done and is supported by many database systems.


    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  4. #4

    Default Re: how to hand code a transaction

    On Tue, 6 Mar 2007 15:46:50 +0100, Martijn Tonies wrote: 
    >>
    >> You don't. ROLLBACK doesn't apply to the real world. I find it
    >> difficult to believe that you cannot find some way to (maybe manually,
    >> and with 6 levels of management approval) credit back an erroneous
    >> payment. You can try hard, though:[/ref]
    >
    > I fail to see why you cannot do the rollback and notify
    > the user of the failure?[/ref]

    The user has given money, but receieved only a notification of error in
    return. This leaves the user Unsatisfied.

    --
    It is odd, but on the infrequent occasions when I have been called upon
    in a formal place to play the bongo drums, the introducer never seems to
    find it necessary to mention that I also do theoretical physics. --Feynman
    Peter Guest

  5. #5

    Default Re: how to hand code a transaction

    On 5 Mar 2007 17:27:46 -0800, com wrote: 

    Fines are not your atomicity. Fines are a debit against a "user
    account". Payments are a credit against that account, once verified by
    whatever mechanism verifies NETS payments. Payment is entered as an
    unverified credit between step 3 and step 4, including the ID of the
    specific fine being paid (for later crosschecking) and whatever token
    is passed to NETS. When NETS confirms (however it confirms, even if
    it's a paper list of transactions and tokens), the credit is update to
    "verified". This changes step six and make it its own process and not
    part of the transaction per se as far as the database is concerned.

    You can then generate three useful kinds of lists:

    fines paid (fines that have verified corresponding payments)
    fines in processing (fines that have unverified corresponding payments)
    fines unaddressed (fines without corresponding payments listed at all)

    Periodically, you run reports summing users that have more confirmed
    payments totalling more than their total amount of fines and issue
    refunds.

    --
    83. If I'm eating dinner with the hero, put poison in his goblet, then have to
    leave the table for any reason, I will order new drinks for both of us
    instead of trying to decide whether or not to switch with him.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  6. #6

    Default Re: how to hand code a transaction

     
    > >
    > > I fail to see why you cannot do the rollback and notify
    > > the user of the failure?[/ref]
    >
    > The user has given money, but receieved only a notification of error in
    > return. This leaves the user Unsatisfied.[/ref]

    Sure, but as long as you cannot update the database accordingly,
    the transaction should be rolled back, don't you think?


    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
    MS SQL Server
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  7. #7

    Default Re: how to hand code a transaction

    On Tue, 6 Mar 2007 16:39:41 +0100, Martijn Tonies wrote: 
    >>
    >> The user has given money, but receieved only a notification of error in
    >> return. This leaves the user Unsatisfied.[/ref]
    >
    > Sure, but as long as you cannot update the database accordingly,
    > the transaction should be rolled back, don't you think?[/ref]

    That would depend on being able to roll back the completed NETS payment
    as well, and that depends on NETS policies, which I don't know, and it
    kind of seems like the OP doesn't know deeply either. If step 6 fails
    above, one needs to not just roll back, but issue a NETS cancellation or
    credit and get a success with that before you can roll the transaction
    back in the database. And what do you do if the payment succeeds, but
    step 6 fails *and* the NETS cancellation/credit fails? Can't roll back
    the database yet because we've got money we can't credit, and can't
    refund the money.

    --
    17. When I employ people as advisors, I will occasionally listen to their
    advice.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  8. #8

    Default Re: how to hand code a transaction

     [/ref][/ref]
    manually, 
    > >
    > > Sure, but as long as you cannot update the database accordingly,
    > > the transaction should be rolled back, don't you think?[/ref]
    >
    > That would depend on being able to roll back the completed NETS payment
    > as well, and that depends on NETS policies, which I don't know, and it
    > kind of seems like the OP doesn't know deeply either. If step 6 fails
    > above, one needs to not just roll back, but issue a NETS cancellation or
    > credit and get a success with that before you can roll the transaction
    > back in the database. And what do you do if the payment succeeds, but
    > step 6 fails *and* the NETS cancellation/credit fails? Can't roll back
    > the database yet because we've got money we can't credit, and can't
    > refund the money.[/ref]

    Right, now I understand.

    Then I suggest doing things the other way around:

     [/ref][/ref]
    operated [/ref][/ref]
    to a [/ref][/ref]
     [/ref][/ref]
     [/ref][/ref]

    If the NETS thing fails, rollback. If it succeeds, commit. If the actual
    "commit" fails for whatever reason, log it and act accordingly, but
    it would solve the immediate problem.
     [/ref][/ref]



    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
    MS SQL Server
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  9. #9

    Default Re: how to hand code a transaction

    com wrote:
     

    I thought that rang a bell..


    NDC




    NN NN EEEEEEEE TTTTTTTT SSSSSS
    NNN NN EE E T TT T SS SS []
    NNNN NN EE E TT SS []
    NN NN NN EEEEE TT SSSSSS [][][][]
    NN NNNN EE E TT SS []
    NN NNN EE E TT SS SS []
    NN NN EEEEEEEE TTTT SSSSSS

    NETS+ PC BATCH Version 3.00




    ------------------------------------------------------------------------------
    | NATIONAL ELECTRONIC
    | TRANSACTION SERVICES NETS+ COPYRIGHT (C) 1987,1988 NDC
    ------------------------------------------------------------------------------


    Well, you can't "roll it back" but you can delete it from the authorized file
    before you settle the transactions.

    Getting back to your problem. ERROR.DTA is your friend. I'm not sure how you are
    loading INFILE.DTA, but after calling pcbatch, look for content in ERROR.DTA and
    loop against that (redial, give up, etc...) against whatever you want to do.

    If the transaction is in SETT.DTA it worked...

    We worked with this using filePro (www.fptech.net) since it could work natively
    with alien fixed format files. However this is mySQL, I don't think the CSV
    storage engine will help. If there was a storage engine that would allow named
    files with pre-defined fixed width fields, you actually might be able to do this.

    --
    Walter
    Walter Guest

  10. #10

    Default Re: how to hand code a transaction

    >> >How do I manually code an atomic transaction, given the following 
    >>
    >> You don't. ROLLBACK doesn't apply to the real world. I find it
    >> difficult to believe that you cannot find some way to (maybe manually,
    >> and with 6 levels of management approval) credit back an erroneous
    >> payment. You can try hard, though:[/ref]
    >
    >I fail to see why you cannot do the rollback and notify
    >the user of the failure?[/ref]

    Because YOU don't own the database of the payment processor and the
    payment processor isn't going to let you access it like you do own
    it. You've billed the user and informed him he just got cheated.
    It may, in fact, be impossible to totally roll back the transaction
    with the payment processor, as the transaction fee might still
    remain.
     
    >transaction. 
    >>
    >> Databases let you do atomic transactions within a database. They
    >> don't let you do atomic transactions between two otherwise unconnected
    >> databases each capable of atomic transactions, or between a database
    >> and the real world.[/ref]
    >
    >Not true, this is where multi-database transactions come in. It has
    >been done and is supported by many database systems.[/ref]

    My bet is that it has NOT been done in a setup where the two parties
    owning the two databases have to assume that the other party might
    be out to cheat them and can't tolerate disruptions of their system
    by the other one. A payment processor isn't going to let a merchant
    use SQL directly on their database. Nor will the reverse be allowed.

    Gordon Guest

Similar Threads

  1. [PHP] Wrapping code inside [code][/code] tags.
    By Php in forum PHP Development
    Replies: 2
    Last Post: October 6th, 09:34 AM
  2. Transaction id and transaction isolation
    By Alex in forum Informix
    Replies: 3
    Last Post: July 23rd, 09:33 PM
  3. Does RANK and weightage goes hand in hand
    By Abhijeet Raje in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 6th, 12:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139