Committing the Transaction

A transaction is opened on the first occurrence of a query statement (select, insert, update or delete) and remains open until you specifically commit it with the commit statement. You can use the following transaction control statements:

Declares a savepoint. You can roll a transaction back to any of its savepoints prior to committing. (For gateways users, the transaction is rolled back to the beginning.)

Changes every query statement to a "Single Query" transaction with an implicit commit occurring after every successful statement

See the SQL Reference Guide for detailed descriptions of these commands and a full description of transactions in SQL.

In your 4GL programs, issue a commit statement after every query statement or group of query statements that form a single transaction. If you do not use explicit commit statements after queries (or begin/end transaction statements around queries) and do not specify set autocommit on, your 4GL/SQL application runs as a single transaction.

Each query issued accumulates as part of the transaction; this transaction does not commit until the user exits the application.

This large transaction can potentially acquire and hold locks on large portions of the database, drastically lowering concurrency and using up a large portion of the server's logging file. Statements within your 4GL code are not the only cause for locking in the database. The first time a frame is called, ABF prepares the form for use. If the form uses a VIFRED validation of the type "field in table.column," ABF issues a database query to retrieve validation data and loads the data into memory.

• If no transaction is open before the frame is called, ABF issues a commit statement, which releases the read locks on the validation data tables.

• However, if a transaction is open when the frame is called, a commit is not issued and the read locks on the validation table remain until your application issues a commit.

When multiple queries are involved in a transaction, both queries must run successfully or neither query runs. If a deadlock occurs, the transaction must rerun.

In the example below, the queries change the value of a field used to join two tables. If an error occurs, the transaction is rolled back and the join fields in both tables retain their original values. If the error is deadlocked, the example retries the transaction. The updates are committed only when both queries run successfully.

The example uses two integer global constants, OK and Fail. OK is defined to a value of 1 (one) and Fail is defined to a value of 0 (zero).

/* The example assumes SQL transaction */
/* semantics: set autocommmit off */
status = ok;
  /* ok/fail = global constants */
commit;
a: while (1=1) do
  b: while (1=1) do
  update orders set order_no = :new_order_no
    where order_no = :old_order_no;
  inquire_sql (errno = errorno,
    rows = rowcount);
  if (errno = 4700) or (errno = 4706) then
  /* deadlock or forced abort limit */
  /* reached; restart */
    endloop b;
  elseif (errno > 0 or rows = 0) then
  /* error: roll back transaction */
    rollback;
    status = fail;
    endloop a;
  endif;

  update order_items set order_no =
    :new_order_no
    where order_no = :old_order_no;
  inquire_sql (errno = errorno,
    rows = rowcount);
  if (errno = 4700) or (errno = 4706) then
  /* deadlock or forced abort limit */
  /* reached; restart */
    endloop b;
  elseif (errno > 0 or rows = 0) then
  /* error: roll back transaction */
    rollback;
    status = fail;
    endloop a;
  endif;

  /* successful transactions pass through */
  /* here */
  commit;
  endloop a;
  endwhile;   /* end of loop b */
  /* deadlocks pass through here; */
  /* outer loop reruns */
endwhile;   /* end of loop a */
if (status = ok) then
  /* success above */
else
  /* error above */
endif;