Group transactions in one stored procedure, or separate into two stored procedures?

Question

I have a complex web site that handles online games with real money. I use a double-entry database design for my transactions, a simple example is as follows:

John deposits $5 John receives 5000 credits John uses those 5000 credits to play a game.

The transaction in the database looks as follows:

trans_id | account_id  | trans_type | date | amount |
-----------------------------------------------------
1        | John(PayPal)| Debit      | date | -5.00  |
2        | System      | Credit     | date |  5.00  |
3        | SystemGame  | Debit      | date | -5000  |
4        | JohnGame    | Credit     | date |  5000  |

I wrote a stored procedure with a transaction in it that inserts transactions 1 and 2, the Debit from John's PayPal account, and the Credit to our System account.

My question is, should I also include the other transactions where John has money transferred from our SystemGame account to his Game account? Or should I have a stored procedure for each group of transactions? All 4 transactions occur simultaneously, John is credited immediately after depositing the $5.

Also, should I separate the transaction tables for game credits from the real money transaction table?

score 0 · Answer 1 · answered Aug 08 '12 at 14:50

I think this is what you want. But I recommend you doing in 2 different transactions to separate ingame-money and real-money.

The only reason is for if in future you need to change ingame-money management or real-money management you can do it separately.

score 0 · Answer 2 · edited May 23 '17 at 11:56

A transaction must be atomic: all the steps must be completed. If one fails, everything is rolled back.

If those 4 steps you mention must be all completed at once, they must be in a single transaction.

So, for me, your current approach is OK.

However, if you want to split things into 2 stored procedures, you can manage transactions from the language(PHP, C#) you use.

Check this: Transactions in MySQL - Unable to Roll Back and this: PHP + MySQL transactions examples

Group transactions in one stored procedure, or separate into two stored procedures?

2 Answers2