1.1.1

behavior for replicated tables. The transaction manager works closely with the SQLFire membership management
system to make sure that, irrespective of failures or adding/removing members, changes to all rows are either
applied to all available copies at commit time, or they are applied to none.
Note: SQLFire does not support adding new members to a cluster for an ongoing transaction. If you add
a new member to the cluster in the middle of a transaction and the new member is to store data involved
in the transaction, SQLFire implicitly rolls back the transaction and throws a SQLException (SQLState:
"X0Z05").
There is no centralized transaction coordinator in SQLFire. Instead, the member on which a transaction was
started acts as the coordinator for the duration of the transaction. If the application updates one or more rows,
the transaction coordinator determines which owning members are involved, and acquires local "write" locks
on all of the copies of the rows. At commit time, all changes are applied to the local cache and any redundant
copies. If another concurrent transaction attempts to change one of the rows, the local "write" acquisition fails
for the row, and that transaction is automatically rolled back. In the case where there is no persistent table
involved, there is no need to issue a two-phase commit to redundant members; in this case, commits are efficient,
single-phase operations.
Unlike traditional distributed databases, SQLFire does not use write-ahead logging for transaction recovery in
case the commit fails during replication or redundant updates to one or more members. The most likely failure
scenario is one where the member is unhealthy and gets forced out of the distributed system, guaranteeing the
consistency of the data. When the failed member comes back online, it automatically recovers the
replicated/redundant data set and establishes coherency with the other members. If all copies of some data go
down before the commit is issued, then this condition is detected using the group membership system, and the
transaction is rolled back automatically on all members.
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 on page 149. 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 on page 149.
• 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.
vFabric SQLFire User's Guide152
Developing Applications with SQLFire