Mysql Transaction: Effect on other Transaction query

Question

I am writing an application which shall track the financial transactions (as in a bank), to maintain the balance amount. I am using Denormalizing technique to keep the performance in check(and not have to calculate the balance at runtime) as discussed Here and Here.

Now, I am facing a Race Condition if two people simultaneously did a transaction related to same entity, the balance calculation as discussed above, shall return/set inconsistent data, as discussed Here and Here, And also as suggested in the answers..

I am going for Mysql Transactions.

Now My question is,

What Happens to the other similar queries when a mysql Transaction is underway?

I wish to know if other transactions fail as in Error 500 or are they queued and executed, once the first transaction finishes.

I also need to know how to deal with the either result from the php point of view.

And since these transactions are going to be an element of a larger set of operation in php with many prior insert queries, should I also device a mechanism to roll-back those successfully executed queries too, since I want Atomicity not only as in individual queries but also as in whole operation logic(php).

Edit 1 :- Also, if former is the case, should I check for the error, and wait a few second and try that specific transaction query again after some time?

Edit 2 :- Also Mysql Triggers is not an option for me.

No, but the point system we have is none the less, it eventually goes on to be withdrawn in real cash, but a manual verification is in place, and the cash can only be withdrawn offline. — Mohd Abdul Mujib, May 18 '15 at 21:19
Whatever answer you get - the only important point is: If your bank will implement your code, Do you trust your code to always work correctly with your bank account? — Ryan Vincent, May 18 '15 at 22:21
If you are materializing balance amount for performance purposes (and easier overview), then you don't let X possible processes do that. You have only one do it, ever. Therefore, no race condition can occur. You have multiple tools at your disposal - queues, mysql event scheduler to do it on a time basis etc. It's difficult to steer you properly in the right direction since the whole accounting you're dealing with is unclear. — N.B., May 18 '15 at 22:23
@RyanVincent Of Course, I sure do 'trust' my code, and as I explained in the question, I am striving to make it least erroneous as possible, while all the input validation taken care of, I am now pondering over the points where an error might creep in, and at the moment this seems to be the point. Thanks though, for your comment. — Mohd Abdul Mujib, May 18 '15 at 23:13
@N.B. Well, yeah. That is exactly what I am doing, the balance part is just one query, but there are various preceeding queries like if a customer purchased some product, Add the product to that customers shopping cart so a query for that (query1), Deduct the no of product available(query2), deduct the amount from that users balance(query3), credit the amount in system balance(query4) something similar... — Mohd Abdul Mujib, May 18 '15 at 23:17
Wasn't trying to be obtuse - you need to be sure that your code works with money correctly - always, Speed is not important - accuracy is. That I should have made clear. Ok, 'speed' is 'nice as well'. — Ryan Vincent, May 18 '15 at 23:19
Usually the other applications are locked out or see the unchanged data , depends on what kind of transaction you are doing. — Tim3880, May 18 '15 at 23:20
@N.B. Also the race condition I mention of is what if two users did the same thing, its better explained in this questions second paragraph. http://stackoverflow.com/questions/15026825/php-mysql-how-to-prevent-two-requests-update — Mohd Abdul Mujib, May 18 '15 at 23:40

score 1 · Accepted Answer · answered May 19 '15 at 01:27

With code like this, there is no race condition. Instead, one transaction could be aborted (ROLLBACK'd).

BEGIN;
SELECT balance FROM Accounts WHERE acct_id = 123 FOR UPDATE;
if balance < 100, then ROLLBACK and exit with "insufficient funds"
UPDATE Accounts SET balance = balance - 100 WHERE acct_id = 123;
UPDATE Accounts SET balance = balance + 100 WHERE acct_id = 456;
COMMIT;

And check for errors at each step. If error, ROLLBACK and rerun the transaction. On the second time, it will probably succeed. If not, then abort -- it is probably a logic bug. Only then should you give http error 500.

When two users "simultaneously" try to do similar transactions, then one of these things will happen:

The 'second' user will be stalled until the first finishes.
If that stall is more than innodb_lock_wait_timeout, your queries are too slow or something else. You need to fix the system.
If you get a "Deadlock", there may be ways to repair the code. Meanwhile, simply restarting the transaction is likely to succeed.

But it will not mess up the data (assuming the logic is correct).

There is no need to "wait a second" -- unless you have transactions that take "a second". Such would be terribly slow for this type of code.

What I am saying works for "real money", non-real money, non-money, etc.; whatever you need to tally carefully.

Thanks for answering, you mean to say, that the queries shall be queued internally(since my queries shouldn't take more than a few micoseconds), and the end users shall not have to re initiate the query, and it will all be seamless when viewed from outside? — Mohd Abdul Mujib, May 19 '15 at 02:08
"Lock wait" is seamless, except for an imperceptible delay. "Deadlock" is fatal to one of the transactions, hence the need to check for errors and replay the `BEGIN...COMMIT`. The example I gave could deadlock if one connection is moving 'money' from account#123 to 456, and _simultaneously_ another connection is moving money the opposite direction. — Rick James, May 19 '15 at 08:18

Mysql Transaction: Effect on other Transaction query

What Happens to the other similar queries when a mysql Transaction is underway?

1 Answers1