Re: Two "create table as select" locking?

From: Anatoly Moskovsky <avm_at_trais.com.ua>
Date: Thu, 23 Aug 2001 16:36:50 +0300
Message-ID: <TRS10405CDF5@trais.com.ua>

Hi!

• On august 23 Greg Simpson wrote:

 GS> Question: did the two "create table as select * from A" commands
 GS> lock each other out? I know two "select * from A" should never lock
 GS> each other, but does the "create table as" request a different type
 GS> of lock?

When you run "create table as" statement Oracle runs implicit "create","select", and "insert" statements. Dead lock is possible if two sessions insert rows in different order(primary key must be enabled):

SQL1> create table tmp1 (n number primary key);
SQL1> insert into tmp1 values (1);
SQL2> insert into tmp1 values (2);
SQL1> insert into tmp1 values (2); --

now SQL1 is waiting
SQL2> insert into tmp1 values (1);
now SQL2 is waiting but for SQL1 Oracle detects a dead-lock and unlock the session (SQL1)
IMHO, this detection works with expicit inserts, but maybe does not work with implicit one such as "create table as select...".

Bye

---

Anatoly Moskovsky, Oracle Developer avm_at_sqlbatch.com [SQL Batch/Oracle, DBA tools, reverse engineering, scripting extensions]: http://sqlbatch.com/sb/ Received on Thu Aug 23 2001 - 08:36:50 CDT