Transaction Principles

---

A transaction is an atomic operation which may not be divided into smaller operations.

Three transaction phases exist: build-up, during which the database operations are requested; prepare, during which the transaction is validated; commitment, during which the operations performed in the transaction are written to disk.

Read-only transactions have only two phases: build-up and prepare.

Transaction build-up may be started explicitly or implicitly, see Starting Transactions; prepare and commitment are both initiated explicitly through a request to commit the transaction (using COMMIT).

In interactive application programs, build-up takes place typically over a time period determined by the user, while prepare and commitment are part of the internal process of committing a transaction, which occurs on a time-scale determined by machine operations.

Optimistic Concurrency Control

Since Mimer SQL uses optimistic concurrency control (OCC), deadlocks never occur, see Locking for a further discussion of deadlocks. How optimistic concurrency control works in Mimer SQL is described below.

The transaction begins by taking a snapshot of the database in a consistent state. During build-up, changes requested to the contents of the database are kept in a write-set and are not visible to other users of the system. This allows the database to remain fully accessible to all users. The application program in which build-up occurs will see the database as though the changes had already been applied. Changes requested during transaction build-up become visible to other users when the transaction is successfully committed.

During build-up, a read-set records the state of the database as seen at the time of each operation (including intended changes). If the state of the database at commitment is inconsistent with the read-set, a conflict is reported and the transaction is rolled back (i.e. the write-set is erased and no changes are made to the database). This can happen if, for instance, a transaction updates a row which gets deleted by another user after build-up has started but before the transaction is committed. The application program is responsible for taking appropriate action if a transaction conflict occurs.

Concurrency Control Guidelines

Because of the nature of this concurrency control protocol, it is important that some of the implications are understood.

A transaction that exists for a long elapsed time has a greater chance of conflicting with changes made by other users than a transaction with a short elapsed time.

At the other extreme, an application that immediately commits every executed SQL statement will seldom meet any conflicts, but will incur unnecessary overhead.

In general:

• keep transactions as short as is reasonably possible
• keep interactive user dialogs outside of transactions

A common situation that can generate unnecessarily large read-sets is the following: an application program reads through the rows in a table in a loop construct, with a conditional exit to update a row on user intervention.

It is tempting to simply place a COMMIT after the update statement, for example:

 EXEC SQL DECLARE c_1 CURSOR FOR SELECT...
 loop
     EXEC SQL FETCH c_1
         INTO :VAR1, :VAR2, ..., :VARn;
     display VAR1, VAR2, ..., VARn;
     prompt "Update row?";
     exit when ANSWER = "YES";
 end loop;
 EXEC SQL UPDATE table
          SET ...
          WHERE CURRENT OF c_1;
 EXEC SQL COMMIT;
 

However, the FETCH loop can create a large read-set while waiting for the user update request, risking transaction conflict at the UPDATE.

A tempting solution for this problem might be:

 EXEC SQL DECLARE c_1 CURSOR FOR SELECT...
 loop
     EXEC SQL FETCH c_1
         INTO :VAR1, :VAR2, ..., :VARn;
     display VAR1, VAR2, ..., VARn;
     prompt "Update row?";
     exit when ANSWER = "YES";
     EXEC SQL ROLLBACK;
 end loop;
 EXEC SQL UPDATE table
          SET ...
          WHERE CURRENT OF c_1;
 EXEC SQL COMMIT;
 

But since ROLLBACK closes all cursors, this will not work.

Instead, something like the following is a better approach:

 EXEC SQL DECLARE c_1 CURSOR FOR SELECT...
 EXEC SQL SET TRANSACTION READ ONLY;
 loop
     EXEC SQL FETCH c_1
         INTO :VAR1, :VAR2, ..., :VARn;
     display VAR1, VAR2, ..., VARn;
     prompt "Update row?";
     exit when ANSWER = "YES";
 end loop;
 EXEC SQL ROLLBACK;
 EXEC SQL UPDATE table
          SET ...
          WHERE col1 = :VAR1,
                col2 = :VAR2, ...
 EXEC SQL COMMIT;
 

The risk of a transaction conflict in the final transaction is minimal, because both the size and duration of the transaction is minimized. The use of a read-only transaction can significantly improve the performance of the FETCH statements.

A number of changes are necessary if we want to loop over FETCH, UPDATE and COMMIT.

 EXEC SQL DECLARE c_1 CURSOR WITH HOLD FOR SELECT...
 loop
     EXEC SQL FETCH c_1
         INTO :VAR1, :VAR2, ..., :VARn;
     display VAR1, VAR2, ..., VARn;
     prompt "Update row?";
     if  ANSWER = "YES" then
         EXEC SQL COMMIT;
         EXEC SQL UPDATE table
                  SET ...
                  WHERE col1 = :VAR1,
                        col2 = :VAR2, ...
         EXEC SQL COMMIT;
     end if;
 end loop;
 

The cursor is declared WITH HOLD in order to remain open and positioned after COMMIT.

COMMIT is used instead of ROLLBACK, since holdable cursors does not remain open after ROLLBACK.

The SET TRANSACTION statement is removed, because the existence of an open holdable cursor prohibits a change of transaction mode. The cursor cannot be accessed both in READ ONLY and in READ WRITE mode.

Locking

Deadlock situations, which can be relatively common in some database management systems where records are locked during transaction build-up, can not occur in Mimer SQL.

In Mimer SQL it is impossible for two processes to be waiting for a record locked by the other process. In some other database management systems this situation may require operator intervention to resolve the problem.

In any database system, at some stage in a transaction, the data records must be locked to prevent access by other processes and to ensure that the transaction is not interrupted. In the Mimer SQL system, no change is made to the database contents during the transaction build-up and no records are locked. This means that the database can be freely accessed (and updated) by any other process; the data accessed by the transaction is only locked during the commit phase. In this way, locks are held only for a very short period of time.

The problems associated with locking are further reduced since only those records that are actually to be updated are locked. Other data in the same table continues to be accessible to other transactions.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se