Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

SET TRANSACTION

Name

SET TRANSACTION -- Sets the transaction isolation level for the current transaction block.

Synopsis

SET TRANSACTION ISOLATION LEVEL
    { READ COMMITTED | SERIALIZABLE }
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
    { READ COMMITTED | SERIALIZABLE }

Parameters

READ COMMITTED

The clause that specifies that statements will be able to view changes to the database that were committed before the transaction began. This is the default.

SERIALIZABLE

The clause that specifies that statements will be able to view all rows that were committed in the database before the transaction's first DML statement is executed.

Results

SET VARIABLE

The message returned when the isolation level has been set successfully. To verify that it is correctly set, you can issue the command SHOW TRANSACTION ISOLATION LEVEL, which should then return the variable's setting (either READ COMMITTED or SERIALIZABLE).

Description

Use the SET TRANSACTION command to set the transaction isolation level for the current transaction. This change will affect only the current transaction; all other subsequent transactions must have their isolation mode explicitly set, otherwise the default of READ COMMITTED will be used.

You can only use this command before the first DML statement has been executed. A DML statement is one of SELECT, INSERT, DELETE, UPDATE, FETCH, or COPY.

To set the default transaction isolation level (as opposed to individual transaction), use SET SESSION CHARACTERISTICS and specify either READ COMMITTED or SERIALIZABLE. Issuing a SET TRANSACTION command from within a transaction can override this default setting.

When the isolation level is set to READ COMMITTED, all statements within the transaction view only the rows that were committed before the transaction was started. Setting the isolation level to SERIALIZABLE allows statements within the transaction to view changes made to the database before the first DML statement was executed within the transaction.

Examples

The following example sets the transaction isolation level to SERIALIZABLE for the current transaction:

testdb=# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET VARIABLE

The next example demonstrates setting the default transaction isolation level for the current session:

testdb=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET VARIABLE

 
 
  Published courtesy of O'Reilly Design by Interspire