Why don't two parallel transactions block each other?

Question

Yii

I have a simple controller:

public function actionIndex()
    {
  $model = Counter::model();
  $transaction = $model->dbConnection->beginTransaction();
  $Counter = $model->findByPk(1);

  $Counter->value++;
  sleep(10);
  $Counter->save();
  $transaction->commit();

  $this->render('index');
    }

Initially, the "value" field in the Counter table is 0. So, if you run such a controller twice and simultaneously, then when both controllers are executed, the "value" field will have the value 1. It turns out that transactions with simultaneous execution of two controllers do not block each other and are executed in parallel. As a result, they both get zero, wait 10 seconds, and then write the updated value, that is, 1. But I need the result to be a two. That is, so that when the first transaction starts, the second one would block and after the completion of the first, the second one was launched, receiving the updated value (1), would increase it by another 1 and already write the value "2" in the field.

Answer 1

I think you can see the idea here: dr-magic.blogspot.com/2010/01/3.html
and another resource: https://msdn.microsoft.com/en-us/library/aa0416cz(...
i.e. , when saving, you must first check if the version of the record that was taken by the transaction at the beginning matches the version that is on the server now.If not, re-make the transaction with updated
data.in Gii, the generator for yii2, there is even a field for such needs - lock is
called.Also, speaking specifically about your situation, you can redefine ActiveQuery - so that it performs an update in this style -
UPDATE Table1 Set Col1 = :new_val1, lock = :old_lock + 1
WHERE (lock = :old_lock)

Answer 2

Let's say you're using MySQL....
If I'm wrong about something, please correct me.

Answer 3

Everything works correctly and as expected for you, and transactions have nothing to do with it at all: the actual change in the value in the database occurs after 10 seconds (save + commit). By starting 2 processes, they both count 0 at the same time, change it to 1, and both after 10 seconds save this unit in the database - naturally, there will be 1.
You can save changes immediately after changing to 1, but this will not solve the problem , since in fact another change transaction will be available only after the commit (I advise you to read about transaction isolation levels https://habrahabr.ru/post/135217/ to understand why this happens)
A version control database could help - when trying to overwrite obsolete data such a database would return an error. But MySQL is not such a database (however, you can do it yourself).
What you need is for the update to go sequentially, not in parallel. This is the queue. You can make it yourself, you can use a bunch of ready-made tools, you can use mutexes (although, IMHO, this is dubious advice in the context of php).
If we are talking purely about counters, I would recommend you Redis with its incr (it is single-threaded and there are no such problems in principle).
If this is just an example and a question about how not to mess up important data while changing it - read about transaction isolation levels and make data versioning.