From: J.P. Trosclair (jptrosclairjudelawfirm.com)
Date: Tue May 12 2009 - 15:43:21 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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=`root``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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]