Mysql concurrent SELECT/INSERT

Question

I keep track of user credits in a creditlog table that looks like this:

user quantity action balance
1001     20     SEND   550
1001     30     SEND   520
1001     5      SEND   515

Now at first I tried to use Active Record syntax and select latest balance then insert a new line that computed the new balance. Then I found myself into a race condition:

user quantity action balance
1001     20     SEND   550
1001     30     SEND   520
1001     5      SEND   545 (the latest balance was not picked up because of a race condition)

Next solution was using a single query to do both:

INSERT INTO creditlog (action, quantity, balance, memberId) 
VALUES (:action, :quantity, (SELECT tc.balance from creditlog tc where tc.memberId=:memberId ORDER by tc.id desc limit 1) - :quantity, :memberId);

My script which tests this with 10 reqs/second would throw the following error for 2/10 queries:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction. The SQL statement executed was:
INSERT INTO creditlog (action, quantity, reference, balance, memberId)     
VALUES (:action, :quantity, :reference, (SELECT balance from (SELECT tm.* FROM creditlog tm where tm.memberId=:memberId) tc where tc.memberId=:memberId ORDER by tc.id desc limit 1) -:quantity, :memberId, :recipientId);. 
Bound with  :action='send', :quantity='10', :reference='Testing:10', :memberId='10001043'.

Shouldn't the engine wait for the first operation to release the table then start on the second one?

Does my issue relate to: How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction' ?

How can I avoid this situation and turn the concurrent requests into sequential operations?

Answer 1

Here is a working solution, may not be the best one so please help improve.

Since transactions don't block other sessions from SQL SELECTs, I used the following approach:

LOCK TABLES creditlog WRITE;
//query 1 extracts oldBalance
"SELECT balance FROM creditlog where memberId=:memberId ORDER BY ID DESC LIMIT 1;";
//do my thing with the balance (checks and whatever)

//query 2
"INSERT INTO creditlog (action, quantity, balance, memberId) 
VALUES (:action, :quantity, oldBalance- :quantity, :memberId);
UNLOCK TABLES;

Result:

mysql> select * from creditlog order by id desc limit 40;
+--------+-----------+----------+---------+----------+---------+---------------------+-------------+------------+
| id     |  memberId | action  | quantity | balance | timeAdded |
+--------+-----------+----------+---------+----------+---------+---------------------+-------------+------------+

| 772449 | 10001043 | send    |    10.00 |    0.00 | 2013-12-23 16:21:50 | 
| 772448 | 10001043 | send    |    10.00 |   10.00 | 2013-12-23 16:21:50 | 
| 772447 | 10001043 | send    |    10.00 |   20.00 | 2013-12-23 16:21:50 | 
| 772446 | 10001043 | send    |    10.00 |   30.00 | 2013-12-23 16:21:50 | 
| 772445 | 10001043 | send    |    10.00 |   40.00 | 2013-12-23 16:21:50 | 

Answer 2

Solution #2

Since I was already using Redis, I tried out a redis mutex wrapper: https://github.com/phpnode/YiiRedis/blob/master/ARedisMutex.php

This gives me a lot more flexibility, allowing me to lock only specific segments (users) of the table. Also there is no risk of deadlocking since the mutex automatically expires after X (configurable) seconds.

Here's the final version:

 $this->mutex = new ARedisMutex("balance:lock:".$this->memberId);
 $this->mutex->block();

 //execute credit transactions for this user

 $this->mutex->unlock();