Oracle Database - COMMIT (of a transaction)

Card Puncher Data Processing

About

Transaction - Commit in oracle

Syntax

COMMIT;

Committing Transactions

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”.

  • The log writer (LGWR) process writes remaining redo log entries in the redo log buffers to the online redo log and writes the transaction SCN to the online redo log. This atomic event constitutes the commit of the transaction.
  • Oracle Database releases locks held on rows and tables.

Users who were enqueued waiting on locks held by the uncommitted transaction are allowed to proceed with their work.

  • Oracle Database deletes savepoints.
  • Oracle Database performs a commit cleanout.

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.

  • Oracle Database marks the transaction complete.

After a transaction commits, users can view the changes.

Performance

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.

Documentation / Reference





Discover More
Data System Architecture
Data Management - (Transaction|Request|Commit|Redo) Log

(Transaction|Request|commit) logs are structured log file store all changes made to the data as they occur. They permits the implementation of : transaction isolation undoable operation. recovery...
Card Puncher Data Processing
Oracle Database - (Db Block Get|Current Get) Buffer Mode (CU)

db block get is a buffer retrieval mode. It's also know as current mode get. In this mode, the block is retrieved in the buffer cache. The block is then known as a CURRENT block. You will see these...
Card Puncher Data Processing
Oracle Database - DDL Locks (data dictionary lock)

A data dictionary (DDL) lock protects the definition of a schema object while an ongoing DDL operation (CREATE, ALTER, DROP) acts on or refers to the object. Only individual schema objects that are modified...
Card Puncher Data Processing
Oracle Database - Database Writer Process (DBWn)

Database writer process (DBWn) is a background process that writes buffers in the database buffer cache to data files. Modified or new data is not necessarily written to a datafile immediately. To reduce...
Card Puncher Data Processing
Oracle Database - Distributed Transactions

A distributed database is a set of databases in a distributed system that can appear to applications as a single data source. A distributed transaction is a transaction that includes one or more statements...
Toad Lock
Oracle Database - Locks

Lock Mechanism in Oracle Oracle Database provides: data concurrency, consistency. The data a session is viewing or changing must not be changed by other sessions until the user is finished. and...
Card Puncher Data Processing
Oracle Database - ROLLBACK (undo of a transaction)

Use the ROLLBACK statement to undo work done in the current transaction or to manually undo the work done by an in-doubt distributed transaction. An implicit ROLLBACK occurs when the session (or...
Oracle Database Row Lock Tx
Oracle Database - Row Locks (TX)

A row lock, also called a TX lock, is a lock on a single row of table. A transaction acquires a row lock for each row modified by an INSERT, UPDATE, DELETE, MERGE, or SELECT ... FOR UPDATE statement. The...
Card Puncher Data Processing
Oracle Database - SAVEPOINT (of a transaction)

A savepoint is a user-declared intermediate marker within the context of a transaction. Internally, this marker resolves to an SCN. Savepoints divide a long transaction into smaller parts. If you use...
Card Puncher Data Processing
Oracle Database - System Change Numbers (SCNs) - Point in time

A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction....



Share this page:
Follow us:
Task Runner