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