Try ERPNext Try Frappe Cloud Buy Support Partners Foundation

Conditional save for transations?

Hi, I am just wondering if there is a way to issue a commit operation to the database with conditions.

I take Frappe’s library management system tutorial as example ( https://frappeframework.com/docs/user/en/tutorial/types-of-doctype ).

class LibraryTransaction(Document):
      def before_submit(self):
          if self.type == "Issue":
              self.validate_issue()
              # set the article status to be Issued
              article = frappe.get_doc("Article", self.article)
              article.status = "Issued"
              article.save()
          elif self.type == "Return":
              self.validate_return()
              # set the article status to be Available
              article = frappe.get_doc("Article", self.article)
              article.status = "Available"
              article.save()

The transaction is validated before commit. But how do I make sure that the status of the article does not change between the validation and commit? Lets say, I wish to borrow a book which is available at the time of validation. But someone has borrowed it just 0.00001 seconds before me. I know it is very rare, but theoretically it is possible isn’t it? Is it possible to simply commit with conditions and throw exception in case the conditions are not fulfilled?

Any suggestion will be appreciated :slight_smile:

Ok, I have put a time.sleep(10) after validation to deliberately create race condition. It turns out that the second submit will fail due to the doctype time being modified. It seems to be a nice mechanism to prevent the problem. But this mechanism seems to work only with Submittable doctype. For normal doctype. The the race condition is not addressed. As an example below, if I want to make sure a member not registering twice by validating his social security number, and I deliberately put 10 seconds sleep, then I can create two members with the same social security number. I know I can simply make the field of social security number being unique. But it is just for an illustration.

class LibraryMember(Document):
    def before_save(self):
        valid_id = frappe.db.exists(
            "Library Member",
            {
                "id": self.id
            },
        )
        time.sleep(10)	
        if valid_id:
            frappe.throw("The member exists")

You can manage that by transactions in the db level. Frappe automatically begins and commits (or rollbacks) transactions. You can manually lock records within transactions.

Dear Tunali, thank you for the response. Can you share a snippet of simple example. It is not that really clear to me. Lets say, I really want to make sure LibraryMember.id = “12345” does not exists before add the record.

Sorry, I’ve missunderstood your problem. Transactions are great when modifying multiple records and when you have an error at the end, you can rollback to your default status.

For your problem you need a lock mechanism. As https://mariadb.com/kb/en/for-update/ states

SELECT * FROM LibraryMember WHERE id=12345 FOR UPDATE;

will do the trick. It will lock the record for the entire transaction lifecycle but Frappe should creates and ends your transaction automatically.

Can you test and share your results?

Dear Tunali, this is not working. But I found another way (https://thispointer.com/insert-record-if-not-exists-in-mysql/)

There is yet another problem. To manipulate directly using db.sql() function I need to override the document.save() function which performs a lot of other works apart from updating the entry in database. I do not see this as an beautiful solution :thinking:

What did you try exactly? Can you share you script? You can’t override doc.save easily. It should be something like:

try:
    frappe.db.begin()
    lstLM = frappe.db.script("SELECT * FROM LibraryMember WHERE id=12345 FOR UPDATE")
    if ... 
        ....
        doc.save()
    else raise("Invalid LM ID")
except Exception as e:
    frappe.db.rollback()
else:
    frappe.db.commit()
finally:
    frappe.destroy()

Source:Database transaction

It turns out that all save() function is checked against last modified time before commit(). The problem in my example is that it creates a new entry in which case no document was modified. I solve this problem by introducing a new document called “lock” in single doctype. Then my code becomes like blew. In this case, no matter which process calls the article.save() first another process will throw exception when calling article.save(). So that the controller will not go on to create new entry for the member.

class LibraryMember(Document):
    def before_save(self):
        valid_id = frappe.db.exists(
            "Library Member",
            {
                "id": self.id
            },
        )
        article = frappe.get_doc("lock")
        time.sleep(5)
        article.save()	
        if valid_id:
            frappe.throw("The member exists")

will not work when the record with id = 12345 does not exist. I override the save function by completely copy the code of original function _save() and replace insert() with my own insert(). And inside insert() I also copy the original code except using db.sql to insert the record. The code is very massy and I think it is not maintainable in case the frappe upgrades. So I chose the trick by modifying the timestamp of “lock” document. That is so far the most nice way of addressing this problem in my opionion.