Search code examples
mysqlsqldjangodatabaseinnodb

MySQL select_for_update() and triggers .. how blocking works?

I have a row with an integer value, there're many concurrent requests on that row, I want that every read operation to be followed by an update operation (increment), and keep the data consistent, only one request can read->update at the same time.

I've made some research and figured out the select_for_update(), and I also thought of making a trigger on SELECT to increment the value AFTER it's selected, the question is .. Will that work as I'm assuming?

Is the trigger working in parallel or is it consistent this way?

Solution

  • The django select_for_update is a wrapper for the select ... for update feature present in most common databases (postgres, mysql, oracle...), which basically locks the selected rows against concurrent updates.

    PostgreSQL docs

    MySQL docs

    You need to wrap the lock into a transaction so the access will be locked until the execution of the transaction ends.