Solved

Deadlock in MySQL

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 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