1.0

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:
• SET ISOLATION on page 486
• java.sql.Connection Interface on page 332
• sqlf commands: autocommit on page 399, commit on page 401, and rollback on page 417.
Transactions and DDL Statements
SQLFire permits schema and data manipulation statements (DML) within a single transaction. If you create a
table in one transaction, you can also insert data into it in that same transaction. A schema manipulation statement
(DDL) is not automatically committed when it is performed, but participates in the transaction within which it
is issued.
Although the table itself becomes visible in the system immediately, it acquires exclusive locks on the system
tables and the affected tables on all the members in the cluster, so that any DML operations in other transactions
will block and wait for the table's locks.
For example, if a new index is created on a table in a transaction, then all other transactions that refer to that
table wait for the transaction to commit or roll back. Because of this behavior, as a best practice you should keep
transactions that involve DDL statements short (preferably in a single transaction by itself).
Handling Member Failures
These events occur in response to the failure of a single member during a transaction.
1. If the coordinator fails before a commit is fired, then each of the cohorts aborts the ongoing transaction.
2. If a participating member fails before commit is fired, then it is simply ignored. If the copies/replicas go to
zero for certain keys, then any subsequent update operations on those keys throws an exception as in the case
of non-transactional updates. If a commit is fired in this state, then the whole transaction is aborted.
3. If the coordinator fails before completing the commit process (with or without sending the commit message
to all cohorts), the surviving cohorts determine the outcome of the transaction.
If all of the cohorts are in the PREPARED state and successfully apply changes to the cache without any
unique constraint violations, the transaction is committed on all cohorts. Otherwise, if any member reports
139
Using Distributed Transactions in Your Applications