How can I stop 2 concurrent processes in mysql to avoid avoid duplicate payments?!

[ConfusedOfLife]

Hi

Lemme explain the full scenario for you at first. I’m writing an online investment program. Clients should be able to put their money in the web site for a special period, let’s say a month, and after it by the confirmation of the administrator, their money + their interest will be returned to them. The problem that I’m facing is the same old 2 concurrent threads running against each other problem, something that’s been the major problem in OS developments!

Let’s say I have a field called “paid” in my investment table. Tonight when the admin gets online and visits his administration page, he sees that 4 persons are eligible to get their money back. He checks the checkboxes for all these 4 ppl and confirms that they get their money back. When the form is posted to the server, my PHP script checks to see if the “paid” field for these records is not “1”, if it’s “0”, then it starts to pay the money from web site account to these ppl’s credit cards. Let’s say at the same time (before the admin selects these records and submits the payment form) he opens the same page in another browser. So, he’d see the same 4 ppl in there too, and he checks these ppl and submits these 2 forms at the same time?! This is a slight possibility that my script pays a person twice! Let’s say how:

First of all I have two big problems that I think do not let me use transactions; or better to say transactions become completely worthless in my case:

1- I’m communicating with the bank gateway (e-gold), and I’m sending money to it, when I send money to a person’s e-gold account, there is NO WAY to get it back! So, I can’t do any ROLLBACK!

2- There are multiple threads running concurrently, which means even if I wasn’t communicating with another gateway, transactions couldn’t help me, because transactions follow the ACID rules, and the “I” here means “Isolations”, which means before a transaction is committed/rollbacked, no changes are visible to other threads!

Basically my problem is that I’m really afraid that 2 threads read “paid” field at the same time when it’s still zero and start their work to pay the user!

What did I come up with myself? Well, I thought of several solutions for my problem:

1- I use another field for accounts table like “block”, so, when a thread gets a payment request, it firsts checks to see if this field is 1, if yes, it should abort, if not, it sets it to 1 and starts the payment process. But this solution doesn’t work either, because: 1- We still have the same problem! What if 2 threads read it simultaneously when it’s zero, and both UPDATE it to 1?! So, they can still start the same process at the same time! 2- What if the working thread forgets to UPDATE the block field to 0 when a problem comes up that it can not pay the money? This way the poor person never gets his money back because his thread is locked forever! Of course I can run a cron job every night for example to UPDATE investments SET block=0 WHEN block_date > 24 hours ago! But it’s dirty work, I mean I wana do everything at database level and it’s what databases are for!

2- I use GET_LOCK() function. By using GET_LOCK() at the beginning of my thread, I can lock the whole database till my process is finished and I can be sure that no other thread can unlock the database till the whole job is done. This method is so safe because MySQL itself promises that it does everything by itself, But you know what? It’s better if I could lock the records I needed, not the whole database! This way the poor admin won’t be able to have 2 concurrent processes at the same time even for those records that really do not go against each other!

3- I use another table called “block”. This table has 2 fields: account_id, date_time; by account_id being the primary key. So, whenever my script receives several record IDs to work on, first of all it tries to insert them all in “block” table. If the operation is successful it means that all the records we want are free and the thread can work with them! So, it starts its work and after the whole job is finished, it DELETE FROM blocks WHERE account_id IN ( $IDsArray). Yes, as you noticed yourself this way is really close to my first solution, but it has one big advantage: instead of using UPDATE to set the block field to 1, I use another table and use INSERT. What’s the difference? Well, MySQL guarantees that even if 100000000 threads are running at the same time for the same set of ID, they can’t insert it all at the same time in “block” table, because “account_id” is primary key and doesn’t let duplication! So, the first lucky thread can insert the ID and start its work, but other threads get an error message which means they have to wait! I still have the same problem of the CRON job, that should check this table every 24 hours and deletes the records that are older than 24 hours old. Even though this trick make my program error prone almost 99.99999999%, but classically we still have the same problem: what if a process takes a time more then 24 hours?! In this method I’m really relying on the fact that my PHP process doesn’t take 24 hours!

What do you think guys? I know this is a really really long post, but this is a design architecture that I wanted to share with you to see your point of views and potential solutions.

