db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andreas Korneliussen <Andreas.Kornelius...@Sun.COM>
Subject Re: [jira] Commented: (DERBY-690) Add scrollable, updatable, insensitive result sets
Date Thu, 23 Feb 2006 19:19:21 GMT
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.
>
>  
>


Mime
View raw message