Re: Optimistic locking with multiple rows

On Sun, 10 Jan 2010 16:01:57 +0800, Craig Ringer wrote:

>On 6/01/2010 10:53 PM, John T. Dow wrote:
>> I posted this several days ago to pgsql-jdbc but have had no response. I am posting it here (with minor changes in the wording).
>>
>> I have developed some code that works, I'm just not sure I have the "best" solution.
>>
>> I have applications in which the user can create a read-only resultset with multiple rows. For example, customers who are 90 days in arrears might be brought up for review.
>>
>> The user might scroll through the rows reviewing the data, and then he might decide to update one of them. A second query is used to update that one row. At the time of the update, the current contents of that row is reread FOR UPDATE and compared against the original row. If they differ, someone else has altered the row after the resultset was created.
>>
>> The user is informed that another user has changed the row in question; he can then decide to accept the changes he has made or leave in place the changes made by the other user. In either case, that row in the original resultset has to be made to match the current contents in the table, because the user might scroll back and forth and revisit it.
>>
>> I am using refreshRow() to make that row current, but the problem is that refreshRow() can be extremely slow.
>>
>> I create the read-only, multiple row resultset (ie "viewResultSet") like this:
>>
>> createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
>> viewResultSet = jdbcStmt.executeQuery("SELECT ... FROM ... WHERE select multiple rows");
>>
>> Scroll through the resultset to view rows as desired.
>>
>> When positioned at a row, can update that row. See below.
>
>Can you instead create an explicit, named updatable cursor? Then FETCH
>from it, and UPDATE ... WHERE CURRENT OF it? That way you won't have to
>do all this kludging with refreshing rowsets.
>
>--
>Craig Ringer

I have not used cursors, so you've inspired me to rtfm about them. So far I don't see how to implement optimistic locking with cursors.

The postgres documentation says this: "Without FOR UPDATE, a subsequent WHERE CURRENT OF command will have no effect if the row was changed since the cursor was created."

I read that to mean that the cursor has to be declared FOR UPDATE, which means that the rows are locked and I don't have optimistic locking.

John