Solved

How to resolve a deadlock in MySQL?

Posted on 2008-10-23
6
812 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 142

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 142

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 142

Accepted Solution

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now