Re: How do handle concurrent DML operations - Mailing list pgsql-jdbc

You should very seriously read and understand the following

http://www.postgresql.org/docs/7.4/interactive/mvcc.html


On Wed, 2004-04-21 at 12:35, kenchen077 wrote:
> if user1 set Resultset rs to ResultSet.TYPE_SCROLL_SENSITIVE and using
> refreshRow() in rs. The rs might show the updated record within this
> rs updated by user2.

If this is inside a transaction and you will not see the other
transactions commits, even if you do a refresh row, see above
documentation.

Dave
>
>    The pessimistic locking seems a good method. How can I do it by
> programming or set up something in DBMS ? Could you please advise me?
> I need to set up row lock to prevent 2 users update same record
> concurrently. If I use synchronized method it may cause deadlock.
>
> Thanks
>
>
>
> pg@fastcrypt.com (Dave Cramer) wrote in message news:<1082479857.3069.178.camel@localhost.localdomain>...
> > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > --
> > Dave Cramer
> > 519 939 0336
> > ICQ # 14675561
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
>
> !DSPAM:408d34f8160166163863926!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561