Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to resolve a deadlock in MySQL?

Posted on 2008-10-23
6
Medium Priority
?
853 Views
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?
0
Comment
Question by:technoparkcorp
  • 3
  • 3
6 Comments
 
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
0
 

Author Comment

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

Author Comment

by:technoparkcorp
ID: 22783880
in other words, how can I kill ALL transactions in MySQL?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22783915
the simplest would be the restart MySQL.
otherwise, to see all sessions:
http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html

to kill a session:
http://dev.mysql.com/doc/refman/5.0/en/kill.html
0
 

Author Comment

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

Accepted Solution

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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

580 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