Transaction - Commit in oracle
COMMIT;
A commit ends the current transaction and makes permanent all changes performed in the transaction.
When a transaction commits, the following actions occur:
The internal transaction table for the associated undo tablespace records that the transaction has committed. The corresponding unique SCN of the transaction is assigned and recorded in the transaction table. See “Serializable Isolation Level”.
Users who were enqueued waiting on locks held by the uncommitted transaction are allowed to proceed with their work.
If modified blocks containing data from the committed transaction are still in the SGA, and if no other session is modifying them, then the database removes lock-related transaction information from the blocks. Ideally, the COMMIT cleans out the blocks so that a subsequent SELECT does not have to perform this task.
Because a block cleanout generates redo, a query may generate redo and thus cause blocks to be written during the next checkpoint.
After a transaction commits, users can view the changes.
Typically, a commit is a fast operation, regardless of the transaction size. The speed of a commit does not increase with the size of the data modified in the transaction. The lengthiest part of the commit is the physical disk I/O performed by LGWR. However, the amount of time spent by LGWR is reduced because it has been incrementally writing the contents of the redo log buffer in the background.
The default behaviour is for LGWR to write redo to the online redo log synchronously and for transactions to wait for the buffered redo to be on disk before returning a commit to the user. However, for lower transaction commit latency, application developers can specify that redo be written asynchronously so that transactions need not wait for the redo to be on disk and can return from the COMMIT call immediately.