35

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?

Ehsan Khodarahmi
  • 4,390
  • 9
  • 57
  • 81

2 Answers2

57

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.

Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
  • i'm getting mixed results inside my temp tables from different stored procedure calls done by my webserver. Can you imagine why is this happening? is may apache using the same mysql connection for different http requests? thanks a lot – Packet Tracer Jan 17 '13 at 16:00
  • @FeidaKila, I cannot guess what you mean by "mixed results". Perhaps you should post a new question. – Bill Karwin Jan 17 '13 at 20:18
  • as mixed i mean results from different stored procedure calls (with different parameters) inserted in the same temporary table. I think I'll create a new question about this issue. Imagine user A calling SP(A, B, C); and userB calling SP(D, E, F); at the same time so the execution is done at the same time, and the target table gets rows inserted from both procedure calls. – Packet Tracer Jan 22 '13 at 12:59
  • 1
    @FeidaKila, temporary tables are scoped to the session that created them. Two concurrent sessions cannot insert to the same temp table. That is, if you actually used `CREATE TEMPORARY TABLE...` when defining the table. – Bill Karwin Jan 22 '13 at 17:35
  • this is how i'm creating the tables inside my stored procedure (I also use a drop table before the create sentence): CREATE TEMPORARY TABLE IF NOT EXISTS tmpfinal ( – Packet Tracer Feb 08 '13 at 10:13
  • thanks Bill, I've opened a new question about this issue, let's see if someone knows what's happening -> http://stackoverflow.com/questions/14769653/stored-procedure-mixing-results-into-temp-tables-from-different-invokations – Packet Tracer Feb 08 '13 at 10:19
  • Wouldn't client (DB) connection pooling have this side effect? – Martin Suchanek Dec 19 '14 at 23:21
  • @MartinSu, client-side connection pools should `ConnectionImpl.resetServerState()` before re-allocating a connection. This re-authenticates the user, clears any connection state, including transactions, temp tables, session variables, connection character set, etc. – Bill Karwin Dec 19 '14 at 23:36
  • @BillKarwin ah great, that makes sense. Thanks for that! – Martin Suchanek Dec 20 '14 at 00:28
  • It seems like MySQL, or maybe the .NET provider, isn't dropping temporary tables always, or maybe not fast enough, so subsequent connections when pooling is enabled are able to access them. See [this question](http://stackoverflow.com/q/32768917/173497) and [this question](http://stackoverflow.com/q/23337944/173497) for two examples. – Kenny Evitt Sep 24 '15 at 19:52
15

Temporary table is visible only for current session.

So if you have several simultaneuous sessions - each one will have its own independent temporary table with the same name.

Documentation: http://dev.mysql.com/doc/refman/5.1/en/create-table.html, ctrl+f for "You can use the TEMPORARY"

zerkms
  • 230,357
  • 57
  • 408
  • 498
  • 1
    What in the case of a pooled database connection? Session doesn't really matter at all. – Anvesh Checka May 07 '13 at 11:41
  • 2
    @AnveshChecka, in the case of a pooled connection, only one session at a time gets to use that connection. Opening the connection removes it from the available pool (marks it as in-use, or whatever the particular implementation does to remove it from the available connection pool). "Closing" the connection just releases it back to the pool so that another session can use it. Pooling connections eliminates the overhead associated with creating them from scratch, creating context and allocating memory on the server, etc. But a pooled connection is never used concurrently from multiple sessions. – Craig Feb 19 '15 at 22:52