VMware.Data.SQLFire.SQLFTransaction

VMware.Data.SQLFire.SQLFTransaction implements the System.Data.IDbTransaction interface, which controls distributed transactions for a SQLFire system.

This class also extends the abstract System.Data.Common.DbTransaction class, so applications requiring generic coding can make use of the base class for transactional operations.

A SQLFTransaction object cannot be explicitly created. To begin a new transaction in SQLFire, use the BeginTransaction methods of the SQLFClientConnection class. It is also possible to control transactions directly using the SQLFClientConnection using the BeginSQLFTransaction, Commit and Rollback methods without creating a transaction object for the same. Also refer to AutoCommit, IsolationLevel properties of the SQLFClientConnection class for additional properties related to transactions.

The isolation level for the transaction is set by passing the required System.Data.IsolationLevel to the BeginTransaction/BeginSQLFTransaction methods. The supported isolation levels are ReadUncommitted, ReadCommitted, and RepeatableRead.

Internally, any transaction that uses the ReadUncommitted level is implicitly upgraded to ReadCommitted. When nothing is specified, the transaction uses the ReadCommitted isolation level, which is what will be returned as the result of the IsolationLevel property (rather than returning Unspecified). In addition the special IsolationLevel.Chaos denotes an absence of any transaction (Connection.TRANSACTION_NONE in JDBC). However, the underlying GemFire store still offers a certain amount of data consistency and atomicity as given in the section Understanding the data consistency model. Using Distributed Transactions in Your Applications provides an overview of the distributed transaction implementation offered in SQLFire and the semantics for the same.

When a SQLFire transaction throws a SQLFException having a severity of Transaction or greater, the transaction is implicitly rolled back on the server and there is no need to explicitly roll it back. Conflicts between transactions (where the same row is being updated by two transactions) and constraint violations cause the transaction to roll back. The latter case is a limitation caused by the distributed nature of a SQLFire system.

The MSDN documentation for IDbTransaction and DbTransaction provides more details about the API methods and usage.

Example

// Open a new connection to the network server running on localhost:1527
string host = "localhost";
int port = 1527;
string connectionStr = string.Format("server={0}:{1}", host, port);
using (SQLFClientConnection conn = new SQLFClientConnection(connectionStr)) {
  conn.Open();

  // create a table
  SQLFCommand cmd = new SQLFCommand(
      "create table t1 (id int primary key, addr varchar(20))", conn);
  cmd.ExecuteNonQuery();

  SQLFTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
  cmd.Transaction = tran;
  // insert into the table using positional parameters
  cmd = new SQLFCommand("insert into t1 (id, addr) values (?, ?)", conn);
  cmd.Prepare();
  for (int i = 0; i < 1000; i++) {
    cmd.Parameters.Clear();
    cmd.Parameters.Add(i);
    cmd.Parameters.Add("addr" + i);

    cmd.ExecuteNonQuery();
  }
  tran.Commit();

  // fire some updates and if any unsuccessful then rollback the transaction
  cmd.CommandText = "update t1 set addr = ? where id = ?";
  tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
  cmd.Transaction = tran;
  bool success = true;
  for (int i = 100; i < 200; i++) {
    cmd.Parameters.Clear();
    cmd.Parameters.Add("address" + i);
    cmd.Parameters.Add(i);
    if (cmd.ExecuteNonQuery() != 1) {
      // update failed; rolling back the entire transaction
      success = false;
      tran.Rollback();
      break;
    }
  }
  if (success) {
    // all succeeded; commit the transaction
    tran.Commit();
  }

  // drop the table
  cmd = new SQLFCommand("drop table t1", conn);
  cmd.ExecuteNonQuery();

  conn.Close();
}