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.

>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?

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 screwing up, and
also gives him information needed to fix it. Granted, the user might
get confused and pay AGAIN.

>Is there any example/web page which show how to program a atomic
>transaction manually?

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.

> >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?
>
> 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:

I fail to see why you cannot do the rollback and notify
the user of the failure?

> - 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 screwing up, and
> also gives him information needed to fix it. Granted, the user might
> get confused and pay AGAIN.
>
> >Is there any example/web page which show how to program a atomic
> >transaction manually?
>
> 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.

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

> >> >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?
> >>
> >> 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:
> >
> > I fail to see why you cannot do the rollback and notify
> > the user of the failure?
>
> The user has given money, but receieved only a notification of error in
> return. This leaves the user Unsatisfied.

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

> >> >>
> >> >> 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:
> >> >
> >> > I fail to see why you cannot do the rollback and notify
> >> > the user of the failure?
> >>
> >> The user has given money, but receieved only a notification of error in
> >> return. This leaves the user Unsatisfied.
> >
> > Sure, but as long as you cannot update the database accordingly,
> > the transaction should be rolled back, don't you think?
>
> 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.

Right, now I understand.

Then I suggest doing things the other way around:


> >> >> >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.)

> >> >> >6. System would update the database to indicate the fine have been
> >> >> >paid.

> >> >> >5. Payment/NETS transaction done; transaction cannot rollback after
> >> >> >this.

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.

> >> >> >But step 6 might fail. How do I ensure that all the steps is in
> >> >> >one single transaction?



--
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

i676373@gmail.com wrote:

> 5. Payment/NETS transaction done; transaction cannot rollback after
> this.

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

>> >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?
>>
>> 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:
>
>I fail to see why you cannot do the rollback and notify
>the user of the failure?

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.

>> - 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 screwing up, and
>> also gives him information needed to fix it. Granted, the user might
>> get confused and pay AGAIN.
>>
>> >Is there any example/web page which show how to program a atomic
>> >transaction manually?
>>
>> 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.
>
>Not true, this is where multi-database transactions come in. It has
>been done and is supported by many database systems.

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.