What is a transaction?

Abstract: What is a transaction?

Problem: This document defines what a transaction is. It provides an introductory level overview of the basic types of transaction options supported by InterBase. Solution: The information in this article applies to: * All InterBase versions 1. Definition of a transaction ================================= A transaction defines an atomic scope of work performed by a database server. From the start transaction command until the commit/rollback command, all requests to modify database tables will either succeed (commit) or fail (rollback) together. This is done to ensure that related changes to a database complete as a single consistent unit. 2. Beginning a transaction ============================ Transactions are invoked with a set transaction statement. Listed below is a diagram of the syntax for declaring a transaction. Each of the options listed will be described in section 3. SET TRANSACTION [NAME transaction_name] [ACCESS MODE] [LOCK RESOLUTION] [ISOLATION LEVEL] [RESERVING] [USING] [FOR [SHARED |PROTECTED] {READ | WRITE} 3. Attributes of a transaction ================================ The following attributes can be specified for a transaction: Access Mode, Lock Resolution, Isolation level, table reservation, and Database specification. A. Access Mode ------------------------ This determines the type of access the transaction can have for a particular table. Access mode can either be set to READ ONLY or READ WRITE. B. Lock Resolution ---------------------------- This setting specifies what the transaction is to do if it encounters a locked row during an update or delete. There are two possible values for Lock resolution, WAIT and NO WAIT. WAIT = Wait until resource becomes available so we can try the operation again. NO WAIT = Immediately give the user an error message when a lock conflict occurs. C. Isolation Level ------------------------- The isolation level setting determines what the transaction can see in terms of updates that other transactions may be committing. There are three isolation levels supported by InterBase. They are READ COMMITTED, SNAPSHOT TABLE STABILITY, and SNAPSHOT. 1. READ COMMITTED = This means that a transaction can see and update all committed has two further options that can be specified. They are : RECORD_VERSION, and NO RECORD_VERSION. RECORD_VERSION= Reads the latest committed version of a record. It will ignore uncommitted, but more recent versions of a record. NO RECORD_VERSION = Reads only the latest version of a record. If the latest version of the record is uncommitted and WAIT lock resolution is being used, the transaction waits until the this version is resolved (committed or rolled back). If NO WAIT lock resolution is used then an error is reported immediately. 2. SNAPSHOT This isolation level allows the transaction to see a view of the records as they were when the transaction began. Subsequent changes by other transactions that occur after the transaction began will not be seen. 3. SNAPSHOT TABLE SECURITY Prevents other transactions from modifying rows in use by the transaction. Other transactions are allowed to view the rows in use by the transaction. D. RESERVING ----------------------- Specifies a set of tables to lock for this transaction. It also determines the level of access that other transactions will have on reserved tables during the transaction. The reservation options are : PROTECTED READ, PROTECTED WRITE, SHARED READ, and SHARED WRITE. 1. PROTECTED READ = No other transactions can read rows that this transaction is working on. 2. PROTECTED WRITE = No other transactions can update rows that this transaction is working on. Transactions with Read committed and SNAPSHOT isolation levels can read rows. 3. SHARED READ = Any transaction can read from this table. READ WRITE transactions can write to this table. 4. SHARED WRITE = Any SNAPSHOT or READ COMMITTED READ WRITE transaction cna update this table. Other SNAPSHOT and READ COMMITTED transactions can also select from this table. E. USING -------------- This specifies the databases that a transaction can access. 3. Writing changes to disk ============================= After a transaction is finished doing its work, the application can write the changes to disk with a COMMIT command. If the application wants to undo changes made by the transaction, it can issue the ROLLBACK command. Issuing a rollback command in a transaction ends the transaction.