Solved

Deadlock in MySQL

Posted on 2004-09-10
6
491 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
[X]
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
  • 2
6 Comments
 
LVL 26

Expert Comment

by:ushastry
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:ushastry
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

749 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