Supported Transaction Isolation Levels

SQLFire supports several transaction isolation levels. It does not support the SERIALIZABLE isolation level, nested transactions, or savepoints.

SQLFire supports these transaction isolation levels:
  • NONE. By default, connections in SQLFire do not engage in transactions, unlike in other databases (see Data Consistency Concepts. This corresponds to the JDBC TRANSACTION_NONE isolation level (or IsolationLevel.Chaos in ADO.NET, or the “SET ISOLATION RESET�? SQL command). However, this default behavior does not mean that there is no isolation and that connections have access to uncommitted state from other in-process transactions. The default consistency model without transactions is described in Understanding the Data Consistency Model.
  • READ_UNCOMMITTED. SQLFire internally upgrades this isolation to READ_COMMITTED.
  • READ_COMMITTED. SQLFire ensures that ongoing transactional as well as non-transactional (isolation-level NONE) operations never read uncommitted (dirty) data. SQLFire accomplishes this by maintaining transactional changes in a separate transaction state that is applied to the actual data-store for the table only at commit time.
  • REPEATABLE_READ. SQLFire supports the REPEATABLE_READ isolation level according to the ANSI SQL standard. A transaction that reads the same row more than once always sees the same column values for the row. REPEATABLE_READ also guarantees that the underlying committed row in a table never changes after the first read in a transaction, until the transaction completes (for example, it commits or aborts).

    SQLFire applies read and write locks to copies of selected data to ensure repeatable reads for the duration of a transaction. SQLFire does not use range locks, and phantom reads are still possible with this isolation level.

    In addition, readers that use the REPEATABLE_READ isolation level are guaranteed to see distributed, atomic commits. This means that if there is a transaction that writes rows and commits over multiple SQLFire members, then readers either see all of the commit row values across all members of the distributed system (after the commit), or they will see all of before-committed row values across all members. Readers never see some committed rows on one member and before-committed row values on another node. To support this behavior, SQLFire uses a 2-phase commit protocol for all REPEATABLE_READ transactions that have pending writes.

    SQLFire detects conflicts between two transactions that write on the same row either during the transactions or just before a commit for READ_COMMITTED and REPEATABLE_READ transactions. However, if a REPEATABLE_READ transaction writes on the same row that has been read by another transaction, then SQLFire always detects such a conflict before the writer commits (in the first phase of the commit). This enables the system to minimize conflicts where reader transactions are short in duration and the transactions complete before the writer starts its commit.

    Note: REPEATABLE_READ transactions that have only performed reads never receive a conflict. In particular, even if a transaction reads a row after it has already been marked for write by another transaction, it is the writer that sees the conflict at commit time if the reader transaction has not completed by then. For both the write-write and write-read cases, if a reader or writer attempts to lock a row while the commit of another writer transaction on the row is in progress, then the reader waits for the commit to complete. The commit is usually short in duration, so this behavior reduces conflicts and ensures that the wait is finite.

    SQLFire provides these system properties that you can use to alter the conflict detection behavior for READ_COMMITTED and REPEATABLE_READ transactions: gemfire.WRITE_LOCK_TIMEOUT, gemfire.READ_LOCK_TIMEOUT, and gemfire.LOCK_MAX_TIMEOUT.

    Note: You must set these system properties to the same value on each data store in your SQLFire distributed system.
For more information, see: