locking strategy on deletes

[blom0344]

How does ORACLE work with regards to deleting a set of records from a table? Does it lock the entire table , avoiding inserts untill the commit is issued (on the delete statement) Can the table still be accessed for read-only action during the delete action?

Ties Blom

 

[carp]

Oracle handles DELETE like it does INSERT and UPDATE. Only row-level locks are issued; the other rows in the table are still available for UPDATE/DELETE, and INSERTs can also be executed simultaneously.
In addition, the deleted rows are available to SELECT statements until the COMMIT is issued.

 

[Beilstwh]

Until the delete is committed, the original records are available for any other user to read. As soon as the commit is done, the rows are gone. Any new inserts or updates (except on the deleted records are allowed before, during, or after the commit. For a full description see:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c01_02intro.htm#46633

Bill
Oracle DBA/Developer
New York State, USA