Using API/Function calls or going for the Cron Job approach - An inquiry into dealing with transactions - How would you handle it?

Question

Hey Everyone,

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

Answer 1

I would suggest processing each transaction when the request for that transaction is received.

I.E Bob clicks a 'Send money' button to send $20 to Alice. I would call a function to handle this transaction, and show them a 'Transaction successful' or 'There was a problem..' message right there.

If a transaction processing will take long, you could have it done via AJAX, showing them a nice progress bar saying 'Processing..' while the processing is being done on the server side, and then either redirect them to yoursite.com/transactions.php?result=success&id=$id or yoursite.com/transactions.php?result=failure&id=$id

Answer 2

If you want to do it using the bare minimum variance from your current technology stack you're on the right track. Essentially you're re-creating a bare bones MQ or job server.

Minimum features you need for job/task/transaction queue are:

• the job (user1 send $2 to user2)
• the state (ready, out for processing, error, done)

You're also probably going to want

• a last error string (so you can figure out what the hell happened)
• possibly a retry count (for tasks that should be retried before failing, anything that might fail due to transient errors)

If you decide to parallelize your processing cron job, you'll want to track which instance of the script has a job out for processing, especially if you start operating on large batches. (and if you do that, you're going to want to watch to make sure any given transaction finishes fast, or you can effectively stall all the jobs behind a single slow job)

Whether you fetch it from an API or straight from the database is 6 of one, half a dozen of the other.