Re: update nested select locking?

> Could someone please settle an disagreement:
>
> Is the following an atomic operation or is dirty data possible:

Neither.

What follows is not a single atomic operation. And dirty data is not possible.

> create tbl
> (
> col number;
> );

On completion of the CREATE TABLE command, an implicit COMMIT is issued. All DDL statements come with a COMMIT. Therefore, the CREATE TABLE command is one atomic operation and what follows is a second atomic operation.

> insert into tbl values (0);
>
> update tbl set col = (select sum(col + 1) from tlb where rownum = 1);

Since there is no explicit start or end of the transaction, I'm going to assume that you run the code as you stated it. In that case, there is an implicit start of the transaction when right before the INSERT statement. The UPDATE statement is part of the same transaction. There is no COMMIT or ROLLBACK given, so the transaction will not end. No one else will be able to see the changes to the table until the COMMIT is issued. However, this transaction will be able to see its own changes. So the UPDATE command will operate as if the INSERT has been completed. But none of that work will be committed until you tell it to do so.

I highly recommend that you read the Oracle Concepts Guide for your version as this is explained in more detail.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown