I have a background job that stores a few values in a DocType, then runs some audio processing task that could take up to 12 hours or more, and then sets a few more values and terminates. For shorter jobs this works ok, but if audio processing takes long enough, I get the error (2006, "MySQL server has gone away (ConnectionResetError(104, 'Connection reset by peer'))"), which I believe means the database gave up waiting. Even without this error though, I don’t think its good to hold this transaction open for so long as it can interfere with other db operations.
From what I’ve read in the documentation, frappe automatically starts a transaction for each background job and commits it at the end. Is there any way to avoid this? Ideally, I’d like to commit the changes at the beginning, closing any transaction. Then do the audio processing, and then open another transaction at the end to save a few more values. There is no need for the transaction to be open during the audio processing.
Thanks, I think this would fix the “connection reset by peer” error, but I would still like to avoid having a transaction open for so long. I’ve noticed that, for example, a Delivery Note cannot be deleted while one of these audio jobs is running. I get the error “Server is busy”, which usually means the database connection timed out, likely because it is waiting on some lock being held by the long running audio transaction.
But your code snippet pointed me to the database.py file. From there I can see that calling the commit() function commits and re-opens a Tx, but uses self.sql("commit") internally. So I’m wondering if the following would work:
<initial db operations>
frappe.db.commit() # normal commit so all hooks run
frappe.db.sql("commit") #manually close Tx opened by db.commit()
<long running code>
frappe.db.begin()
<save a few db things>
frappe.db.commit() # or rollback in exception clause
Would this miss any important hooks or other internal operations that Frappe needs?