As it turned out, everyone knows, but not everyone understands. Transactions in mysql and SELECT FOR UPDATE
As a result of my service, I sometimes have to conduct interviews for the positions of "[senior | junior] python / django developer", "team lead". To my great surprise, I found that 9 out of 10 job seekers with a summary of the words “Mysql / Innodb / transactions / triggers / stored proc etc.” could tell absolutely nothing about their past experience working with them. Unfortunately, I have not received a single description of the use case.
Further on the interview, I suggested trying to propose a solution for the following situation:
Suppose we are an online service, which 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. A user in our system creates a request for activating a service, fills in all the fields, and on the last page presses the “Activate service” button. That is, at the time of sending the HTTP request, we have a record in our database (request to activate the service). What is our algorithm? - I ask and I continue:
- we get the user's balance from the database;
- if there is enough balance, then pull the API;
- if everything is fine, then we deduct the amount for the service from the balance sheet, do UPDATE, commit, otherwise roll back;
- respond to the user.
Everything seems to be trivial, but when I present 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), the most sophisticated solutions begin to offer, starting from 5 choices (I must admit, I I didn’t understand anything in this option), the use of auto-increment counters, external caches, new tables in the database, slips, and don’t understand what.
As you know (and all the candidates knew that!), Innodb in mysql provides a transactional mechanism and the possibility of line-by-line locking. In order to apply this same line lock, just add the FOR UPDATE expression at the end of the SELECT, for example:
SELECT * FROM requests WHERE id = 5 FOR UPDATE
The transaction will start and all other sessions to the database will not be able to fulfill a similar request until the completion of our transaction, they will simply wait. For reading, the 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 the autocommit turned off, since no matter what you locked, the lock will be removed after the first update.
It seems a trifle, kind of obvious, but 9 out of 10 ...
upd
former name "Transactions in mysql», is not disclosed in the article has been replaced with "Transactions in mysql and SELECT FOR UPDATE"
Threat
is not mentioned in the article that the API is necessary to pull within transactions and what to do in case of failure and how to handle exceptions.
Further on the interview, I suggested trying to propose a solution for the following situation:
Suppose we are an online service, which 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. A user in our system creates a request for activating a service, fills in all the fields, and on the last page presses the “Activate service” button. That is, at the time of sending the HTTP request, we have a record in our database (request to activate the service). What is our algorithm? - I ask and I continue:
- we get the user's balance from the database;
- if there is enough balance, then pull the API;
- if everything is fine, then we deduct the amount for the service from the balance sheet, do UPDATE, commit, otherwise roll back;
- respond to the user.
Everything seems to be trivial, but when I present 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), the most sophisticated solutions begin to offer, starting from 5 choices (I must admit, I I didn’t understand anything in this option), the use of auto-increment counters, external caches, new tables in the database, slips, and don’t understand what.
As you know (and all the candidates knew that!), Innodb in mysql provides a transactional mechanism and the possibility of line-by-line locking. In order to apply this same line lock, just add the FOR UPDATE expression at the end of the SELECT, for example:
SELECT * FROM requests WHERE id = 5 FOR UPDATE
The transaction will start and all other sessions to the database will not be able to fulfill a similar request until the completion of our transaction, they will simply wait. For reading, the 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 the autocommit turned off, since no matter what you locked, the lock will be removed after the first update.
It seems a trifle, kind of obvious, but 9 out of 10 ...
upd
former name "Transactions in mysql», is not disclosed in the article has been replaced with "Transactions in mysql and SELECT FOR UPDATE"
Threat
is not mentioned in the article that the API is necessary to pull within transactions and what to do in case of failure and how to handle exceptions.