1

I have a service which makes some manipulations in DB, everything is working normally until I tried to make two requests in parallel.

Below see the sql debug log:

Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): START TRANSACTION;
Executing (f49986af-9741-4f20-94ba-0644fbafea05): START TRANSACTION;
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
Executing (f49986af-9741-4f20-94ba-0644fbafea05): INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1
Executing (f49986af-9741-4f20-94ba-0644fbafea05): UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): COMMIT;
Executing (f49986af-9741-4f20-94ba-0644fbafea05): ROLLBACK;

Deadlock found when trying to get lock; try restarting transaction

I don't understand why a transaction does not wait for another to end, but a Deadlock exception is thrown?

Thanks.

  • Because you have not locked all tables which you want to alter in transaction. – Akina Oct 24 '18 at 13:42
2

Databases are designed to support multiple concurrent users. "Waiting for the other transaction to finish" is mutually exclusive with that requirement. The ability to support multiple concurrent users is why you use a Database instead of flat files.

You must design your algorithm accordingly.

In your example, you want to serialize this process. (note: Serialization is the same as setting the configuration parameter SLOW=TRUE)

You can serialize this process by putting a Row Lock on Table_2 first. In MySQL (and other RDBMS), you do that by using the SELECT ... FOR UPDATE syntax at the beginning of the transaction.

The process should look like this:

START TRANSACTION;
select count from test_2 where id=1 for update;
INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1;
commit;
  • In our use case, we don't need actually to serialize the process because this leads to an extra selection. SQL for update contains only fields from db: UPDATE table_2` SET count=count + 1 WHERE id = 1` which don't lead to inconsistency of data if it runs non-serial – Darii Petru Oct 24 '18 at 14:46
1

Sometimes InnoDB takes the pessimistic approach. To do the "right" thing might be so complex that it is not worth doing so. Live with it.

When you get a deadlock, rerun the transaction. That is usually the "right" solution. This is simply part of dealing with transactions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.