What is a transaction?

By: Borland Staff

Abstract: This document defines what a transaction is. It provides an introductory level overview of the basic types of transaction options supported by InterBase.

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.

Server Response from: ETNASC04