Deadlock found when trying to get lock; try restarting transaction

[vince]

we are testing web2py with galera cluster and notice lots of Deadlock found when trying to get lock; try restarting transaction ticket.

those ticket don't come with request/session info, we have no idea where and when did this problem occur. does that mean that web2py already handle the retry?

[Massimo Di Pierro]

We need more information. What is galera cluster? What you trying to lock?

[vince]

i am using mariadb galera cluster and we did not implement any locking. been testing this cluster for long and it will return locking error during node lag.

heres the error i got.

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.

Traceback (most recent call last): File "/home/web2py/gluon/main.py", line 599, in wsgibase BaseAdapter.close_all_instances('commit') File "/home/web2py/gluon/dal.py", line 560, in close_all_instances db._adapter.close(action) File "/home/web2py/gluon/dal.py", line 540, in close getattr(self, action)() File "/home/web2py/gluon/dal.py", line 1740, in commit if self.connection: return self.connection.commit() File "/home/web2py/gluon/contrib/pymysql/connections.py", line 631, in commit self.errorhandler(None, exc, value) File "/home/web2py/gluon/contrib/pymysql/connections.py", line 202, in defaulterrorhandler raise errorclass, errorvalue InternalError: (1213, u'Deadlock found when trying to get lock; try restarting transaction')

In file: Framework

[Niphlod]

ehm, sorry. are you blaming web2py or the galera cluster ? web2py doesn't do anything special unless sending SQL queries down the pipe. If your galera cluster goes into deadlocks executing queries, fix the cluster ^_^

[Alan Etkin]

those ticket don't come with request/session info, we have no idea where and when did this problem occur. does that mean that web2py already handle the retry?

 
AFAIK, web2py does not retry db I/O commands. The tickets are created when the request generates an uncaught exception (in your case, a driver error). One way of preventing this would be writing your own driver error (and retry) handler in your application logic.

I guess something like this

def action():
    from adriver import DriverException
    try:
        rows = ...
    except DriverException:
        raise HTTP(500, "Seems like the cluster is busy now. Try in a few moments")
    return dict(...)

[vince]

actually yes i just need a way to handle this error myself. see the above for the error message i attached, the sample from you didn't work.

[Alan Etkin]

the sample from you didn't work.

Can you post the new error output?

[vince]

i do not know which function actually causing this problem. the application we testing are mostly select query so most likely it's from the web2py session update query.

in pymysql/connection.py 

    def commit(self):

        ''' Commit changes to stable storage '''

        try:

            self._execute_command(COM_QUERY, "COMMIT")

            self.read_packet()

        except:

            exc,value,tb = sys.exc_info()

            self.errorhandler(None, exc, value)

it's belong to commit function and we didn't do any db.commit call. a ticket without request and session info can i assume it's web2py session update?

[Alan Etkin]

i do not know which function actually causing this problem. the application we testing are mostly select query so most likely it's from the web2py session update query.

I think this is a workflow issue. Perhaps your db error occurs after executing the action logic. In that case, this pseudocode might help:

At a web2py controller:

try some DAL db i/o command
force a commit (with db.commit())
if there's a driver exception wait for a few seconds and retry
if the second try fails, return a message
else return the query output

BTW: How did you implement my previous pseudocode? What was the error output?

[vince]

i think the framework errors are from web2py session update into mysql, we've already use session.forget to minimize the problem but still we need to update session sometimes.

we have already catching all update request and do retry ourself. since mariadb galera cluster is synchronous replication i guess it's no way to avoid deadlock during update.

is there any solution to do custom update retry on web2py session? it would be ideal if dal have an option to auto retry update query.

[vince]

actually it is possible to disable transaction?  i believe the cluster will handle auto-recommit except for transactional commit.