Daniel John Debrunner wrote:
Fernanda Pizzorno (JIRA) wrote:
Read-uncommited isolation level does aquire locks for updatable forward-only
result sets, the same behavior has been kept on scrollable insensitve updatable
result sets.
Hmmm, this means such ResultSets are not running at read-uncommitted
isolation level, they are running at read-committed isolation level.
I understand this is existing behaviour, I wonder if it arises due to
SELECT's FOR UPDATE causing this locking behaviour.
SELECT's FOR UPDATE have this behaviour, even if you do not have
CONCUR_UPDATABLE.
Customers in the past have complained about any locks obtained in
read-uncommitted mode, it might be suprising for them to have a
read-uncommitted ResultSet blocked by other transactions.
I think what the standards say about read-uncommitted mode, is that
phenomenas like dirty read can occur, however as far as I know, even
serializable would be a legal implemenation of read-uncommitted (not the
other way around), since the standards do not require these phenomena to
happen.
It is good to be in line with forward-only (FO), to get a symmetric
system in terms of behaviour, and also a less complicated
implementation. So, what happens is that in read-uncommitted SUR sets
locks in line with read-committed (and FO), however it may of course be
many other queries in the transaction which are not SUR, and they will
not set locks (so the dirty read phenomena can occur even if you use SUR
in your transaction).
Also, I think the JDBC tutorials mention that using updatable
resultsets, may affect the locking behaviur compared to read-only. I.e from
http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/resultset.html :
CONCUR_UPDATABLE
* Indicates a result set that can be updated programmatically
* Available to drivers that implement the JDBC 2.0 core API
* Reduces the level on concurrency. Updatable results sets may use
write-only locks so that only one user at a time has access to a data
item. This eliminates the possibility that two or more users might
change the same data, thus ensuring database consistency. However, the
price for this consistency is a reduced level of concurrency.
(In Derby we use update locks instead of write locks)
I wonder if, at least for scrollable updateable ResultSets, we should
disallow read-uncommitted isolation level, it may be a cleaner way
forward thean implementing them as read-committed and then have concerns
in the future about backwards compatibility if we ever need to implement
them correctly. Maybe the more precise statement is disallow updateRow
and deleteRow when the isolation level is read un-committed. insertRow
seems fine.
I do not think it would be good to disallow updateRow() and deleteRow()
in an updatable resultset.
Forward-only updateable read-uncommitted ResultSets ideally should be
disallowed as well, but that might cause backward compatibility issues.
I would argue that the current behavior is correct.
Andreas
Dan.
