Difference between revisions of "SET TRANSACTION"

From Recital Documentation Wiki
Jump to: navigation, search
(Syntax)
 
(7 intermediate revisions by 3 users not shown)
Line 1: Line 1:
=SET TRANSACTION=
 
 
 
==Class==
 
SQL Applications
 
 
 
 
==Purpose==
 
==Purpose==
 
To set the current transaction state
 
To set the current transaction state
Line 14: Line 7:
  
 
SET TRANSACTION ISOLATION LEVEL <isolation-level>
 
SET TRANSACTION ISOLATION LEVEL <isolation-level>
 +
  
 
==See Also==
 
==See Also==
[[CLOSE]], [[DECLARE CURSOR]], [[DROP CURSOR]], [[FETCH]], [[OPEN]], [[SELECT]]
+
[[CLOSE]], [[Configuration Files]], [[DECLARE CURSOR]], [[DROP CURSOR]], [[FETCH]], [[OPEN]], [[SQL SELECT|SELECT]], [[TXLEVEL()]], [[TXNISOLATION()]], [[TXNLEVEL()]]
  
  
 
==Description==
 
==Description==
 
A current transaction state can be either read-only or read-write.  Three further aspects of transaction behavior are configurable: dirty reads, non-repeatable reads and phantom reads.  Dirty reads occur when a transaction updates a row, then a second transaction reads that row before the first transaction commits.  If the first transaction rolls back the change, the information read by the second transaction becomes invalid.  Non-repeatable reads occur when a transaction reads a row then another transaction updates the same row.  If the second transaction commits, subsequent reads by the first transaction get different values than the original read.  Phantoms occur when a transaction reads a set of rows that satisfy a search condition and then another transaction updates, inserts, or deletes one or more rows that satisfy the first transaction’s search condition.  In this case, if the first transaction performs subsequent reads with the same search condition, it reads a different set of rows.
 
A current transaction state can be either read-only or read-write.  Three further aspects of transaction behavior are configurable: dirty reads, non-repeatable reads and phantom reads.  Dirty reads occur when a transaction updates a row, then a second transaction reads that row before the first transaction commits.  If the first transaction rolls back the change, the information read by the second transaction becomes invalid.  Non-repeatable reads occur when a transaction reads a row then another transaction updates the same row.  If the second transaction commits, subsequent reads by the first transaction get different values than the original read.  Phantoms occur when a transaction reads a set of rows that satisfy a search condition and then another transaction updates, inserts, or deletes one or more rows that satisfy the first transaction’s search condition.  In this case, if the first transaction performs subsequent reads with the same search condition, it reads a different set of rows.
 
The <level> can be any one of the following:
 
* SERIALIZABLE
 
* REPEATABLE READ
 
* READ COMMITTED
 
* READ UNCOMMITTED
 
  
 
If you use a SET TRANSACTION statement, it must be the first statement in your transaction.
 
If you use a SET TRANSACTION statement, it must be the first statement in your transaction.
 +
 
NOTE: This command can also be used as a standard SET COMMAND in the config.db file, to set the transaction state on a system or application wide basis.
 
NOTE: This command can also be used as a standard SET COMMAND in the config.db file, to set the transaction state on a system or application wide basis.
 +
  
 
{| class="wikitable"
 
{| class="wikitable"
|'''Keywords'''||'''Description'''
+
!Keywords||Description
 
|-
 
|-
 
|READ ONLY||Set the default transaction type to read-only.  
 
|READ ONLY||Set the default transaction type to read-only.  
Line 49: Line 39:
 
|-
 
|-
 
|}
 
|}
 +
  
 
==Example==
 
==Example==
 
<code lang="recital">
 
<code lang="recital">
set transaction isolation level read uncommitted</code>
+
set transaction isolation level read uncommitted
 +
</code>
 +
 
  
 
==Products==
 
==Products==
Recital Database Server, Recital Mirage Server, Recital Terminal Developer
+
Recital, Recital Server
 
[[Category:Documentation]]
 
[[Category:Documentation]]
 
[[Category:Commands]]
 
[[Category:Commands]]
 
[[Category:Set_Commands|TRANSACTION]]
 
[[Category:Set_Commands|TRANSACTION]]
 
[[Category:SQL]]
 
[[Category:SQL]]
 +
[[Category:SQL Set Commands]]

Latest revision as of 15:49, 25 November 2009

Purpose

To set the current transaction state


Syntax

SET TRANSACTION [READ ONLY | READ WRITE]

SET TRANSACTION ISOLATION LEVEL <isolation-level>


See Also

CLOSE, Configuration Files, DECLARE CURSOR, DROP CURSOR, FETCH, OPEN, SELECT, TXLEVEL(), TXNISOLATION(), TXNLEVEL()


Description

A current transaction state can be either read-only or read-write. Three further aspects of transaction behavior are configurable: dirty reads, non-repeatable reads and phantom reads. Dirty reads occur when a transaction updates a row, then a second transaction reads that row before the first transaction commits. If the first transaction rolls back the change, the information read by the second transaction becomes invalid. Non-repeatable reads occur when a transaction reads a row then another transaction updates the same row. If the second transaction commits, subsequent reads by the first transaction get different values than the original read. Phantoms occur when a transaction reads a set of rows that satisfy a search condition and then another transaction updates, inserts, or deletes one or more rows that satisfy the first transaction’s search condition. In this case, if the first transaction performs subsequent reads with the same search condition, it reads a different set of rows.

If you use a SET TRANSACTION statement, it must be the first statement in your transaction.

NOTE: This command can also be used as a standard SET COMMAND in the config.db file, to set the transaction state on a system or application wide basis.


Keywords Description
READ ONLY Set the default transaction type to read-only.
READ WRITE Set the default transaction type to read-write.
ISOLATION LEVEL Specify how the transaction will perform.
SERIALIZABLE This will disable dirty reads, non-repeatable reads and phantom reads. This is the default isolation level.
REPEATABLE READ This will disable dirty reads, non-repeatable reads and enable only phantom reads.
READ UNCOMMITTED This will enable dirty reads, non-repeatable reads and phantom reads.
READ COMMITTED This will disable dirty reads and enable non-repeatable reads and phantom reads.


Example

set transaction isolation level read uncommitted


Products

Recital, Recital Server