What about partitioning?
Ben, July 12, 2007 - 1:23 pm UTC
Thanks for all you do on this site, it's very helpful.
How does partitioning play into this locking? If I hand a group of rows to an insert with the append hint, that all happen to have the same list partitioning key for example, does the insert still lock the whole table?
July 12, 2007 - 2:20 pm UTC
it doesn't know that the insert will be inserting into a single partition.
so, if you insert /*+ APPEND */ into table select .... - table is locked.
if you insert /*+ APPEND */ into table partition(pname) select ... - then only that partition segment is locked.
Is regular dml allowed?
Reader, July 19, 2007 - 2:54 pm UTC
Is regulare DML allowed concurrently with an insert append
i.e if a session is running insert append, can another session change or do regulare insert/delete/update on the table
July 19, 2007 - 2:58 pm UTC
table is locked.
other sessions/transactions - block.
ops$tkyte%ORA9IR2> create table t ( x int );
Table created.
ops$tkyte%ORA9IR2> insert into t values ( 0);
1 row created.
ops$tkyte%ORA9IR2> commit;
Commit complete.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert /*+ append */ into t select 1 from dual;
1 row created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> declare
2 pragma autonomous_transaction;
3 begin
4 update t set x = 2 where x = 0;
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
What about GLobal Temporary Tables
Tom, May 29, 2008 - 9:47 am UTC
Would this also apply to multiple threads inserting with "append" to the "same" global temporary table?
May 29, 2008 - 10:05 am UTC
they each get their own segment at runtime, they are separate objects....
in fact, my temporary table might put its data into TEMP_TS1 and your insert into the same temporary table might put its data into TEMP_TS2
so, no, it would not apply to global temporary tables. Each session gets its own temporary table - entirely separate and distinct from anyone elses.
Global Temp table locking?
Tom, June 09, 2008 - 1:31 pm UTC
And would this also be true of multiple sessions truncating their version of the "same" global temp table, as truncating a regular table would involve a table lock?
June 09, 2008 - 2:20 pm UTC
I don't know what you mean.
what is the relevance of "multiple sessions" in this context?
GLobal Temp table locking?
Tom, June 09, 2008 - 2:46 pm UTC
If several sessions run processes which include truncating the same global temporary table, would they risk locking each other because of the truncate or would these sessions enjoy the same "immunity" you described in response to my last message?
June 09, 2008 - 3:42 pm UTC
same answer. really easy to see.
ops$tkyte%ORA10GR2> create global temporary table gtt ( x int );
Table created.
ops$tkyte%ORA10GR2> insert into gtt values ( 1 );
1 row created.
<b>
ops$tkyte%ORA10GR2> !sqlplus /
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 9 15:34:44 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
</b>
ops$tkyte%ORA10GR2> select * from gtt;
no rows selected
ops$tkyte%ORA10GR2> truncate table gtt;
Table truncated.
ops$tkyte%ORA10GR2> insert into gtt select rownum from all_users;
40 rows created.
ops$tkyte%ORA10GR2> truncate table gtt;
Table truncated.
ops$tkyte%ORA10GR2> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte%ORA10GR2> select * from gtt;
X
----------
1