Solved

Deadlock in MySQL

Posted on 2004-09-10
6
487 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

840 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