Can Django application run multiple atomic transactions each locking/modifying only a specific row, as opposed to the whole table, on my MySQL database?
We are running a Django server with RabbitMQ and Celery. We have Parent objects which are tied to Children (These objects are actually the same Model, but used differently). When new children are added, they perform a merge operation which updates the parent. If we receive children A, B, and C, we make a new merged version for each. A, then A+B, then AB+C. The current state of this process can be seen on the Parent object. The trouble comes if a second child arrives before the merge process for the first has concluded. This results in [A, B, B+C] instead of [A, A+B, AB+C]. Is there a way to lock the row of one Parent object only, while still allowing multiple separate Parent/Child operations simultaneously?
You should be able to use a select_for_update to retrieve the rows for your merge operation.
so I do lock the first parent object with the select_for_update call for its merge, however while the transaction is running, i get a 2nd and 3rd parent objects from a different query submission and what it seems to be happening that they will go under the same transaction waiting for the process to be done, but soon timeout. i get the error msg django.db.utils.OperationalError: (1205, ‘Lock wait timeout exceeded; try restarting transaction’)
You’ll need to post the complete view along with the complete traceback message for us to be able to best properly assist you with diagnosing this issue.
Note: When posting code or traceback messages here, surround the code (or traceback) between lines of three backtick - ` characters. This means you’ll have a line of ```, then your code, then another line of ```. This forces the forum software to keep the text properly formatted.
Please do not post images of code.