Thanks a lot

[mcrilly]

I don't have time to read all your solutions but...

This is the same as your first solution, but more complex...
Why, once a customer has been confirmed to have their money back, not change the "paid" value to "1" and another field, say "locked" to some sort of thread ID that's unique to that thread (which could be generated when the thread starts to process the "refund")

Now, when your thread is processing the "refund" back to the customer, part way through the process (before actually sending the money/request for the money) the thread can check for this "locked" field, and if the value is different from their unique ID number (which is distroyed after the process, but still stored in the DB) then they know another thread is processing this transaction and can abort. I think this might work :O)

Good luck! :O)

[raf]

i think it's realy very simple:
this sort of processes don't need to be real-time, so you can just queu the paymentorders and then execute them all at once.

So if the admin says 'pay for account 256' then 256 is added to the collection of records that will be processed in the daily batchproces.
Your collection can be a table where you have like

paymentID|accountID|amount|currency|ordergivenat|orderexecutedat

you then create a unique index on the accountID

when you proces the paymentorders from the admin, you do a REPLACE INTO istead of an INSERT INTO.
If the admin gives 100 paymentorders for the same account, then the table will still only contain the last paymentorder for that account.
this has some important advantages. the admin can for instance cancel the order until the application is disabled for that day. if mistakes are made, then just give a new order with the right amount, and everything will be solved.

then to actualy proces the payments, you lock the table with the accountdetails, and start processing your paymentorderstable. After each processed record, make sure to update the "orderexecutedat" column !!!
When all paymentorders are finished, unlock the table with the accountdetails.

Personally, i would disable all db-interactions from the website durig a fixed period at night to avoid any possible interferance, but a tablelock should do.

[Kiwi]

If you can't batch the process (a better solution), then something like this might work:
1. Use an intermediate staging table to store transactions before sending them off. I would also use temporary tables to run the live data on the website. This is where you capture everything and validate/authenticate it before completing the financial transaction.
2. Run the website transactions off a temporary table - when someone accesses a page, put the working data in a temporary table.
3. Have 'last update' fields in all three tables. Make sure this is updated when anything is done on the table (I would also use a timestamped transaction history table, but let's not get ahead of ourselves). Then compare the last updates before committing to any transaction.

Your data flow would look something like this:
When the admin goes to the page, a temporary table is created.
When the admin decides to pay them, this is recorded in the temporary table.
The temp table is then compared to the base table.
If there are any differences, the transaction is bounced back to the admin, updating the temp table.
If there are no differences, then the base table and the staging table are written (and the transaction history, if you've gone down that route).
Finally, staging table can be used to send the transaction to e-Gold. If you're really worried, you could validate this against the transaction history as well.
Steps 3-5 are the critical ones and can be bound in a database transaction. Step 6 will involve several sub-steps, which should also be tied up in a transaction.

[mcrilly]

Hi,

I think RAF's solution is excellent, and is one used by IBM on our (I work for O2 UK) MSISDN (mobile phone number) processing DB!

IBM slap thousands up on thousands (if not millions) of MSISDN's in to a DB and lock it. They then process the DB and bar (restrict the service on that handset) every handset in that DB. This is also the method used to UNbar MSISDN's too.

It's an excellent method I'll be using myself when the time comes. I do think some of Kiwi's methods can be incorperated to make RAF's method a little more secure, especially the comparing a tempory table to the main table.

Good work RAF and Kiwi! I'm impressed ;O)

[ConfusedOfLife]

Hi

Thanks to you all because of your fast replies. Well, as you mentiond mcrilly, your solution doesn't fully solve the problem, because when you SELECT the value of block field and after making sure that it's empty, you UPDATE your new generated block code, some other thread might UPDATE its own code just between your SELECT & UPDATE queries!

Yes, raf's way is the coolest like alway! Thank you raf. But I have one question: How can I block the web interaction with MySQL during the cron job process? I mean if I use a sort of flag that the server set's it on when it starts its job and I always check from the web site that if this field is on, my script terminates, then it's always the possibility that my cron job doesn't finish successfully and it can not set the flag to zero again. And if I guess a sort of delay, like 2 hours, then it might be more for some nights or less for some other nights. I still think using the GET_LOCK() function is the best (should I say only?) way I have. In my cron job I call it to lock everything, but in my web script I only check it to see if it's on, but if not, I don't call it, I just do my work, huh?

Thanks to you too Kiwi, but to be honest, I really didn't understand your post that much.

Thanks again

[raf]

Like you say, there are 2 options:
A) a fixed timeperiod.
B) a flag

