How to resolve a deadlock in MySQL?

Posted on 2008-10-23
Last Modified: 2012-05-05
When I start new transaction in MySQL sometimes I get an error:

(1205) Lock wait timeout exceeded; try restarting transaction

How can I resolve this deadlock in such case? In other words, I want to KILL the transaction which created this deadlock. Can I do this?
Question by:technoparkcorp
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
  • 3
  • 3
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22783818
that error is NOT a deadlock, but a simple lock.

now, to find the session, I found nothing but some forum entry saying:
Run mysqladmin debug
Then, the list of locks will be listed in the error log file.

hope this helps

Author Comment

ID: 22783863
I want to kill a transaction right from my PHP script. Not manually from console

Author Comment

ID: 22783880
in other words, how can I kill ALL transactions in MySQL?
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22783915
the simplest would be the restart MySQL.
otherwise, to see all sessions:

to kill a session:

Author Comment

ID: 22786308
any chance to kill a transaction?
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 22786419
yes. see the last link above for the syntax.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
running code or pseudo code of table structure 5 41
Excel - SQL export question 3 58
mysql db 3 95
converting integer data type to time data type in sql 4 60
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

732 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