Transaction processing

With the SQL Anywhere .NET Data Provider, you can use the SATransaction object to group statements together. Each transaction ends with a COMMIT or ROLLBACK, which either makes your changes to the database permanent or cancels all the operations in the transaction. Once the transaction is complete, you must create a new SATransaction object to make further changes. This behavior is different from ODBC and embedded SQL, where a transaction persists after you execute a COMMIT or ROLLBACK until the transaction is closed.

If you do not create a transaction, the SQL Anywhere .NET Data Provider operates in autocommit mode by default. There is an implicit COMMIT after each insert, update, or delete, and once an operation is completed, the change is made to the database. In this case, the changes cannot be rolled back.

For more information about the SATransaction object, see SATransaction class.

Setting the isolation level for transactions

The database isolation level is used by default for transactions. However, you can choose to specify the isolation level for a transaction using the IsolationLevel property when you begin the transaction. The isolation level applies to all statements executed within the transaction. The SQL Anywhere .NET Data Provider supports snapshot isolation.

For more information about isolation levels, see Isolation levels and consistency.

The locks that SQL Anywhere uses when you enter a SELECT statement depend on the transaction's isolation level.

For more information about locking and isolation levels, see Locking during queries.

The following example uses an SATransaction object to issue and then roll back a SQL statement. The transaction uses isolation level 2 (RepeatableRead), which places a write lock on the row being modified so that no other database user can update the row.

To use an SATransaction object to issue a statement

Declare and initialize an SAConnection object.

SAConnection conn = new SAConnection(
    "Data Source=SQL Anywhere 12 Demo" );

Open the connection.
conn.Open();

Issue a SQL statement to change the price of Tee shirts.

string stmt = "UPDATE Products SET UnitPrice =
    2000.00 WHERE name = 'Tee shirt'";

Create an SATransaction object to issue the SQL statement using a Command object.

Using a transaction allows you to specify the isolation level. Isolation level 2 (RepeatableRead ) is used in this example so that another database user cannot update the row.

SATransaction trans = conn.BeginTransaction(
    IsolationLevel.RepeatableRead );
SACommand cmd = new SACommand( stmt, conn,
    trans );
int rows = cmd.ExecuteNonQuery();

Roll back the changes.
trans.Rollback();
The SATransaction object allows you to commit or roll back your changes to the database. If you do not use a transaction, the SQL Anywhere .NET Data Provider operates in autocommit mode and you cannot roll back any changes that you make to the database. If you want to make the changes permanent, you would use the following:
trans.Commit();
Close the SAConnection object.
conn.Close();

Distributed transaction processing

The .NET 2.0 framework introduced a new namespace System.Transactions, which contains classes for writing transactional applications. Client applications can create and participate in distributed transactions with one or multiple participants. Client applications can implicitly create transactions using the TransactionScope class. The connection object can detect the existence of an ambient transaction created by the TransactionScope and automatically enlist. The client applications can also create a CommittableTransaction and call the EnlistTransaction method to enlist. This feature is supported by the SQL Anywhere .NET 2.0 Data Provider. Distributed transaction has significant performance overhead. It is recommended that you use database transactions for non-distributed transactions.

Discuss this page in DocCommentXchange.