Re: Optimistic locking with multiple rows

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
thewording). 
>
> 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
are90 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
queryis used to update that one row. At the time of the update, the current contents of that row is reread FOR UPDATE
andcompared 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
hasmade or leave in place the changes made by the other user. In either case, that row in the original resultset has to
bemade 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