Yii2 database transaction behaviour to support repeatable read

Question

I have the following question. I have a web application (written in php Yii2), where multiple post requests are expected to hit the application server within a very short time. The business logic should be very strict, meaning that only the very first request's data should be inserted in the MySQL table, all the rest should be ignored. The clients are sending both the parent's and the latest child record's id in the post request. I use Yii's db transactions in this way.

$transaction = Yii::$app->db->beginTransaction();
$parent = ObjectParent::findOne(Yii::$app->request->post('parent_id')));
$latest_child = ObjectChild::findOne(Yii::$app->request->post('latest_child_id')));

if($parent->latest_child_id == $latest_child->id) {
    try{
        $new_child = $latest_child->createNewChild();
        $parent->setLatestChild($new_child->id);
        $transaction->commit();
    } catch{
        $transaction->rollback();
    }
}

In case the requests would income sequentially, than the second request would be ignored, because the latest child record's id would not match with the one coming from the client. But my problem is, that there are multiple rows inserted in the database. The database's isolation level is REPEATABLE READ, which should assure (according to my knowledge), that the rows which were read within the transaction are guaranteed not to change until the commit happens. If this is true, than it wouldnt be a problem, because it would make the second transaction to "break". The problem might be, that Yii might not use or is not aware of these DB locks, so doesnt know that the record is already part of a transaction, and makes the validation according to the current state of the object. The DB of course doesnt know anything about the validation rules, so it is fine from its point of view also.

My ideas to solve this:

set yii transaction explicitly to REPEATABLE READ also. This might change its behaviour. I doubt, because according to the documentation, without defining it explicitly, it uses DB default (REPEATABLE READ).
put validation logic a little bit later, closer to the commitm and after $parent->setLatestChildId($new_child->id); I dont know if it is a 100% solution, so I dont want to start to rewrite the tested code. Note, that the skeleton code above is only the simplified version of the original.
solve the whole thing with database triggers, so it would bypass the application context.

Please let me know what is the best practise in these scenarios. Unfortunately I am not that experienced in these concurreny issues, and its quite hard to test that and simulate concurrent requestes. thanks

score 1 · Answer 1 · answered Jun 06 '20 at 00:53

Repeatable read only assures that if you read rows in a transaction, re-reading those rows gets the same result. Another transaction may alter the results.

To put some locking on them the following are possible:

SELECT ... [LOCK IN SHARE MODE|FOR UPDATE]

However for your case of ensuring an insert of parent/child is unique, I recommend that (parent_id,child_id) being a unique or primary key in your table that way a duplicate insert will generate a duplicate key exception.

Yii2 database transaction behaviour to support repeatable read

1 Answers1