How to handle "lock wait timeout" errors in database-based sessions?

[pkrish]

Hello All,

 

I have a custom session.set_save_handler to handler sessions for my application. It seems to work fine. The database table is of type innodb. I see some errors in my log file of the nature, "(1205) Lock wait timeout exceeded; try restarting transaction".

 

I have a class which contains the session handler callbacks and have made it a singleton class.

 

session_set_save_handler(

        array($this, "db_open"),

        array($this, "db_close"),

        array($this, "db_read"),

        array($this, "db_write"),

        array($this, "db_destroy"),

        array($this, "db_gc")

        );

 

In my "db_read" method, I am doing a "SELECT FOR UPDATE" on the row with the session id. So, while performing load testing on my app. which has a lot of ajax calls, I noticed the "lock wait timeout" errors.

 

How do I resolve this problem? Any suggestions?

 

1) Should I catch these errors and issue a explicit "commit" in the read or write methods?

2) Should I specify a higher timeout value for "innodb_lock_wait_timeout"?

 

Thanks

 

[pkrish]

Hello All,

 

I have a custom session.set_save_handler to handle sessions for my application. It seems to work fine. The database table is of type innodb. I see some errors in my log file of the nature, "(1205) Lock wait timeout exceeded; try restarting transaction" with multiple threads.

 

I have a class which contains the session handler callbacks and have made it a singleton class.

 

session_set_save_handler(

        array($this, "db_open"),

        array($this, "db_close"),

        array($this, "db_read"),

        array($this, "db_write"),

        array($this, "db_destroy"),

        array($this, "db_gc")

        );

 

In my "db_read" method, I am doing a "SELECT FOR UPDATE" on the row with the session id. So, while performing load testing on my app. which has a lot of ajax calls, I noticed the "lock wait timeout" errors.

 

How do I resolve this problem? Any suggestions?

 

1) Should I catch these errors and issue a explicit "commit" in the read or write methods?

2) Should I specify a higher timeout value for "innodb_lock_wait_timeout"?

 

Thanks