Solved

Mysql Error "Lock wait timeout exceeded; try restarting transaction"

Posted on 2010-09-15
9
1,845 Views
Last Modified: 2013-12-12
Hi

I am using Interspire application with Mysql as backend.  From long time am seeing these error in error log:


Lock wait timeout exceeded; try restarting transaction         Internal         Sep 15 2010 07:36:59
        
Query:

UPDATE email_queues SET processed='0' WHERE queueid='3896' AND queuetype='autoresponder' AND sent='0'
Location::
/var/www/html/admin/functions/api/jobs_autoresponders.php (Line 1082)


Mysql 's config files content:
cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

net_read_timeout=120

#[NDBD DEFAULT]
#TransactionDeadlockDetectionTimeout: 5000
innodb_buffer_pool_size=838860800


# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



Can you help indentifying the root cause of error and hint to fix it ?
0
Comment
Question by:ashwanijain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 3

Accepted Solution

by:
AliFarhadi earned 167 total points
ID: 33679935
Looks like something Locked table BEFORE this query and it is blocked for a long time, so the timeout exceeded. There are tho ways:

1. check the logfile and try to determine the periodic of the error - maybe this will help you to find the  Cron-script which lock the table.
2. create an offline script, which will execute "SHOW FULL PROCESSLIST" every X minutes and check if trere are some LOCKED states and longtime UPDATE queries... In this case send a full list of queries to Log ad then check it manually. There should be some query Locked table for some long task (usually updating, maybe with a WHERE case without indexes)
0
 
LVL 29

Assisted Solution

by:Michael Worsham
Michael Worsham earned 167 total points
ID: 33681462
I recommend enabling the slow query log on the MySQL server to see with an in-depth view of where the problem is starting to occur.

Enable MySQL Query Log File
http://linuxwindowsmaster.com/enable-mysql-query-log-file/
0
 
LVL 3

Expert Comment

by:AliFarhadi
ID: 33681823
sometimes queries are marked as SLOW because of waiting in queue, so the viewing of a process list will allow to determine the most first "slow" query which slow execution of all other queries.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:ashwanijain
ID: 33683391
Hi

I have enabled the slow query logging for queries taking more than 10 seconds, lets see which query is it.
0
 
LVL 8

Assisted Solution

by:wolfgang_93
wolfgang_93 earned 166 total points
ID: 33695408
I will guess that email_queues is a MyISAM table.
Locking is very primitive on MyISAM tables.
When anyone issues a query against a MyISAM table, it will put a full table lock
on the table until the query is done.

Here's a command to find out the type of table (look for "Engine"):
  show table status like 'email_queues'\G

Consider converting your table to InnoDB which does row locking like other
traditional database systems such as Oracle, SQL Server, and DB2:

  alter table email_queues engine=innodb;

0
 
LVL 8

Expert Comment

by:wolfgang_93
ID: 33931947
There were some good suggestions here to address a lock-wait timeout issue
including how to find the offending query, how to minimize locking (an probably
avoiding it happening in the first place) by converting the table in question to
InnoDB (my own suggestion).

Moving the server to a new hardware or a newer version of MySQL will not
fix the problem if none of the suggestions suggested here are followed.

Therefore I am against having the question simply deleted and a refund issued.



0
 

Expert Comment

by:_alias99
ID: 34008572
I've distributed the points among the responses provided.

With no response from the author, my reading of the question was that it was looking for troubleshooting assistance.
 
_alias99
Community Support Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

The purpose of this article is to show how we can create Linux Mint virtual machine using Oracle Virtual Box. To install Linux Mint we have to download the ISO file from its website i.e. http://www.linuxmint.com. Once you open the link you will see …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

726 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