Hello. I read a lot of articles on transactions and locks in MySQL, but so far I haven’t understood what to use for my case. There is a clients table with a balance field, where the user's balance is recorded. The user in the interface enters the amount he wants to write off and as a result, the balance field must be updated for the client. How to protect yourself from the fact that two processes can simultaneously read and write data in one line. InnoDB table. Thankful in advance for the help in this question!

    1 answer 1

    There is an explicit and not explicit blocking.

    • Explicit is used directly by the database engine when writing, reading, updating data.
    • An implicit lock is set directly by the user.

    Since you have an InnoDB storage mechanism, implicit locking will occur at the row level.
    If you want your data to be correctly written and read, then before writing this data, explicitly set the lock (Set on the entire table). And then take it off. Example:

    LOCK TABLES clients READ LOCAL; ... производим запись (insert, update)... UNLOCK TABLES;

    Here:

    • LOCK TABLES - sets the table lock
    • clients - the name of the table being locked
    • READ LOCAL - lock type (locks the table for reading, but allows data insertion)

    PS here in more detail

    • Lukmanov, and why the type of lock READ LOCAL? Shouldn't WRITE be used? After all, if I correctly understood READ LOCAL, if someone read the table before it was locked, then the same process will be able to update the data in it - Baurzhan
    • @Baurzhan Better not follow this advice. Complete table locking, if you need to change one record, is to shoot a cannon at sparrows. when locking a table, no one else can work with it. Get a specific record with the balance of the user with the phrase for update , then only this line will be blocked and another process that reads it specifically for the same purpose will wait, the rest will work. dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html - Mike
    • @Baurzhan, yes you would be better to use the type of blocking WRITE. And look at Mike's comment. If your database will work in a multi-user environment with a large number of simultaneous transactions, it is better to use for update. And yes, you need to consider the case of deadlocks even more in detail. - Lukmanov