Home » Open Source » MySQL » Locking in Mysql procedure (Mysql 5.0)
| Locking in Mysql procedure [message #595013] |
Thu, 05 September 2013 01:03  |
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  |
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 #595895 is a reply to message #595178] |
Mon, 16 September 2013 00:45 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Dec 21 20:02:17 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
