I wanted to run something by you guys. I am starting a new project which is roughly the following:
Important information: I am using PHP and MySQL
Every minute I get a list of to-do transactions from an API from different users.
Example:
user1 send $1 to user2
userx send $2 to usera
userw send $0.50 to user2
etc..
Lets say user1 wants to send $1 to user2. There are two posibilities, it's succesful or it's unsuccessful because there are insufficient funds or the user spelled the username wrong. If it's unsuccesfull I send out a message to the user.
I am now facing several options - please bare with me through my thought process.
Option 1
Create a database table with transactions that need to be processed and use a cronjob that processes them every minute. The risk here is that the script could run against an error or a timeout and the other transactions would still show In Progress in the database table. So I would need a second script to check that against a timestamp.
Option 2
Create an API or Function which gets called for each transaction after I receive them and brings me a response. From which then I can call another API or Function to deal with that response or move on to the next transaction. However I would still have to put them in a database table since I can't risk losing them if the script stops executing. So it would work as follow: put all transactions in database table - start transaction - when finished transaction delete from table - start transaction 2.
Both options are flawed because you don't know how long the list of transactions will be. If it's long PHP is definitely not optimal to run for a long time - using set timeout to zero is risky. I am looking to create a solution that will scale with PHP. So I was thinking about an Option 3.
Option 3 (Optimal solution?)
Use an API to return 10 transactions.
In database set a flag to say they are sent to a script and timestamp to say when they were sent
PHP script retreives 10 transactions from API - handles 10 transactions.
Once transaction completed - delete it from this table and copy it to completed transaction table.
Have a cronjob script check every 3 minutes if timesent is greater then X minutes (based on an upper bound of the execution time of 10 transactions). If it is greater - set them to not sent - so they can be sent out again.
As you can see I wrote down my whole thought process on this and am looking for input. There's bound to be stuff that I missed. Also please realize that these are not real financial transactions - it's just the best metafore I could use to make it clear.
Thank you very much,
Ice
