General read cursor under write conflicts from another statement handle
An application can have multiple statement handles doing read and write operations on the same table at the same time. Conflicts occur when one handle is performing a write operation on the table (for example, UPDATE, DELETE, or INSERT) while another handle is in the middle of a read or write operation. The DB2 Everyplace behavior is that the read cursor is stable and always reading the most current data. It survives the write conflicts, regardless of whether it is using an index or not. For example, suppose an application has two statement handles. Handle #1 is used to fetch rows from a table T whereas handle #2 is used to delete some rows from the same table. It is likely that each handle may have been created by different threads (for example, in a Java thread environment).
Here is a possible scenario:
// Fetch 2 rows from table T
Statement handle 1: execute "SELECT A FROM T WHERE primary_key < 10"
Statement handle 1: fetch one row; fetch another row
// Delete some rows in table T
Statement handle 2: prepare "DELETE FROM T WHERE primary_key = ?"
Statement handle 2: execute
// Continue to fetch one more row from T
Statement handle 1: fetch one row
At this point in the execution, the statement handle #1 will be able to continue fetching the next row (if any), regardless of whether an index is used. In the scenario above, an index is used because there is a primary key. The idea is that DB2 Everyplace will try to reposition the cursor position of handle #1, using its current position, before advancing. If the current position does not exist anymore (for example, the row was deleted by another statement handle), then the cursor simply advances to the next position upon fetching. Likewise, if the next position was deleted by another statement handle, the cursor can skip over the "hole" to the following position.
Scrollable cursor under write conflicts from another statement handle
Consider an example similar to the one in the previous section, but in which the read cursor is a scrollable cursor. If it is an "insensitive" scrollable cursor, this is not an issue because the result set does not change by definition. If the cursor is not "insensitive", the behavior matches a regular read cursor described above. Essentially, the read cursor behavior after the conflict is that the result set is recomputed according to the most current table data, and the start of the current row set is maintained. The cursor is advanced to the next row if the current row is deleted.
The following example illustrates the case with a scrollable cursor using CLP. Suppose table T has six rows:
create table T (a int, b int)
create index idx1 on T(a)
insert into T values (1, 1)
insert into T values (2, 2)
insert into T values (3, 1)
insert into T values (3, 2)
insert into T values (3, 3)
insert into T values (4, 4)
Without loss of generosity, consider an example where the application has two statement handles, one for read and the other for delete.
Statement handle 1: enable scrollable cursor; Statement handle 1: execute "SELECT A FROM T WHERE a < 10" Statement handle 2: prepare "DELETE FROM T WHERE a = ?" Statement handle 1: fetchscroll with SQL_FETCH_FIRST -- get (1, 1) Statement handle 1: fetchscroll with SQL_FETCH_NEXT -- get (2, 2) Statement handle 1: fetchscroll with SQL_FETCH_NEXT -- get (3, 1) Statement handle 2: execute --- suppose delete row (2, 2) Statement handle 1: fetchscroll with SQL_FETCH_NEXT -- re-compute previous rows, and return (3, 2) Statement handle 1: fetchscroll with SQL_FETCH_PRIOR -- get (3, 1) Statement handle 1: fetchscroll with SQL_FETCH_PRIOR -- get (1, 1) note that (2, 2) is gone Statement handle 1: fetchscroll with SQL_FETCH_ABSOLUTE, offset 2 -- get (3, 1) note that (2, 2) is gone Statement handle 1: fetchscroll with SQL_FETCH_ABSOLUTE, offset 5 -- get (4, 4)
Cursor under commit and rollback, including autocommit mode
Regardless of transaction or autocommit mode, an open cursor remains open across commit, and an open cursor is closed upon rollback.
Object dependency
Preparing an SQL statement via a statement handle H may put some dependency on certain objects. For example, selecting rows from a table T via an index Idx requires the existence of the table T and the index Idx. If these objects were deleted by another statement handle (for example, if the index Idx was dropped), re-executing the statement through H will force a re-compilation of the SQL statement. As a result, the query plan may be different or an error may be returned.