Home » SQL & PL/SQL » SQL & PL/SQL » DELETE statement deletes rows inserted after the DELETE statement began (Oracle Database 10g 10.2.0.1.0, Windows XP)
icon5.gif  DELETE statement deletes rows inserted after the DELETE statement began [message #421695] Wed, 09 September 2009 21:10 Go to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
My English is poor. If you find some syntax errors in the following text, please point it out. Thank you!


Preparing the data:
create table t (id number);
insert into t (id) values(1);
commit;


Test case 1, start two sessions and execute statements in the following order:

session 1:
update t set id=2 where id=1;


session 2:
delete from t;

Now session 2 is blocked by session 1.

session 1:
insert into t (id) values(3);
insert into t (id) values(4);
commit;


session 2:
Now session 2 is unblocked and deletes 1 row which id is 2 now. This is what I want.



Test case 2, also start two sessions like test case 1 and execute statements in the following order:

session 1:
delete from t where id=1;


session 2:
delete from t;

Now session 2 is blocked by session 1.

session 1:
insert into t (id) values(3);
insert into t (id) values(4);
commit;


session 2:
Now session 2 is unblocked and deletes 2 rows. But I think that session 2 should delete 0 row after unblocked.
Because the 2 rows which id are 3 and 4 were inserted and commited after the session 2 delete statement began, session 2 should not see these rows.
But session 2 delete the two new rows.
Why? Could anyone give me some links to documents? I have searched google and Oracle Documents, but no result.



Re: DELETE statement deletes rows inserted after the DELETE statement began [message #421696 is a reply to message #421695] Wed, 09 September 2009 21:30 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Because the 2 rows which id are 3 and 4 were inserted and commited
Your post shows no COMMIT,so we don't know for sure what happened & when.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


use sqlplus &
SQL> SET TIME ON

CUT & PASTE whole session so we can see exactly what & when you did what and how Oracle responded.
Re: DELETE statement deletes rows inserted after the DELETE statement began [message #421702 is a reply to message #421695] Wed, 09 September 2009 22:31 Go to previous messageGo to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
BlackSwan, thank you for your advices.

Test case 2, sqlplus spool output:

session 1:
11:24:34 SQL> select * from v$version;

BANNER                                                                                              
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod                                    
PL/SQL Release 10.2.0.1.0 - Production                                                              
CORE	10.2.0.1.0	Production                                                                          
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production                                             
NLSRTL Version 10.2.0.1.0 - Production                                                              

Elapsed: 00:00:00.00
11:24:40 SQL> create table t (id number);

Table created.

Elapsed: 00:00:00.01
11:24:48 SQL> insert into t (id) values (1);

1 row created.

Elapsed: 00:00:00.01
11:25:09 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
11:25:13 SQL> delete from t where id=1;

1 row deleted.

Elapsed: 00:00:00.00
11:25:30 SQL> insert into t (id) values (3);

1 row created.

Elapsed: 00:00:00.00
11:25:44 SQL> insert into t (id) values (4);

1 row created.

Elapsed: 00:00:00.00
11:25:47 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
11:26:34 SQL> select * from t;

no rows selected

Elapsed: 00:00:00.01
11:26:52 SQL> spool off


session 2:
11:26:09 SQL> select * from t;

        ID                                                                                          
----------                                                                                          
         1                                                                                          

Elapsed: 00:00:00.01
11:26:17 SQL> delete from t;

2 rows deleted.

Elapsed: 00:00:11.98
11:26:34 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
11:26:41 SQL> select * from t;

no rows selected

Elapsed: 00:00:00.01
11:26:45 SQL> spool off


Re: DELETE statement deletes rows inserted after the DELETE statement began [message #421709 is a reply to message #421695] Wed, 09 September 2009 23:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The default Oracle transaction level ("read committed", which is the same one for most other rdbms) allows such behaviour.
Read this very interesting article from T. Kyte: On Transaction Isolation Levels

Regards
Michel


Re: DELETE statement deletes rows inserted after the DELETE statement began [message #421713 is a reply to message #421709] Thu, 10 September 2009 00:56 Go to previous messageGo to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
Michel Cadot wrote on Thu, 10 September 2009 12:34
The default Oracle transaction level ("read committed", which is the same one for most other rdbms) allows such behaviour.
Read this very interesting article from T. Kyte: On Transaction Isolation Levels

Regards
Michel





Michel, thank you for your help. I have read the topic which you give me.

All my test cases use statement level isolation (READ COMMITTED isolation level). A statement can only see the data commited before the statement began.

My confusion is that test case 1 didn't delete the two new rows but test case 2 deleted the two new rows.

Here is test case 1 sqlplus spool output:

session 1:
13:17:29 SQL> select * from v$version;

BANNER                                                                                              
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod                                    
PL/SQL Release 10.2.0.1.0 - Production                                                              
CORE	10.2.0.1.0	Production                                                                          
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production                                             
NLSRTL Version 10.2.0.1.0 - Production                                                              

Elapsed: 00:00:00.00
13:18:25 SQL> create table t (id number);

Table created.

Elapsed: 00:00:00.07
13:18:32 SQL> insert into t (id) values (1);

1 row created.

Elapsed: 00:00:00.01
13:18:40 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
13:18:44 SQL> update t set id=2 where id=1;

1 row updated.

Elapsed: 00:00:00.00
13:19:06 SQL> insert into t (id) values (3);

1 row created.

Elapsed: 00:00:00.01
13:19:28 SQL> insert into t (id) values (4);

1 row created.

Elapsed: 00:00:00.00
13:19:32 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
13:20:07 SQL> select * from t;

        ID                                                                                          
----------                                                                                          
         3                                                                                          
         4                                                                                          

Elapsed: 00:00:00.00
13:20:22 SQL> spool off



session 2:
13:19:48 SQL> select * from t;

        ID                                                                                          
----------                                                                                          
         1                                                                                          

Elapsed: 00:00:00.00
13:19:52 SQL> delete from t;

1 row deleted.

Elapsed: 00:00:09.40
13:20:07 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
13:20:12 SQL> select * from t;

        ID                                                                                          
----------                                                                                          
         3                                                                                          
         4                                                                                          

Elapsed: 00:00:00.01
13:20:16 SQL> spool off


In test case 1 two new rows with id 3 and 4 are remained.
But in test case 2 two new rows with id 3 and 4 are deleted.

Test case 2 sqlplus output is on the previous post.

The only difference between test case 1 and test case 2 is the blocking statement in their session 1.

In test case 1 the blocking statement use UPDATE t set id=2 where id=1.
In test case 2 the blocking statement use DELETE from t where id=1.

But the results of test case 1 and test case 2 are different.
Why did this happen?
Re: DELETE statement deletes rows inserted after the DELETE statement began [message #421715 is a reply to message #421713] Thu, 10 September 2009 01:08 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You need to read about read consistency during DML operation as well written by Tom Kyte in his blog as mentioned below.

Part I
Part II
Part III

[Update] Just to add, in your test scenario 1 change the delete condition as
delete from t where id > 0;
and see what happens

[Updated on: Thu, 10 September 2009 01:13]

Report message to a moderator

Re: DELETE statement deletes rows inserted after the DELETE statement began [message #421735 is a reply to message #421715] Thu, 10 September 2009 03:27 Go to previous messageGo to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
bonker wrote on Thu, 10 September 2009 14:08
You need to read about read consistency during DML operation as well written by Tom Kyte in his blog as mentioned below.

Part I
Part II
Part III

[Update] Just to add, in your test scenario 1 change the delete condition as
delete from t where id > 0;
and see what happens


bonker, thank you for your help.

I can't access the blogspot.com because website blogspot.com is prohibited by our government.

Could you save Tom Kyte's Part I, Part II and Part III topics and send them to me by email? My email address is shchenchang@gmail.com.

Thank you very much!
Re: DELETE statement deletes rows inserted after the DELETE statement began [message #421737 is a reply to message #421735] Thu, 10 September 2009 03:39 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
It will be too much of an effort to save them and email it to you.
Can you access Asktom Site then you will find the same discussion
here
Re: DELETE statement deletes rows inserted after the DELETE statement began [message #421754 is a reply to message #421737] Thu, 10 September 2009 04:40 Go to previous message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
bonker wrote on Thu, 10 September 2009 16:39
It will be too much of an effort to save them and email it to you.
Can you access Asktom Site then you will find the same discussion
here


Thank you for your help! I got it and will read it carefully.
Previous Topic: Simulator (merged 2)
Next Topic: Optimization in the where clause
Goto Forum:
  


Current Time: Sat Dec 03 08:16:22 CST 2016

Total time taken to generate the page: 0.13733 seconds