?
Solved

Deadlock in MySQL

Posted on 2004-09-10
6
Medium Priority
?
519 Views
Last Modified: 2012-06-27
This is the SQL process list captured :
Host     DB     Command     Time     State                   Info
A         Max   Query           626       Writing to net       Select * from Table1
B         Max   Query           620       Locked                 Delete from Table1 where T1 = 'Yoyo'
B         Max   Query           617       Locked                 Insert into Table1 (T1,T2,T3) values (1,2,3)
C         Max   Query           192       Writing to net       Select * from Table2
A         Max   Query           47         Locked                Select * from Table1 order by no

Our java application hang when it try to access database Max. We tried to manual kill the select statement process, althrough there is a red color cross sign next to the process list but the process cannot be remove. We manage to kill all the locked state processes. We tried to flush the tables and flush the database and the database hang.

The database is being installed in Windows 2003 environment.

Questions :
1. Is this considered as deadlock in MySQL?
2. Why the insert statement manage to lock the table where there's a delete statement lock the table before the insert statement comes in?
3. What can we configure or do in order to resolve this problem in the future?

Thanks in advance.

Rgds,
Ooiling
0
Comment
Question by:ctlim
  • 2
4 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 12027511
0
 

Author Comment

by:ctlim
ID: 12061664
Hi Ushastry,

Thanks a lot on the references given. The articles stated that deadlock problem will always happen which INNODB type but we are using MYiSAM DB type here. And does the pattern shown consider as a deadlock problem?

0
 
LVL 26

Expert Comment

by:Umesh
ID: 12062714
Hi,

I don't think so.

Except for InnoDB and BDB storage engines, All locking in MySQL is deadlock-free for storage engines that use table-level locking. This include the MyISAM, MEMORY (HEAP), and ISAM engines. Deadlock avoidance is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order.

Take a look at this..
http://help.scibit.com/mysql/manual_MySQL_Optimisation.html
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 13772649
Submitted to PAQ with no points refunded (of 500)

DarthMod
Community Support Moderator
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month12 days, 19 hours left to enroll

578 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