Does Frappe rollback explicitly the transactions on Lock timeout?

ERPNext
1

Team,

In the maria/myslq Db configuration, we find that innodb_rollback_on_timeout is disabled by default. It implies innodb rollbacks only the last statement on a transaction timeout and not the entire transaction.

Will Frappe rollbacks the entire transaction on lock timeout?

We are seeing lots of lock timeout happening and ERP Server becoming very slow.

If we enable the innodb_rollback_on_timeout, will it further degrade the DB Performance?

Thanks,
Saravana

2

https://frappeframework.com/docs/user/en/api/database#database-transaction-model

3

What happens if the property is kept as it is.

  • We fire lot of API calls to enter data with naming series.

  • Sometimes API got timeout response but the transaction got completed. That was unexpected behaviour as we were expecting entry to fail on timeout!

  • User have a way to confirm and not re-enter the document. API considers it as a request failure if there is a timeout and may retry resulting into duplicate entries.

  • This causes API calling app to register an inconsistent state.

Solution:

  • API calling app can have additional handling logic that can ensure success of previous timed-out entries. It will result in more complexities in code.

  • Or just set the MariaDB property to true so timeout always results into rollback and no need to add complex logic to ensure success even after timeout.

This timeout property has nothing to do with frappe framework and the request failure/rollback handled in framework. This is part of mariadb configuration and changes the behaviour of MariaDB.

Set it to true, worst performance degrade will be slowness for timeout duration when there is a timeout causing transaction. I think because default timeout duration is 10 minutes (not sure).

If you don’t set it to true the duration for degrade is unpredictable as there is a timeout for mysql clients and server keeps processing the transaction.

Related: [Error]'Lock wait timeout exceeded; try restarting transaction - #3 by revant_one

4

Thanks @revant_one for the clarification.

We see that in mariadb innodb_autoinc_lock_mode parameter is set to Consecutive Lock Mode . In this mode for bulk insert/update table level lock will occur.

Is there any possibility of frappe doing bulk insert/update?

We are suspecting(not sure) that table level lock might be happening resulting in Lock Timeout.

Can we change the innodb_autoinc_lock_mode to Interleaved Lock Mode ?

Will it improve the db performance? Will it have any implications?

Thanks,
Saravana

5

Any updates have happened further to this discussions?