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):
UPDATEINSERTDELETEBest Answer
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 datawithin schema objects. Some examples:
INSERT - insert data into a tableUPDATE - updates existing data within a tableDELETE - deletes records from a table, the space for the records remainMERGE - UPSERT operation (insert or update)CALL - call a PL/SQL or Java subprogramEXPLAIN PLAN - explain access path to dataLOCK TABLE - control concurrencyIn 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.
DML have to be committed or rollbacked. DDL cannot.
http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands
You can switch auto-commit on and that's again only for DML.DDL are never part of transactions and therefore there is nothing like an explicit commit/rollback.
truncate is DDL and therefore commited implicitly.
Edit
I've to say sorry. Like @DCookie and @APC stated in the comments there exist sth like implicit commits for DDL. See here for a question about that on Ask Tom.This is in contrast to what I've learned and I am still a bit curious about.
And a key point - although TRUNCATE TABLE seems like a DELETE with no WHERE clause, TRUNCATE is not DML, it is DDL. DELETE requires a COMMIT, but TRUNCATE does not.