Skip site navigation (1) Skip section navigation (2)

Re: How do handle concurrent DML operations

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: kenchen077 <kenchen077(at)yahoo(dot)com>
Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: How do handle concurrent DML operations
Date: 2004-04-20 16:50:57
Message-ID: 1082479857.3069.178.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-jdbc
the driver isn't automagically going to update your records if someone
changes them in the db while you have the result set loaded in your
program.

You need to do one of two things, either use pessimistic locking, ie
select for update, and lock the row, or use optimistic locking and
timestamp the updates so that when you commit your changes then you will
see that the timestamp in your local record is different than the
timestamp in the database and realize your data is stale.

There is another way, but it is not ready yet, we are working on pl/j
which is a java procedural language that would allow you to do the magic
above. ie create a java procedure that you called on a trigger which
would update your local copy of the data if someone else were to change
it

If you are interested and brave, have a look at plj.codehaus.org for the
pl/j code, but beware this is alpha quality code.

Dave
On Thu, 2004-04-15 at 15:23, kenchen077 wrote:
> Hi all,
> 
>   I am building web application by using Oracle data base.
>   Because different uders will manipulate the same data record at the
> same time.
>   Could someone please help me for the following questions:
> 
>   1. How can I handel the concurrently DML (insert, update, delete)
> opertions on same data record   by different users? Can I use
> synchronied method in update, insert, and delete opertionns? Or other
> better method ??
> 
>   2. When user 1 select data from table , user 2 update or delete the
> date in the same table concurrently.
>     Do I need to set transaction isolation level to
> TRANSACTION_READ_COMMITED or refreshRow() to get the updated data?
> 
>     thanks all.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 
> 
> 
> !DSPAM:4085512d14457238818707!
> 
> 
-- 
Dave Cramer
519 939 0336
ICQ # 14675561


In response to

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2004-04-20 16:53:43
Subject: looking for brave souls to test postgres java procedural language
Previous:From: Dave CramerDate: 2004-04-20 16:46:16
Subject: Re: slow seqscan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group