Product Documentation

SQL Operations Guide

Previous Topic

Next Topic

Supported Transaction Isolation Levels

FairCom DB SQL supports both SQL transaction isolation levels 1 and 2. Transaction isolation levels provide defined levels as to which transaction isolation succeeds. Four transaction isolation levels are defined (SQL-92) by several phenomena. These phenomena are defined as follows:

  • Dirty Reads. A dirty read occurs when a transaction reads data that is not yet committed by another transaction.
  • Non-repeatable Reads. A non-repeatable read is when a transaction reads data from the same row twice, and gets different results, usually as the result of updates from another, simultaneous transaction.
  • Phantoms. A phantom is a row that matches some search criteria, and is not initially seen, as adds or deletes from another transaction have changed the result set available. Upon a second attempt, the transaction is returned a different set of rows.

The SQL-92 defined isolation levels are shown in the following table. An ‘X’ identifies an included phenomenon; ‘--’ indicates absence.

Isolation Characteristic

Level

Dirty Reads

Non-repeatable reads

Phantoms

Read Uncommitted

0

X

X

X

Read Committed

1

--

X

X

Repeatable Read

2

--

--

X

Serializable

3

--

--

--

Keep in mind that a transaction isolation level only affects data changes between transactions. Changes within one’s own transaction are always available and will be seen.

The FairCom DB SQL configuration keyword MAX_SQL_ISOLATION_LEVEL sets the maximum transaction isolation level at which FairCom DB SQL clients can operate. The supported values are:

MAX_SQL_ISOLATION_LEVEL 1; READ_COMMITTED only

MAX_SQL_ISOLATION_LEVEL 2; READ_COMMITTED and REPEATABLE_READ

The FairCom DB SQL default without this configuration keyword is a maximum isolation level of 1. Transaction isolation levels 0 (READ_UNCOMMITTED) and 3 (SERIALIZABLE) are not available with FairCom DB SQL at this time.

If a FairCom DB SQL client requests an isolation level that exceeds the default maximum isolation level, or a maximum level specified in the server configuration file, the FairCom DB SQL client only operates at the maximum isolation level of FairCom DB SQL rather than the requested isolation level.

A FairCom DB SQL ODBC client can request a specific transaction isolation level to work in, provided the server supports this level. The SQL_ATTR_TXN_ISOLATION connection attribute is used to request the level with the SQLSetConnectAttr() ODBC function call.

ODBC Example

/* After a connection handle is established... */

if (SQL_ERROR == (SQLSetConnectAttr(connection_hdl,
SQL_ATTR_TXN_ISOLATION, SQL_TXN_REPEATABLE_READ, 0)) )

printf(“Failed to set the Transaction Isolation Level\n”);

The client application can also determine the transaction isolation level in effect with the SQL_TXN_ISOLATION_OPTION and SQL_DEFAULT_TXN_ISOLATION options with the SQLGetInfo() function call.

ODBC Example

/* After a connection handle is established... */

if (SQL_ERROR == (SQLGetInfo(connection_hdl, SQL_TXN_ISOLATION_OPTION, buf,
MAX_BUF, &buf_length)))

do_error(“Failed to Retrieve Current Transaction Isolation Level\n”);

if (SQL_ERROR == (SQLGetInfo(connection_hdl, SQL_DEFAULT_TXN_ISOLATION, buf,
MAX_BUF, &buf_length)))

printf(“Failed to Retrieve Default Transaction Isolation Level\n”);

Requesting transaction isolation level 2, REPEATABLE_READ can introduce delays as additional overhead is involved in acquiring blocking read locks to ensure repeatable reads.

TOCIndex