Data conflicts when procedures read from or write to tables

To preserve the integrity of the database, it is necessary to avoid conflicts when reading from and writing to tables.

For example, suppose an application is updating the EMPLOYEE table, and the statement calls a routine. Suppose that the routine tries to read the EMPLOYEE table and encounters the row being updated by the application. The row is in an indeterminate state from the perspective of the routine- perhaps some columns of the row have been updated while other have not. If the routine acts on this partially updated row, it can take incorrect actions. To avoid this sort of problem, the database manager do not allow operations that conflict on any table.

To describe how the database manager avoid conflicts when reading from and writing to tables from routines, the following two terms are needed:
top-level statement
A top-level statement is any SQL statement issued from an application, or from a stored procedure that was invoked as a top-level statement. If a procedure is invoked within a dynamic compound statement or a trigger, the compound statement or the statement that causes the firing of the trigger is the top-level statement. If an SQL function or an SQL method contains a nested CALL statement, the statement invoking the function or the method is the top-level statement.
table access context
A table access context refers to the scope where conflicting operations on a table are allowed. A table access context is created whenever:
  • A top-level statement issues an SQL statement.
  • A UDF or method is invoked.
  • A procedure is invoked from a trigger, a dynamic compound statement, an SQL function or an SQL method.
For example, when an application calls a stored procedure, the CALL is a top-level statement and therefore gets a table access context. If the stored procedure does an UPDATE, the UPDATE is also a top-level statement (because the stored procedure was invoked as a top-level statement) and therefore gets a table access context. If the UPDATE invokes a UDF, the UDF gets a separate table access context and SQL statements inside the UDF are not top-level statements.

Once a table has been accessed for reading or writing, it is protected from conflicts within the top-level statement that made the access. The table can be read or written from a different top-level statement or from a routine invoked from a different top-level statement.

The following rules are applied:
  1. Within a table access context, a given table can be both read from and written to without causing a conflict.
  2. If a table is being read within a table access context then other contexts can also read the table. If any other context attempts to write to the table, however, a conflict occurs.
  3. If a table is being written within a table access context, then no other context can read or write to the table without causing a conflict.
If a conflict occurs, an error (SQLCODE -746, SQLSTATE 57053) is returned to the statement that caused the conflict.

The following is an example of table read and write conflicts:

Suppose an application issues the statement: 
	UPDATE t1 SET c1 = udf1(c2)
UDF1 contains the statements: 
	DECLARE cur1 CURSOR FOR SELECT c1, c2 FROM t1
	OPEN cur1
This will result in a conflict because rule 3 is violated. This form of conflict can only be resolved by redesigning the application or UDF.

The following does not result in a conflict:

Suppose an application issues the statements: 
	DECLARE cur2 CURSOR FOR SELECT udf2(c1) FROM t2
	OPEN cur2
	FETCH cur2 INTO :hv
	UPDATE t2 SET c2 = 5
UDF2 contains the statements: 
	DECLARE cur3 CURSOR FOR SELECT c1, c2 FROM t2
	OPEN cur3
	FETCH cur3 INTO :hv
With the cursor, UDF2 is allowed to read table T2 because two table access contexts can read the same table. The application is allowed to update T2 even though UDF2 is reading the table because UDF2 was invoked in a different application level statement than the update.