Mike Matrigali wrote:
From the store point of view, 3 things can happen to
RowLocations in heap tables:
1) It can be deleted (requires at least TABLE IX, ROW X locking)
o internal to store it is just marked deleted
o external to store requests for any operation on
this row will fail. Note that your cursor can
experience this no matter what locking you do, as
it is always possible for another statement in your
transaction to do the delete.
2) It can be purged (requires at least TABLE IX, ROW X locking)
o all physical evidence of the row is removed from table,
both internal and external operations on this row will
fail. Only committed deleted rows are purged.
Note this will never happen if you have some
sort of lock on the row as the requested X lock is
always requested in a system only transaction.
o the actual RowLocation will not be reused while
at least some sort of table level intent lock is held.
3) It can be reused (requires a table level X lock)
o basically as part of a compress all rows can be shuffled
in any way. A former RowLocation can now point to
a completely different row.
So as you point out, your implementation can have serious problems
with cursors held over commit. This is why in current usage of
cursors over commit the only safe thing to do is to ask for the
next row location and use it.
Please make sure to consider the delete/purge cases also. One case
that often causes problems is a transaction deleting a row that is
locked by it's own cursor from another statement in the same connection.
Yes, we need to consider those cases.
It seems that the store is capable of graciously handle that the row get
deleted (i.e by its own transaction). If the transaction later tries to
update the deleted row using the resultset, the store call will return
false indicating that the row was not updated. The deleted row will not
be purged as long as the transaction is open.
However in read-committed/read-uncommitted mode, a row read by the
cursor, can be deleted by another transaction, and then purged.
It seems that the store does not handle an update of a deleted+purged
record.
On our prototype impl., I get a get a NullPointerException from the
store in this case. It comes in GenericConglomerateController.replace(..)).
I would think there are multiple ways of adressing this issue:
1 We could make the store graciously handle the situation if the
RowLocation points to a deleted+purged row, by returning false if the
RowLocation is invalid, (and from the caller we can give an exception)
2 Or we could make all scrollable updatable resultsets set read-locks or
updatelocks on every row, for all isolation levels (including
read-uncommitted)
3 Or we could make purging require a table level X lock, instead of row
locks
Below is output from the test:
T1: Read next Tuple:(0,0,17)
T1: Read next Tuple:(1,1,19)
T1: Read next Tuple:(2,2,21)
T1: Read next Tuple:(3,3,23)
T1: Read next Tuple:(4,4,25)
T1: Read next Tuple:(5,5,27)
T1: Read next Tuple:(6,6,29)
T1: Read next Tuple:(7,7,31)
T1: Read next Tuple:(8,8,33)
T1: Read next Tuple:(9,9,35)
T2: Deleted Tuple:(0,0,17)
T2: commit
T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE
T3: purged deleted records
T3: commit
T1: Read first Tuple:(0,0,17)
T1: updateInt(2, 3);
T1: updateRow()
java.lang.NullPointerException
at
org.apache.derby.impl.store.access.conglomerate.GenericConglomerateController.replace(GenericConglomerateController.java:465)
at
org.apache.derby.impl.sql.execute.RowChangerImpl.updateRow(RowChangerImpl.java:516)
at
org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:577)
at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:276)
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:368)
at
org.apache.derby.impl.jdbc.EmbedResultSet.updateRow(EmbedResultSet.java:3256)
at
resultsettests.ConcurrencyTest.testConcurrency7(ConcurrencyTest.java:306)
-- Andreas
