Re: Select for update, cursors

[Craig L Russell]

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 applications. 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. Craig 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" (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT) 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:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature