Thread: Newbie postgres/JDBC question.
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
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>
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 >
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 |
+------------------------------+---------------------------------------------+