Making credit processing atomic

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

An application I've developed does online event registration. Many events have a fee that can be paid by credit card. The system works very well most of the time, but users do sometimes manage to make multiple payments (by clicking refresh) or to have their credit cards charged without us having any record of the transaction. The process I'm using to enter registrations is as follows (pseudo-code):

INSERT INTO registrations (to reserve spot for the registration)
Generate unique key for payment that is included in all subsequent com
+munication from browser
Collect information for registration
Check that payment with unique key hasn't been entered
Get credit authorization
UPDATE registration record with collected information including a fina
+lized flag
INSERT INTO payments (information from credit processor with transacti
+on code, unqiue key from previous step, etc)
[download]

There are several spots where things can go wrong. Sometimes a user will click refresh in the moment between submitting the transaction for approval and inserting the payment information in the payments table. This prevents the unique key check from stopping the second payment. Sometimes a database error after transaction approval prevents the information being stored properly.
Is there a good way to make this process atomic?
Thanks!

Comment on Making credit processing atomic Download Code

Replies are listed 'Best First'.
Re: Making credit processing atomic by RazorbladeBidet (Friar) on Apr 12, 2005 at 16:37 UTC
Several things. You can make your update/insert a transaction. Depending on your database, start the transaction, execute the statments, and commit ONLY if both succeed. Otherwise you have an error. You can also set flags on the users end to prevent them from refreshing the page. One is to disable any buttons immediately after they are clicked and another is to set cookies to hold flags which are set when the transaction starts. These are only really useful if you REQUIRE the user to have JavaScript and/or Cookies enabled. Any database error after transaction approval should be a system error and complete rollback. Unless, of course, you want to try again. Are you also settling the credit card? -------------- "But what of all those sweet words you spoke in private?" "Oh that's just what we call pillow talk, baby, that's all."	[reply]
Re: Making credit processing atomic by gam3 (Curate) on Apr 12, 2005 at 16:47 UTC
Here is what I do: Generate a unique key. (This is just be a large random #) Store the unique key in the submit form. Insert your data into the database with then Unique key being stored in a unique field. Now if the user submits twice (or 100 times) only one of the inserts will succeed. This can fail if you are pick a random number that is already in the database (or you can check that) and it will fail if 2 user get the same unique key. You can't protect against this, but the odds are 1 in 4*10^9 if you use on a 32 bit key, but if you use a 64 bit key, then key collition is not be a problem at all. -- gam3 A picture is worth a thousand words, but takes 200K.	[reply]
Re^2: Making credit processing atomic by satchm0h (Beadle) on Apr 12, 2005 at 22:21 UTC
Check out Data::UUID for your unique identifyers. Stop worrying about collisions, unless of course you have dynamic MACs or are prone to backward time warps. satchm0h...stumbling down the path toward enlightenment	[reply]
Re: Making credit processing atomic by hubb0r (Pilgrim) on Apr 12, 2005 at 18:15 UTC
If using a proper transactional DB system (ie I use Mysql with InnoDB tables) you simply turn AutoCommit => 0 during your intitial database connection. Then you wrap your entire transaction in an eval block, begin and end the transaction inside, and check for errors following, like the following: `eval { $dbh->begin_work; $self->insert_file($file); $self->$handler($file); $self->update_package_table($file,$type); $self->update_city_summary($file->{'processed_scans'}{$type}); $dbh->commit; }; if ($@) { warn stamp()."Transaction aborted because $@ Rolling back transact +ion\n"; eval { $dbh->rollback }; if ($@) { warn stamp()."Rollback Unsuccessful because $@"; } else { warn stamp()."Rollback Successful"; } }` [download] This way everything is either commited if all is successful, or if anything dies within your eval block, or the script is aborted during the transaction, all the work done within the eval block is rolled back. Note that the subs that are called within the eval block call many other subs and touch many different tables. Be aware that if you have a mixture of tables being commited to (InnoDB and MYIsam) and the transaction is rolled back, only the transactional aware tables take the rollback. The Non-transactional tables keep whatever was done to them. As far as checking for errors and dying when appropriate, well that's up to you in your code.	[reply] [d/l]
Re^2: Making credit processing atomic by Anonymous Monk on Apr 12, 2005 at 19:02 UTC
I should have been clearer in my original post. The problem arises because a credit authorization/capture can execute successfully, but the database can still fail. I suppose I could automatically credit those transactions where the database fails, but what if I do if the credit doesn't execute properly? The issue I'm trying to overcome is making the RDBMS and the credit authorization act atomically. =)	[reply]


The stupid question is the question not asked
	PerlMonks