Home » Open Source » MySQL » Locking in Mysql procedure (Mysql 5.0)
Locking in Mysql procedure [message #595013] Thu, 05 September 2013 01:03 Go to next message
lavk
Messages: 12
Registered: February 2010
Junior Member
Hi All,

we are facing some problems with MYSQL Procedure.
There are 6 reports in our project and we have written some procedure to manipulate the data and put that data into one table.

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

Here 3 reports are working fine but rest of the report showing

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

One more thing I want to tell that There are 4 instences of mysql in one server and configration are same in all instences. all 6 reports are working fine in 3 instences but in one instence Loking issue is comming.

Please help me in resolving the above mentioned locking issues.
Please let me know for any further details and information.
Any kind of help on this would be much appreciated.

Thanks and Regards.
Lavkush


Re: Locking in Mysql procedure [message #595062 is a reply to message #595013] Thu, 05 September 2013 15:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I don't much work in My Sql, however, few things I am sure about in your case.

Locking issues arise due to a lot of things like -
1. Unindexed foreign keys.
2. Uncommitted transaction
3. Improper design, which implicitly includes point 1.
4. Transaction handling is vaguely handed over to frontend application.
5. Many other things.....you need to provide a better direction to move forward.
Re: Locking in Mysql procedure [message #595178 is a reply to message #595062] Mon, 09 September 2013 00:29 Go to previous messageGo to next message
lavk
Messages: 12
Registered: February 2010
Junior Member
Hi Lalit,

Thanks for your response .
Please find the procedure in attechment.
  • Attachment: MYProc.sql
    (Size: 1.78KB, Downloaded 3296 times)
Re: Locking in Mysql procedure [message #595895 is a reply to message #595178] Mon, 16 September 2013 00:45 Go to previous messageGo to next message
lavk
Messages: 12
Registered: February 2010
Junior Member
Hi,

This is a procedure which is showing an error
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction




DELIMITER $$


DROP PROCEDURE IF EXISTS `My_Proc`$$

CREATE DEFINER=`test`@`%` PROCEDURE `My_Proc`(
IN reportId VARCHAR(20),
IN SL VARCHAR(20),
IN endDate DATETIME,
IN bnk1 VARCHAR(20)
)
MODIFIES SQL DATA
BEGIN
-- Declare local variables
DECLARE V_SL,V_ACT_BAL,V_END_DATE VARCHAR(50) DEFAULT '0';
DECLARE V_AC2,V_CID,V_BRN VARCHAR(150) DEFAULT ' ';
DECLARE done INT DEFAULT FALSE;
DECLARE Sno INTEGER;
DECLARE seqn_num INT DEFAULT 0;
-- cursor declaration
DECLARE bal_cursor CURSOR FOR
SELECT cr.CID1,cr.AC2,cr.BLNC
FROM XX cr,YY cu
WHERE cr.CID1 =cu.CID1
AND cr.R_CRE_TIME <=CONCAT(SUBSTRING(endDate,1,11),'59:59:59.099')
AND cr.bnk1 = bnk1
AND cr.BRN=SL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


SELECT r.BRN INTO V_BRN
FROM ZZ r
WHERE r.SL_ID=SL;

SET V_END_DATE = SUBSTRING(endDate,1,11);

SET V_SL=SL;
INSERT INTO REPORT_DUMP(report_id,report_data,seqn_num,R_CRE_ID,R_CRE_TIME) VALUES (reportId ,CONCAT('Sr.No|SL_ID|AVL_BAL|ACID|END_DATE|CID1|BRN_NAME'),seqn_num,'SYSTEM',NOW());
SET Sno=0;
OPEN bal_cursor;
read_loop_1: LOOP
FETCH bal_cursor INTO V_CID1,V_ACID,V_ACT_BAL;
IF done THEN
LEAVE read_loop_1;
END IF;
SET seqn_num=seqn_num+1;
SET Sno=Sno+1;

INSERT INTO REPORT_DUMP(report_id,report_data,seqn_num,R_CRE_ID,R_CRE_TIME) VALUES
( reportId,CONCAT(Sno,'|',IFNULL(V_SL_ID,'-'),'|',IFNULL(V_ACT_BAL,'- '),'|',IFNULL(V_ACID,'-'),'|',IFNULL(V_END_DATE,'-'),'|',IFNULL(V_CID1,'-'),'|',IFNULL(V_BRN,'- ')),seqn_num,'SYSTEM',NOW());

END LOOP;
CLOSE bal_cursor;
SELECT R.report_id,R.report_data FROM REPORT_DUMP R WHERE R.REPORT_ID = reportId ORDER BY R.seqn_num;
DELETE FROM report_dump WHERE report_id = reportId;
SET seqn_num=0;
END$$

DELIMITER ;



Thanks in advance
Lavkush
Re: Locking in Mysql procedure [message #595900 is a reply to message #595895] Mon, 16 September 2013 01:21 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel
Previous Topic: UNIQUE KEY
Next Topic: Any MS Access to MySQL database conversion tool
Goto Forum:
  


Current Time: Thu Mar 28 15:52:06 CDT 2024