Thread: Newbie postgres/JDBC question.

Newbie postgres/JDBC question.

From
"dlangschied"
Date:
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?

Sincerely,

David Langschied
Langschied Consulting Services
25644 Mackinac
Roseville, MI 48066

Phone:  (586)777-7542
Cell:      (248)789-8493
e-mail:  dlangschied@ameritech.net


Re: Newbie postgres/JDBC question.

From
Dave Cramer
Date:
Select for update is the command you are looking for.

dave
On Thu, 2003-05-29 at 08: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?
>
> Sincerely,
>
> David Langschied
> Langschied Consulting Services
> 25644 Mackinac
> Roseville, MI 48066
>
> Phone:  (586)777-7542
> Cell:      (248)789-8493
> e-mail:  dlangschied@ameritech.net
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Dave Cramer <Dave@micro-automation.net>


Re: Newbie postgres/JDBC question.

From
Barry Lind
Date:
David,

The sql clause FOR UPDATE is what you are looking for.  As in SELECT FOO
FROM BAR FOR UPDATE (see the SELECT syntax in the doc).  This will lock
the selected row(s) and prevent others from changing them.

Other users will not see any errors, they will just wait until the
lock(s) are released before they can continue.

thanks,
--Barry


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?
>
> Sincerely,
>
> David Langschied
> Langschied Consulting Services
> 25644 Mackinac
> Roseville, MI 48066
>
> Phone:  (586)777-7542
> Cell:      (248)789-8493
> e-mail:  dlangschied@ameritech.net
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>




Re: Newbie postgres/JDBC question.

From
Paul Thomas
Date:
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   |
+------------------------------+---------------------------------------------+