Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Deadlock in MySQL

Posted on 2004-09-10
6
Medium Priority
?
516 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: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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

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.
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…
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

604 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