oracle - what statements need to be committed?

Question

What are the list of statements that need to be committed before further action on the table in order to avoid a lock? I am not talking about full transactions with multiple statements and transaction integrity; instead I am referring to single statements.

I know insert should be committed but truncate has an autocommit. What is the full list of statements that need to be committed?

Need to be committed (starter list):

UPDATE INSERT DELETE 

Answer 1

DML (Data Manipulation Language) commands need to be commited/rolled back. Here is a list of those commands.

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes records from a table, the space for the records remain MERGE - UPSERT operation (insert or update) CALL - call a PL/SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCK TABLE - control concurrency 

Answer 2

In mechanical terms a COMMIT makes a transaction. That is, a transaction is all the activity (one or more DML statements) which occurs between two COMMIT statements (or ROLLBACK).

In Oracle a DDL statement is a transaction in its own right simply because an implicit COMMIT is issued before the statement is executed and again afterwards. TRUNCATE is a DDL command so it doesn't need an explicit commit because calling it executes an implicit commit.

From a system design perspective a transaction is a business unit of work. It might consist of a single DML statement or several of them. It doesn't matter: only full transactions require COMMIT. It literally does not make sense to issue a COMMIT unless or until we have completed a whole business unit of work.

This is a key concept. COMMITs don't just release locks. In Oracle they also release latches, such as the Interested Transaction List. This has an impact because of Oracle's read consistency model. Exceptions such as ORA-01555: SNAPSHOT TOO OLD or ORA-01002: FETCH OUT OF SEQUENCE occur because of inappropriate commits. Consequently, it is crucial for our transactions to hang onto locks for as long as they need them.