[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Lock wait timeout exceeded; try restarting transaction+MySQL

Posted on 2014-08-26
7
Medium Priority
?
1,580 Views
Last Modified: 2014-09-24
I am getting below error in one of MySQL Database. Please review the below mentioned existing MySQL DB configurations and advise which settings I have to modify to resolve below error.

Error: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

Existing configurations:
1. select @@lock_wait_timeout;
Output: 31536000

2. show variables like '%tx_isolation%';
Output: REPEATABLE-READ

3. show variables like '%wait_timeout%';
Output:
Variable_Name                               value
------------------------------------------------
innodb_lock_wait_timeout              50
lock_wait_timeout                           31536000
wait_timeout                                   28800
0
Comment
Question by:sqldba2013
  • 4
  • 3
7 Comments
 
LVL 36

Accepted Solution

by:
Loganathan Natarajan earned 1500 total points
ID: 40285152
Try to increase this value innodb_lock_wait_timeout   =     to 300 or 500
0
 

Author Comment

by:sqldba2013
ID: 40285170
I have used below command to set innodb_lock_wait_timeout to 500.

set innodb_lock_wait_timeout=500;

when I reconnected to MSQL it was showing old value 50.

Do I need to restart services or above command is wrong?

I have executed above command in workbench tool.
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 40285190
you have to restart the service only then the settings value will be set
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:sqldba2013
ID: 40285252
I have restarted services and still its showing old value 50.Untitled.jpg
0
 

Author Comment

by:sqldba2013
ID: 40311363
Please advise how to increase Lock wait timeout value in NDB cluster.
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 40311389
are you sure in your mysql config file  settings changed? I still doubt that you've not changed that.   just open the file again and check whether settings changed or not
0
 

Author Closing Comment

by:sqldba2013
ID: 40341698
Thanks...I have resolved issue myself by changing deadlocktimeout value.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question