Global Temporary Table Concurrency

Question

Hi,

I have a global temp table which is set as delete on commit. How does it behave on concurrency issue? I mean what happens if another session wants to use that global temporary table? The answer will probably not be "they share the same data".

Now, if my guess is correct :), is the table locked until the first connection commits, or does the dbms create a global temp table for each connection? ( something like an instance of the table? )

Answer 1

Hi sahs,

From the documentation:

The data in a temporary table is visible only to the session that inserts the data into the table.

Each session will have its logical independent copy of the temporary table.

Since you can not see other sessions' data and since Oracle deals with locks at the row level, you can not be blocked by other sessions' DML. Concurrent DML (Insert, Delete, Update) won't affect other sessions.

Only DDL will need a lock on the table (ie: ALTER TABLE...)