Re: Newbie postgres/JDBC question.

On 29/05/2003 13:16 dlangschied wrote:
> Hi all!
>
> I have a question that pertains to the update of records in a postgres
> database using JDBC.  I extract a record from the database for update
> and,
> and after modification to the record, I place these changes into
> postgres.
> It does not look, via the documentation on JDBC, as if I have prevented
> someone from making changes at the same time.  Is there a command that I
> must first perform to lock a record that is being updated? If a record is
> locked when another user wants access, what type of error information can
> be
> acquired to pass on to the user?


The whole area of updates can be quite tricky.

Firstly there is the issue of long vs short transactions. In a long
transaction, you would select the data and aquire a lock on the row before
presenting it to the user for editing. This has lots of problems of its
own and doesn't scale well as you would be tying up a connection for a
long period. You would probably find yourself running out of connections!

Secondly, there is the short transaction where locks are only applied
during the actual update. This scales well as a connection is only needed
for tyhe short time it takes to execute the update. The problem is that
another user may have changed your record in between times.

1) the update routine is passed two Transfer/Value Objects (or whatever
Sun is calling them this week). One is the original object read from the
database and the other is a new object, cloned from the original and also
holding any changes made by the user.

2) I start a transaction off with

    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

followed by

    conn.setAutoCommit(false);

3) I now select my data again but this time adding FOR UPDATE to the SQL
statement. I contruct a new object from the Resultset returned and compare
it to my original object. If they differ the I throw an exception.

4) I update the database with the values supplied by the user.

5) commit changes

HTH


--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+