A transaction is a unit of work that is done as a single operation. There are two base characteristics of transaction:

• Transaction mode
• Transaction Isolation Level

Transaction mode

Transactions can be completed by either being committed or being rolled back. When a transaction is committed, the changes made in that transaction are made permanent. When a transaction is rolled back, the affected rows are returned to the state they were in before the transaction was started.

Transactions can be in one of two modes: auto-commit mode or manual-commit mode.

In auto-commit mode, every database operation is a transaction that is committed when performed. In databases without transaction support, auto-commit mode is the only supported mode. In such databases, statements are committed when they are executed and there is no way to roll them back; they are therefore always in auto-commit mode.

In manual-commit mode, applications must explicitly complete transactions by calling SAConnection::Commit to commit them or SAConnection::Rollback to roll them back. This is the normal transaction mode for most relational databases.

Using SQLAPI++ to set auto-commit mode compulsory you should call SAConnection::setAutoCommit method with parameter value SA_AutoCommitOn. To set manual-commit mode you should call SAConnection::setAutoCommit method with parameter value SA_AutoCommitOff. If you didn't set transaction mode explicitly then the library uses the default DBMS transaction settings. See DBMS documentation to get information concerning the default transaction mode.

If you disconnected from server without commit, then the result of transaction depends on underlying DBMS. It can either being committed or being rolled back. See DBMS documentation.

Calling SAConnection::Commit or SAConnection::Rollback in auto-commit is safe (it has no effect). If you are working in manual-commit mode and call SAConnection::Commit method then current transaction is committed and new one is started. If you are working in manual-commit mode and call SAConnection::Rollback method then current transaction is rolled back and new one is started.

Transaction Isolation Level

Transaction isolation refers to the degree of interaction between multiple concurrent transactions. SQL-92 defines four isolation levels, all of which are supported by SQLAPI++:

• Read uncommitted (the lowest level where transactions are isolated just enough to ensure that physically corrupt data is not read)
• Read committed
• Repeatable read
• Serializable (the highest level, where transactions are completely isolated from one another)

SQLAPI++ maps different isolation levels on underlying DBMS. You can set transaction isolation level explicitly by calling SAConnection::setIsolationLevel.

If you didn't set transaction isolation level explicitly then the library uses the default DBMS transaction settings. See DBMS documentation to get information concerning the default transaction isolation level.