ASA 9.0.2 EBF 3270:

rdme_902_ebf_3323.html
================(Build #3270 - Engineering Case #422513)================

The server now supports the READPAST table hint. An example:

Select *
From customer with( readpast )
Where state = 'NY'

READPAST is meaningful only at isolation level 1; it is ignored at all other
isolation levels. As with other hints, the READPAST hint is respected only
when the correlation name in the FROM clause refers to a base or global shared
temporary table. In other situations (views, proxy tables, table functions)
the READPAST hint is ignored. Queries within views may utilize READPAST as
long as the hint is specified for a correlation name that is a base table.

The READPAST hint directs the server to ignore locked rows (that is, rows
write-locked by other connections) rather than blocking. One way to think
about READPAST is to pretend that addtiional predicate has been added to
the query's WHERE clause; with the example above, akin to

Select *
From customer with( readpast )
Where state = 'NY' and (current row is not write locked by another transaction).

Consequently the READPAST hint is inconsistent with the behaviour of ANSI
SQL isolation level 1, as uncommitted changes (locked rows) have an impact
on the result set of a statement utilizing READPAST. As such, the use of
READPAST can lead to anomalies due to the interaction of locking and predicate
evaluation within the server. Subtle differences in this interaction from
release to release of SQL Anywhere may yield different behaviours. The precise
affect of READPAST may also depend on the optimization strategy determined
by the query optimizer, particularly if the hint is specified only on a subset
of the tables in the query. For example, consider the outer join query

SELECT * FROM R LEFT OUTER JOIN S (WITH READPAST) ON 1=0

In 9.0.2, a cursor stability lock is placed on the rows of R and S once
the join is performed (assume nested loop join, JNLO, in this example). Further,
assume that we find a single candidate S row to match the current R row,
but that it is dirty (the row is write-locked by another transaction). The
lock node will reject it, causing the server to fetch another row from the
JNLO. Since the JNLO thinks it has found a match, it will not generate a
null-supplied copy of the R row, and the server will fail to preserve the
R row in the result. Hence, in this case, the query's result will fail to
include all the rows of R, which it would if READPAST was not used.

READPAST can be used with all cursor types. However, KEYSET cursors must
be used with caution. When initially populating the keyset through forward
FETCH statements, READPAST will ignore locked rows as described above. However,
a subsequent refetch of a previously FETCH 'd row may block if that underlying
row was write-locked by another connection after the row was originally added
to the keyset. The blocking can be avoided through the use of the BLOCKING
option, but after receiving the ROW_LOCKED error (SQLCODE -210) the cursor
will be in an invalid state.

READPAST can be useful when implementing a queue with a table, and there
are multiple producers and consumers of the queue. Using READPAST can significantly
improve concurrency in this scenario.