db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: Select for update, cursors
Date Wed, 28 Dec 2005 19:47:40 GMT
Hi Danny,

In my experience, your proposed implementation strategy generally  
won't scale past a few users. The reason is that you will have locks  
held during user think time, which is to be avoided for scalable  

Alternative things to consider:

1. When the first user clicks on a transaction to edit, update the  
database immediately with the user id and date/time. This lets the  
second user know that someone else has reserved the right to update.  
This technique works well for infrequent updates (very commonly used  
for wikis etc.). You can embellish this technique with soft timeouts  
(user 1 can still commit changes even after the timeout unless  
another user has acquired the soft lock).

2. Allow as many users to access and update the transaction as you  
like, but only allow one to succeed. There are several techniques  
here, such as incrementing a number in the row on each update, and  
only allowing an update to occur if the user presents the correct  
update number. Most object-relational mappers use this optimistic  
locking strategy.

The reason for updatable result sets is efficiency and ease of  
programming where there are a large number of things to update in the  
same result set. Instead of having to manage two different things  
(the original result set and the update set) you just manage one  
updatable result set. But IMHO it's not suitable for the scenario you  
are talking about with user think time involved.


On Dec 28, 2005, at 8:22 AM, Danny wrote:

> We are moving from a local database application (1 user to 1  
> database) to an
> installation that will support a single database on a network (many  
> users on
> 1 database).
> In looking into select for update, cursors, locking, etc.  I have a  
> few
> questions that maybe someone could help me with.
> Let me preface this with the fact that I am in no way a database  
> programming
> expert.
> Here is the scenario within the system:
> 1. A user chooses a transaction from a list that they want to edit.
> 2. User clicks edit and all the details of the transaction are  
> displayed in
> the gui, the user has the option of editing any of these details.
> 3. User clicks submit and the transaction is updated in the database.
> Here is where I have gotten so far:
> 1. When the user clicks a transaction from the list and clicks  
> edit, that
> transaction is retrieved from the database using a select for  
> update, which
> returns a ResultSet with the following settings"
> 2. Second user clicks the same transaction in the list and clicks  
> edit, that
> transaction is retrieved using the same query.
> 3. First user changes details in transaction and submits, database  
> update
> occurs through the ResultSet produced from the select from update.
> 4. Second user changes details in transaction, (not the new details  
> created
> by user 1, but the original details) and submits, database update  
> occurs
> through the ResultSet produced from the select from update..
> The behavior that I want is:
> - User 2 to generate some type of exception that I can catch:
> 	- Either
> 	At the time that they select the transaction in the list that User 1
> is already editing.
> 	OR
> 	At the time that User 2 submits the changes when User 1 performed an
> update in the time since User 2 clicked edit to view the details of  
> 	the
> transaction.
> It seems that what I want is a ResultSet.TYPE_SCROLL_SENSITIVE  
> which (from
> what I can gather) derby does not support, although I am not sure  
> if this
> would solve my problem.
> Anyone run into this sort of thing? Any suggestions? I have found some
> commentary that the time honored tradition of comparing the last  
> update
> timestamp column is really the best way to go about it, but then  
> why have
> updatable ResultSets at all?
> Thanks
> Danny Gallagher
> The Gainer Group
> 6525 The Corners Parkway
> Suite 215
> Norcross Ga, 30092

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!

View raw message