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.
- 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.
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.
- Within a table access context, a given table can be both read from and written to without causing a conflict.
- 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.
- 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.
The following is an example of table read and write conflicts:
UDF1 contains the statements:
UPDATE t1 SET c1 = udf1(c2)
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.
DECLARE cur1 CURSOR FOR SELECT c1, c2 FROM t1 OPEN cur1
The following does not result in a conflict:
UDF2 contains the statements:
DECLARE cur2 CURSOR FOR SELECT udf2(c1) FROM t2 OPEN cur2 FETCH cur2 INTO :hv UPDATE t2 SET c2 = 5
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.
DECLARE cur3 CURSOR FOR SELECT c1, c2 FROM t2 OPEN cur3 FETCH cur3 INTO :hv