Is MySQL Temporary table a shared resource?

mysql concurrency temp-tables
Ehsan Khodarahmi picture Ehsan Khodarahmi · Jul 4, 2011 · Viewed 14.6k times · Source

I have a MySQL stored procedure that uses a temporary table. Assume that my table name is 'temp' and I use it to store some middle data. It will create at the beginning of procedure, and will drop at the end.

CREATE PROCEDURE p()
BEGIN

CREATE TEMPORARY TABLE \`temp\`(...);

INSERT INTO \`temp\` VALUES(...);

DROP TEMPORARY TABLE \`temp\`;

END;

The problem is that this stored procedure may be used by different users concurrently, so I want to know if this can cause any problems (i.e. any conflict in inserted data in temp table). In other word is temp table a shared resource within different calls to the same SP?

Answer

Bill Karwin picture Bill Karwin · Jul 4, 2011

No, a temp table is limited to the scope of your database connection. You can use the temp table in subsequent calls to the procedure during the same database connection, but other connections cannot access it. They can create a table by the same name, but each temp table will be independent. Temp tables go away when you close your connection.

Related questions

MySQL INSERT IF (custom if statements)

First, here's the concise summary of the question: Is it possible to run an INSERT statement conditionally? Something akin to this: IF(expression) INSERT... Now, I know I can do this with a stored procedure. My question is: can I …

Read More
How bad is using SELECT MAX(id) in MYSQL instead of mysql_insert_id() in PHP?

Background: I'm working on a system where the developers seem to be using a function which executes a MYSQL query like "SELECT MAX(id) AS id FROM TABLE" whenever they need to get the id of the LAST inserted row (…

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More