A) this is the easiest. inside your connectionfile, you just need to include a checkt that will compaire the current time to see if it's outside the set interval (from 00.00 to 01.00 hour?), and will only open the connection if the current time is outside it.
now, what if your batch stopped during execution ?
Multiple options : with the curent setup that i outlined in my previous posts, notting realy can go wrong. If the batch didn't finish, then there will be a number of records left without an "orderexecutedat". Your batchproces should proces all records with "orderexecutedat" = Null, so they wil just be handled the next time that the batch is ran.
i do a similar thing for scheduled mailsending : if the batch doesn't run/gets stuck,then the remaining mailsare processed the next time. I also write a record to a cron_log table that indicates when the cron initiated the batch, when it was finished and how many records were processed + encountered errors and actions (like 'invalid mailadress for user xxxx, action=skip' or 'unable to select db, action=abend' etc). I then have a screen in my adminsection that displays these data.

So the only strange thing here, would be that the payment is delayed a day (or till you restart the batch) + that an admin could change the amount or cancel a payment that normally allready should have been processed (you could counter that by including a 'updatable_until' column and check on that before the REPLACE INTO) but these are operational issues. the integrity of your data and processes isn't compromised.
If you would include an 'updatable_until' column, then you could update that column to "Now()" after the payment is processed (together with the "orderexecutedat" column !!! which should still be your check to select the records that need to be processed)
You could then also use that column to allow admins to schedule payment in advance (by only processing records "where orderexecutedat=Null and updatable_until<Now()")

With this 'updatable_until' and by including it in the where-clause + testing on it before the REPLACE INTO, you wouldn't even need to close off the webapplication because you could still add new orders or edit untreated ones, while the batch is running. But in my opinion: batches should run on a locked table or, if you would need to run multiple (sequenced) batches at once, on a table that is not accesible by other clients then the one that manages the batch.
My advice and practice: only run batches on tables that can only be manipulated by the batch.

A bigger problem with this approach: what if the batch hasn't finished before the db is unlocked for the application...
You can not handle this without having the batch set som sort of flag somewhere, but then you have sollution B, because you need to check if this flag is set befor opening/using an connection.

B) This can be done with 2 ways: set the flag in a file (a configurationfile for your app, if you already have one) or inside the db (for instance, if you set up this cron_log table, then you can see if an enddate is set (--> create the record at the start of the batchproces, update it at the end)). But checking a value in the db has the disadvantages that you need to run an extra select before each db-manipulation + that you can't realy lock the application out.
So setting a value in a file is probably the best way.
(all depends of course on if you need to alow read-acces to the db during the lockout period or not)

Important note : do not try to save querys by updating all processed records at once, at the end of the batch !! you need to update each individual record as soon as it is processed ! You can select all records that need to be processed at once, but realy need to update all records seperately as soon as they are processed.

[ConfusedOfLife]

Hey raf

I'm just posting this to thank you for all your efforts and time you put for me and the rest of members. That "Helpful member award" that you have is truly yours and the time you spend on giving solutions and making the coder feel that he/she's not alone on his/her way can not be appriciated in anyway. There are lots of ppl who answer questions, but you're one of those few ones who spend his time on long posts, especially those which focus on the design of a project, not only those posts that merely ask for a simple piece of code.

Keep on with your good work & helping the great Open Source community, it's because of you and ppl of your type that new comers feel easy in PHP and do not go through other closed source but easier to implement/learn languages/technologies.

Thank you man, thank you,
bijan

[raf]

you're welcome bijan. :thumbsup:

i like solving difficult problems like design-issues or the sort of processes like in this thread and sometimes get caryed away (hence the long posts :) )

i think that this is what open source is all about: developpers saving eachother time by offering advice, best practices, distributing code that has some general use.


happy coding!