MySQL SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

Question

I have database session enable, when website got thousands of visitor. It gives me error

[30-Apr-2014 04:27:12 America/Denver] PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded;
 try restarting transaction' in public_html/lib/Cake/Model/Datasource/DboSource.php:458
Stack trace:
#0 public_html/lib/Cake/Model/Datasource/DboSource.php(458): PDOStatement->execute(Array)
#1 public_html/lib/Cake/Model/Datasource/DboSource.php(424): DboSource->_execute('UPDATE `yuldi_d...', Array)
#2 public_html/lib/Cake/Model/Datasource/Database/Mysql.php(400): DboSource->execute('UPDATE `yuldi_d...')
#3 public_html/lib/Cake/Model/Model.php(1821): Mysql->update(Object(cake_sessions), Array, Array)
#4 public_html/lib/Cake/Model/Datasource/Session/DatabaseSession.php(117): Model->save(Array)
#5 [internal function]: DatabaseSession->write('66336365f5fc91c...', 'Config|a:3:{s:9...')
#6 [internal function]: session_write_close()
#7 {main}
 thrown in public_html/lib/Cake/Model/Datasource/DboSource.php on line 458

I want to know, how can i check MySQL insert queries per second, is there any commands to check that using ssh ? is there changes in my.cnf required ?

Answer 1

If you wan't to check the queries/s in live you can use the mysqladmin tool with some options:

[root@db-prod-01 ~]# mysqladmin extended-status -uroot -p -i 1 -r | grep 'Com_insert '
Enter password: 
| Com_insert | 59863746 |
| Com_insert | 1 |
| Com_insert | 2 |
| Com_insert | 9 |
| Com_insert | 2 |
| Com_insert | 2 |
| Com_insert | 3 |
| Com_insert | 2 |
| Com_insert | 3 |
| Com_insert | 6 |

• extended-status: return the result of SHOW GLOBAL STATUS;
• -i 1 -r: Execute the SHOW GLOBAL STATUS; every second (one line by second)
• | grep 'Com_insert': Shows only INSERTS statements

Note that Com_insert is a cumulativ counter the first line shows the nominal counter value and at each iteration of mysqladmin, a substraction is made to show you how there were insertions during the time you setted (here -i 1 is each 1 second).

By the way, if you want some infos for your error (1205) it's the InnoDB Engine that should check because it's the only way to see which of your query (or queries) is/are stucking your DB.

You can find infos on current and past transactions that lock your DB with:

SHOW ENGINE INNODB STATUS; -- In a MySQL prompt

Max.

• Surjit Sidhu: how can i check that my system current resources capable to how much workload of insert query. will also help if there any great tool to test high workload on server

Answer 2

Laravel Database Session also caused similar issue. I think you can refer to it based on situation below:-

The Garbage Collection function of the Laravel Database Session to cause sessions table locked when performing deletion of the expired session.

There are two ways to reduce this issue:

First, Ignore Garbage Collection function by manually amend the DatabaseSessionHandler.php located at \vendor\laravel\framework\src\Illuminate\Session to comment gc function, but it will be caused the session will never expired until perform logout.

For example:

 public function gc($lifetime)
 {
 //$this->getQuery()->where('last_activity', '<=', Carbon::now()->getTimestamp() - $lifetime)->delete();
 }

Second, add Index of the column last_activity to table 'sessions' for reduce the range of lock from entire table into row-level. Be careful while performing this action, it will cause down time while perform add index depends of the row size of you sessions table

Note: Take your own risk while perform both actions, better test of localhost environment then update to the production side.