| Edgard L. Riba 2005-07-27, 9:23 am |
| Hi,
I'm doing a keypool thing to generate sys id's for a given set of tables.
The table scripts are:
//--------------------------------------------------------------------------------------------------------
// SysID Pool
//--------------------------------------------------------------------------------------------------------
CREATE TABLE dbKeyPool (
TableName VARCHAR(40) NOT NULL,
sysID INTEGER NOT NULL,
idLoc UNSIGNED TINYINT,
PRIMARY KEY (TableName,sysID)
);
CREATE INDEX dbKP_LLAVE1 ON dbKeyPool (TableName ASC, idLoc ASC,
sysID ASC);
//--------------------------------------------------------------------------------------------------------
// Tables per location...
//--------------------------------------------------------------------------------------------------------
CREATE TABLE dbKeyPoolLocXTable (
TableName VARCHAR(40) NOT NULL,
idLoc UNSIGNED TINYINT NOT NULL,
PRIMARY KEY (TableName,idLoc)
);
//--------------------------------------------------------------------------------------------------------
//- DESCRIPTION: Tables to use in the KeyPool sub-system
//--------------------------------------------------------------------------------------------------------
CREATE TABLE dbKeyPoolTables (
tableName VARCHAR(40) NOT NULL,
PartitionCount INTEGER DEFAULT 5000,
LastSysID INTEGER,
PRIMARY KEY (tableName)
);
Here is the stored procedure I'm using to generate the keypool:
CREATE PROCEDURE ReplenishKeyPool()
BEGIN
DECLARE maxSysId INT;
//For each valid table
FOR EachTable AS TableCursor
CURSOR FOR
SELECT tableName AS CurrTable, partitionCount AS CurrTablePartition,
lastSysID AS CurrTableLastSysID
FROM dbKeyPoolTables
DO
//...for each location that uses the given table
FOR EachLocation AS LocationCursor
CURSOR FOR
SELECT dbKeyPoolLocXTable.idLoc AS CurrLocation, count(*) as
sysIDCount
FROM dbKeyPoolLocXTable
JOIN dbKeyPool ON
dbKeyPoolLocXTable.TableName=dbKeyPool.TableName AND
dbKeyPoolLocXTable.idLoc=dbKeyPool.idLoc
WHERE dbKeyPoolLocXTable.tableName = CurrTable
GROUP BY dbKeyPoolLocXTable.idLoc
DO
//....generate unique sysID's to fill the "quota"!
WHILE sysIDCount < currTablePartition LOOP
SET CurrTableLastSysID = CurrTableLastSysID + 1; //increase
by one the number of TOTAL sysID assigned,
SET sysIDCount = sysIDCount + 1; //and increase by one the
number of sysID assigned to the current location
INSERT INTO dbKeyPool
(tableName,idLoc,sys
ID)
VALUES
(CurrTable, CurrLocation, CurrTableLastSysID);
END LOOP;
END FOR;
UPDATE dbKeyPoolTables SET lastSysID=CurrTableL
astSysID WHERE
tableName=CurrTable;
END FOR;
END; //This procedure
This works, BUT after just a couple of minutes of testing, the procedure
crashed after generating some sysID's, but before updating the control of
"LastSysID". The end result is that after that crash the keypool
replenishment didn't work anymore.
The problem obviously is that the stored procedure must be converted into a
transaction so that it runs completely, or not at all.
I have looked in the help, but I can't find a good example of how to convert
this procedure to a completely atomic transaction. Here is my first rough
attempt, but I'm not sure I'm even on the right track:
CREATE PROCEDURE ReplenishKeyPool()
BEGIN TRANSACTION
DECLARE ErrorCode INT;
DECLARE maxSysId INT;
SET ErrorCode = @Error;
//For each valid table
FOR EachTable AS TableCursor
CURSOR FOR
SELECT tableName AS CurrTable, partitionCount AS CurrTablePartition,
lastSysID AS CurrTableLastSysID
FROM dbKeyPoolTables
DO
IF ErrorCode = 0 THEN
//...for each location that uses the given table
FOR EachLocation AS LocationCursor
CURSOR FOR
SELECT dbKeyPoolLocXTable.idLoc AS CurrLocation, count(*) as
sysIDCount
FROM dbKeyPoolLocXTable
JOIN dbKeyPool ON
dbKeyPoolLocXTable.TableName=dbKeyPool.TableName AND
dbKeyPoolLocXTable.idLoc=dbKeyPool.idLoc
WHERE dbKeyPoolLocXTable.tableName = CurrTable
GROUP BY dbKeyPoolLocXTable.idLoc
DO
SET ErrorCode = @@Error;
IF ErrorCode = 0 THEN
//....generate unique sysID's to fill the "quota"!
WHILE sysIDCount < currTablePartition LOOP
SET CurrTableLastSysID = CurrTableLastSysID + 1;
//increase by one the number of TOTAL sysID assigned,
SET sysIDCount = sysIDCount + 1; //and increase by
one the number of sysID assigned to the current location
INSERT INTO dbKeyPool
(tableName,idLoc,sys
ID)
VALUES
(CurrTable, CurrLocation, CurrTableLastSysID);
END LOOP;
END IF;
END FOR;
SET ErrorCode = @@Error;
IF ErrorCode = 0 THEN
UPDATE dbKeyPoolTables SET lastSysID=CurrTableL
astSysID WHERE
tableName=CurrTable;
END IF;
END IF;
END FOR;
IF ErrorCode = 0 THEN
SET ErrorCode = @@Error;
IF ErrorCode = 0 THEN
COMMIT;
ELSE
ROLLBACK;
RAISERROR ErrorCode 'Keypool could not be replenished...';
END IF;
ELSE
ROLLBACK;
RAISERROR ErrorCode 'Keypool could not be replenished...';
END IF;
END; //This procedure
Any comments/examples/ideas are more than welcome!
Best regards,
Edgard
|