As it turned out, everyone knows, but not everyone understands. Transactions in mysql and SELECT FOR UPDATE
On duty, I sometimes have to hold interviews for the position "[senior | junior] developer python / django", "team leader". To my great surprise, I found out that 9 out of 10 applicants, in whose summaries the words "Mysql / Innodb / transactions / triggers / stored proc etc." appear, can tell absolutely nothing about their past experience of working with them. Unfortunately, I never received a single description of the use case.
Further on the interview I suggested to try to suggest a solution for the following situation:
Suppose we are an online service that in turn uses some kind of external paid API (service activation, paid content, or whatever your heart desires), that is, our service pays money for using the API itself. A user in our system creates a request to activate the service, fills in all the fields and presses the “Activate service” button on the last page. That is, at the time of sending the HTTP request, we have an entry in our database (a request to activate the service). What is our algorithm? - I ask myself continue:
- we get user balance from the base;
- if the balance is enough, then we pull the API;
- if everything is good, then we deduct the amount for the service from the balance, we do UPDATE, commit, otherwise we roll back;
- we respond to the user.
')
It seems to be trivial, but when I cite the first and most obvious problem in the form of 10 competitive requests (that they all get the same balance at the beginning and start calling the API), solutions start to offer the most sophisticated ones, starting from the execution of 5 selections (I have to admit I did not understand anything in this version), the use of auto-increment counters, external caches, new tables in database, slips, and still don’t understand what.
As you know (and all the candidates knew it!), Innodb in mysql provides a transactional mechanism and the possibility of line-by-line blocking. In order to apply this most line-wise lock, it is enough to add a FOR UPDATE statement to the end of the SELECT, for example:
SELECT * FROM requests WHERE id = 5 FOR UPDATE
A transaction will start and all other sessions to the database will not be able to perform a similar request until the completion of our transaction, they will just wait. For reading, the same record will be available in a state that depends on the isolation level of the transaction.
It is also worth noting that the use of FOR UPDATE is best done with autocommit turned off, since regardless of what you have done, the lock will be removed after the first update.
It seems a trifle, it seems obvious, but 9 out of 10 ...
upd
the former name “Transactions in mysql”, not disclosed in the article was replaced by “Transactions in mysql and SELECT FOR UPDATE”
PS
The article does not say that the API should be pulled within the framework of a transaction and what to do in the event of a failure and how to handle exceptions.
Further on the interview I suggested to try to suggest a solution for the following situation:
Suppose we are an online service that in turn uses some kind of external paid API (service activation, paid content, or whatever your heart desires), that is, our service pays money for using the API itself. A user in our system creates a request to activate the service, fills in all the fields and presses the “Activate service” button on the last page. That is, at the time of sending the HTTP request, we have an entry in our database (a request to activate the service). What is our algorithm? - I ask myself continue:
- we get user balance from the base;
- if the balance is enough, then we pull the API;
- if everything is good, then we deduct the amount for the service from the balance, we do UPDATE, commit, otherwise we roll back;
- we respond to the user.
')
It seems to be trivial, but when I cite the first and most obvious problem in the form of 10 competitive requests (that they all get the same balance at the beginning and start calling the API), solutions start to offer the most sophisticated ones, starting from the execution of 5 selections (I have to admit I did not understand anything in this version), the use of auto-increment counters, external caches, new tables in database, slips, and still don’t understand what.
As you know (and all the candidates knew it!), Innodb in mysql provides a transactional mechanism and the possibility of line-by-line blocking. In order to apply this most line-wise lock, it is enough to add a FOR UPDATE statement to the end of the SELECT, for example:
SELECT * FROM requests WHERE id = 5 FOR UPDATE
A transaction will start and all other sessions to the database will not be able to perform a similar request until the completion of our transaction, they will just wait. For reading, the same record will be available in a state that depends on the isolation level of the transaction.
It is also worth noting that the use of FOR UPDATE is best done with autocommit turned off, since regardless of what you have done, the lock will be removed after the first update.
It seems a trifle, it seems obvious, but 9 out of 10 ...
upd
the former name “Transactions in mysql”, not disclosed in the article was replaced by “Transactions in mysql and SELECT FOR UPDATE”
PS
The article does not say that the API should be pulled within the framework of a transaction and what to do in the event of a failure and how to handle exceptions.
Source: https://habr.com/ru/post/238119/
All Articles