Solved

How to resolve a deadlock in MySQL?

Posted on 2008-10-23
6
818 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

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…
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

816 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

8 Experts available now in Live!

Get 1:1 Help Now