Hi,
I'm having a strange problem with table locking. I have written a stored
procedure that only accesses a single table. Before executing the
procedure, I'm trying to lock the table in question for writing. The
LOCK TABLE command succeeds, but when I execute the stored procedure it
tells me a non existent table was not locked:
mysql> LOCK TABLE PlanTracking WRITE; CALL spConfirmRequest(7); UNLOCK
TABLE;
Query OK, 0 rows affected (0.00 sec)
ERROR 1100 (HY000): Table 'pt' was not locked with LOCK TABLES
Query OK, 0 rows affected (0.00 sec)
mysql>
The table being referred to was once an alias for a table in a select
query being used for a cursor in my stored procedure. The alias has been
removed, the procedure has been dropped and re-created, and the mysql
service has been restarted yet the problem persists.
Thanks,
J.P.
Here's the stored procedure:
mysql> show create procedure spConfirmRequest;
+------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure
|
+------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| spConfirmRequest | | CREATE definer=`ro...@`localhost`
PROCEDURE `spConfirmRequest`( ptid bigint )
BEGIN
DECLARE vPlanTrackingID, vProjectID BIGINT;
DECLARE vDownload TINYINT;
DECLARE cursItems CURSOR FOR (
SELECT PlanTrackingID, ProjectID, Download
FROM PlanTracking
WHERE SetNumber = 0 AND Deleted = 0 AND
(PlanTrackingID = ptid OR ParentID = ptid)
ORDER BY PlanTrackingID );
OPEN cursItems;
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
LOOP
FETCH cursItems INTO vPlanTrackingID,
vProjectID, vDownload;
UPDATE PlanTracking
SET
SetNumber = getNextSetNo(vProjectID,
vDownload),
ModDate = NOW()
WHERE PlanTrackingID = vPlanTrackingID;
END LOOP;
END;
CLOSE cursItems;
SELECT PlanTrackingID, ParentID, SetNumber
FROM PlanTracking
WHERE Deleted = 0 AND SetNumber > 0 AND (PlanTrackingID = ptid
OR ParentID = ptid)
ORDER BY PlanTrackingID;
END |
+------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> LOCK TABLE PlanTracking WRITE; CALL spConfirmRequest(7); UNLOCK
TABLE;
Query OK, 0 rows affected (0.00 sec)
ERROR 1100 (HY000): Table 'pt' was not locked with LOCK TABLES
Query OK, 0 rows affected (0.00 sec)
mysql>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
