AR Right way to use concurrent updates

Yii 2.0 General Discussions
1

Hello,

very common case, there two parallel HTTP requests and booth try to update the same resource.

Request#1

select balance from user where id=1; # balance = 0

update user set balance = balance + 100 where id = 1;

After that we have 100 on balance (looks like)

Request#2

select balance from user where id=1; # balance = 0 ! OOPS

update user set balance = balance + 200 where id = 1;

Of course we need to use transaction to get correct balance. Lets try that.

Request#1




$transaction = Yii::$app->getDb()->beginTransaction('SERIALIZABLE');

$userModel = User::findOne(1);

$userModel->balance += 100;

$userModel->save();

$transaction->commit();

Request#2




$transaction = Yii::$app->getDb()->beginTransaction('SERIALIZABLE');

$userModel = User::findOne(1);

$userModel->balance += 100;

$userModel->save();

$transaction->commit();

But with same result. Where my mistake?

2

Please check this section of the guide.

Optimistic Locks

3

but what wrong with transactions -




$transaction = Yii::$app->getDb()->beginTransaction('SERIALIZABLE');

...

?

4

I’m very sorry, I didn’t think about the transaction.

5

So, I tested it myself with raw sql on my MariaDB and this is what I found out:




session A                                      session B


set transaction isolation level serializable;

start transaction;

select a from t where id = 1;

[result = 1]

update t set a = 2 where id = 1;

[done right away]

select a from t where id = 1;

[result = 2]


                                               set transaction isolation level serializable;

                                               start transaction;

                                               select a from t where id = 1;

                                               [result = 1 ... reading is not blocked]

                                               update t set a = 10 where id = 1;

                                               [suspended ... writing is blocked]

commit;

                                               [... done]

select a from t where id = 1;

[result = 2]

                                               select a from t where id = 1;

                                               [result = 10]

                                               commit;

select a from t where id = 1;

[result = 10]

                                               select a from t where id = 1;

                                               [result = 10]

Looks like it isn’t working as you are expecting.