I have the following function that works as intended.

Can I impose a Table Lock during execution with auto release - if locked return -1

CREATE FUNCTION "NextPO" ()
RETURNS INTEGER
BEGIN
 DECLARE NewPO INTEGER;
 START TRANSACTION ON TABLES 'PoNumber';
 BEGIN
   EXECUTE IMMEDIATE 'SELECT Number INTO ? FROM PoNumber' USING NewPO;
   SET NewPO = NewPO + 1;
   EXECUTE IMMEDIATE 'UPDATE PoNumber SET Number = ?' USING NewPO;
   COMMIT;
 EXCEPTION
   SET NewPO = 0;
   ROLLBACK;
 END;
 RETURN NewPO; END
VERSION 1.00

Still wanting Table Lock/UnLock in stored procedure - please advise - thanks

Andrew


Not a full answer by any means but have a look at this section in the manual "7.24 START TRANSACTION!" there is a parameter TIMEOUT which will abort the transaction if the tables can't be locked

Roy Lambert

OK so then START TRANSACTION LOCKS

Andrew


I appologise for not realising you didn't know that. The whole point of a transaction is to make multiple changes to a database essentially atomic. Whilst the transaction is in process the tables in use (or the whole database) are locked, changes are made in memory and only when commit is called do they get written to disk.

Roy Lambert