| MYSQL Dead lock issues [message #511185] |
Fri, 10 June 2011 00:55 |
 |
Vinay_s07
Messages: 8
Registered: March 2011
Location: Bangalore
|
Junior Member |
|
|
Hi All,
we are facing some problems with MYSQL simultaneous transactions.
we are using MYSQL 5.0 with NDBcluster engine in our project.
Problem is with the simultaneous call of multiple procedures from the JAVA application to fetch the required data from the same table from the database.
All the procedures being called have the two statements at the end of the procedures.
Select * from report_dump.. and delete from report_dump table.
Table_Name is Report_DUMP and is having the below columns...
1. Report_Id varchar(50) default NULL
2. Report_data Blob
3. seqn_num int (10) not null auto_increment
seqn_num is also primay key and the value will generated by the sequence.
The actual problem is, select statement with filter (where clause)condition from PROC1 acquires a shared (S) lock on the report_dump table.
Now there is a delete statement with filter (where clause) condition which would try and execute on the same table from some other procedure (PROC 2) on different set of rows.
This can't acquire the lock, because delete statement requires an exclusive (X) lock and the select statement has already acquired a shared (S) lock and the exclusive lock is incompatible with the shared lock that has already been taken.
Hence the request goes on the queue of lock requests and locks PROC2.
Now the PROC1 tries to execute the delete statement by acquiring the exculsive (X) lock but PROC2 is already in queue, hence it goes in a deadlock and fails.
Please note that....both the procedures are trying to alter different set of rows from the same table.
As per my knowledge Inndob and NDBcluster engines will acquire row level locking rather than the table level locking as compared to the other MYSQL engines.
Following is the confirmation that the deadlock while parallel report generation is because of the delete queries. This is a snapshot of the logs generated during the deadlock.
Tue Jun 07 2011 17:57:17
*** (1) TRANSACTION:
TRANSACTION 0 520240, ACTIVE 42 sec, process no 27036, OS thread id 2428300176 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 2727
MySQL thread id 34, query id 280977 10.5.138.139 ececuser updating
DELETE FROM REPORT_DUMP WHERE NAME_CONST('REPORT_ID',_latin1'595292' COLLATE 'latin1_swedish_ci') = NAME_CONST('report_id',_latin1'595292' COLLATE 'latin1_swedish_ci')
*** (2) TRANSACTION:
TRANSACTION 0 520237, ACTIVE 42 sec, process no 27036, OS thread id 2435234704 fetching rows, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1024, 4 row lock(s), undo log entries 6534
MySQL thread id 31, query id 230888 10.5.138.139 ececuser updating
DELETE FROM report_dump WHERE NAME_CONST('REPORT_ID',_latin1'595285' COLLATE 'latin1_swedish_ci')= NAME_CONST('report_id',_latin1'595285' COLLATE 'latin1_swedish_ci')
Please help me in resolving the above mentioned Dead lock issues.
Please let me know for any further details and information.
Any kind of help on this would be much appreciated.
Thanks and Regards.
Vinay B S
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
