Transactions

SQL Anywhere expects you to group your statements into transactions. You commit a transaction to make changes to your database permanent. When you alter data, your alterations are recorded in the transaction log and are not made permanent until you enter the COMMIT statement.

Transactions start with one of the following events:

The first statement following a connection to a database.
The first statement following the end of a transaction.

Transactions complete with one of the following events:

A COMMIT statement makes the changes to the database permanent.
A ROLLBACK statement undoes all the changes made by the transaction.
A statement with a side effect of an automatic commit is executed: most data definition statements, such as ALTER, CREATE, COMMENT, and DROP have the side effect of an automatic commit.
A disconnection from a database performs an implicit rollback.
ODBC and JDBC have an autocommit setting that enforces a COMMIT after each statement. By default, ODBC and JDBC require autocommit to be on, and each statement is a single transaction. If you want to take advantage of transaction design possibilities, then you should turn autocommit off.
Setting the chained database option to Off is similar to enforcing an autocommit after each statement. By default, connections that use jConnect or Open Client applications have chained set to Off.

Options in Interactive SQL

Interactive SQL provides you with two options that let you control when and how transactions end:

If you set the auto_commit option to On, Interactive SQL automatically commits your results following every successful statement and automatically performs a ROLLBACK after each failed statement.
The setting of the option commit_on_exit controls what happens to uncommitted changes when you exit Interactive SQL. If this option is set to On (the default), Interactive SQL does a COMMIT; otherwise, it undoes your uncommitted changes with a ROLLBACK statement.

Using a data source in Interactive SQL

By default, ODBC operates in autocommit mode. Even if you have set the auto_commit option to Off in Interactive SQL, the ODBC setting overrides the Interactive SQL settings. You can change ODBC's setting using the SQL_ATTR_AUTOCOMMIT connection attribute. ODBC autocommit is independent of the chained option.

SQL Anywhere also supports Transact-SQL statements, such as BEGIN TRANSACTION, for compatibility with Adaptive Server Enterprise.

Determining when a transaction began

The TransactionStartTime database property returns the time the database was first modified after a COMMIT or ROLLBACK. You can use this property to find the start time of the earliest transaction for all active connections. For example:

BEGIN
  DECLARE connid int;
  DECLARE earliest char(50);
  DECLARE connstart char(50);
  SET connid=next_connection(null);
  SET earliest = NULL;
  lp: LOOP
  IF connid IS NULL THEN LEAVE lp END IF;
    SET connstart = CONNECTION_PROPERTY('TransactionStartTime',connid);
    IF connstart <> '' THEN
      IF earliest IS NULL 
      OR CAST(connstart AS TIMESTAMP) < CAST(earliest AS TIMESTAMP) THEN
        SET earliest = connstart;
      END IF;
    END IF;
    SET connid=next_connection(connid);
  END LOOP;
  SELECT earliest
END

Transactions

Options in Interactive SQL

Using a data source in Interactive SQL

Determining when a transaction began

See also