rowid of a record being locked for updation

From: users jtech softwares <users(dot)jtechsoftware(at)gmail(dot)com>
To: sydpug(at)postgresql(dot)org
Subject: rowid of a record being locked for updation
Date: 2005-05-14 10:08:00
Message-ID: 10748af20505140308204131e9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: sydpug

Hi all,

I have some problems with record locking using 'SELECT FOR UPDATE'.

I tried with two users who are trying to update the same row of a
table using 'SELECT FOR UPDATE'.

Here is the transactions,

user1
-----
begin;
select amount from "icici_bangalore" where name='drew' for update;

user2
-----
begin;
select amount from "icici_bangalore" where name='drew' for update;

Now, user1 has locked the record, but user2 is in waiting to lock the
record. Also, user2 cannot escape from the transaction untill user1
commits. This is creating a situation as if the system has hung.

I am using a java as client interface to access the database. I have
put 4 sets of query to your reference.

user1
-----
update "icici_bangalore" set amount=32000 where name='drew';
commit;

user2
------

user2 now gets the updated data and can now proceed with his transactions.

amount
-------
32000

update "icici_bangalore" set amount=54000 where name='drew';
commit;

My problem is :

How can i inform user2 the details about the row which is locked by user1?

Thanks in advance.

Shameel.

Browse sydpug by date

  From Date Subject
Next Message users jtech softwares 2005-05-14 10:14:56 record id of a recod which is locked for updation
Previous Message Neil Conway 2005-05-13 05:42:36 Re: retrieve the rowid of a row