Creating Tables Inside Store Procedures

[alastair knowles]

I have written a stored procedure to search an adjacency list, which dumps the search results into an in memory cache table created by the procedure. I am not using a temporary table, as I need to join the cache table to itself as part of the search process. Does anyone know whether a table naming clash could occur if two threads call the same procedure concurrently? I know that statements inside a procedure are atomic - not sure what happens when I create a new table though.

Code below:

DELIMITER $$

DROP PROCEDURE IF EXISTS `server`.`us_re_unlink`$$

CREATE DEFINER=`system`@`%` PROCEDURE `us_re_unlink`(
IN ur_id INT (10) ZEROFILL
/*Seed record identifier*/)
BEGIN
DECLARE loops INT (4) DEFAULT 0;
DECLARE depth INT (4) DEFAULT 1000;
/*Loop counter and exit values*/
DECLARE stamp DATETIME DEFAULT NOW();
/*Date and time of the revision*/
CREATE TABLE tree (
ur_id INT (10) ZEROFILL PRIMARY KEY,
batch INT (4), INDEX(batch))
ENGINE = MEMORY
SELECT ur_id AS ur_id, loops AS batch;
/*Cache the trash files in a table*/
recurse: LOOP
SET loops = loops + 1;
/*Increment the counter*/
INSERT INTO tree (tree.ur_id, tree.batch)
SELECT users_records.ur_id, loops
FROM tree INNER JOIN users_records
ON tree.ur_id = users_records.pa_id
WHERE tree.batch = (loops - 1)
AND users_records.tr_id = 0;
/*Write records into cache*/
IF ROW_COUNT() = 0 OR loops = depth THEN
LEAVE recurse;
/*Finished*/
END IF;
ITERATE recurse;
/*Keep looping*/
END LOOP recurse;
UPDATE users_records INNER JOIN tree
ON users_records.ur_id = tree.ur_id
SET users_records.tr_id = users_records.ur_id,
users_records.revise = stamp;
/*Move the records to the trash*/
DROP TABLE tree;
/*Empty cache*/
SELECT stamp;
END$$

DELIMITER ;

BTW, the code itself is very fast. Might be useful for anyone else trying to solve the same problem - representing a user file system in a database. Guidance/advice appreciated.