Re: [araman@india-today.com: locking problem with JDBC (suspicion)]

HI richard
Thanks for your response..

No this is not a example.. These are the ACTUAL SERVER LOG which got
generated in my postgres log file on a debug level 2 (-d2)

I will also try the 2 session thing which u suggested and get back..

Thanks
Anand

On Tue, Feb 13, 2001 at 11:54:04AM -0000, Richard Huxton wrote:
>From: "Anand Raman" <araman@india-today.com>
>
>
>> I am facing problems with locks occasionally when using postgresql with
>> jdbc drivers..
>>
>> I typically update a table exhibits which has 2 foreign keys from
>> artits table and a foreign key from atoday_users.. Sometimes this query
>simply hangs waiting for some
>> lock to be released.
>
>Might be the foreign keys.
>
>> This is a sample log entry which is generated when the process runs
>> smoothly..
>>
>> StartTransactionCommand
>> query: insert into
>exhibits(exhibit_id,created_by,title,description,artist_id1,artist_id2)
>values (611,1001,'trial sake',null,157,null)
>> ProcessQuery
>> query: SELECT oid FROM "artists" WHERE "artist_id" = $1 FOR UPDATE OF
>"artists"
>> query: SELECT oid FROM "atoday_users" WHERE "user_id" = $1 FOR UPDATE OF
>"atoday_users"
>> CommitTransactionCommand
>
>SELECT ... FOR UPDATE will obviously lock the relevant records here until
>the end of transaction, but you don't appear to be updating the records. I'm
>guessing this is just an example.
>
>> this is the log entry which is generated when the process hangs..
>> StartTransactionCommand
>> query: insert into
>exhibits(exhibit_id,created_by,title,description,artist_id1,artist_id2)
>values (613,1001,'Painiting 1',null,56,null)
>> ProcessQuery
>> <<HANGS AFTER THIS>>
>
>Assuming you have described everything that's going on, it must be something
>to do with either the foreign keys or the jdbc driver.
>> ** MORE IMPORTANTG QUESTION **
>> Also why should insert into exhibits table lock a few rows from artists
>> and atoday_user for UPDATE.. Could this be the cause of the problem..
>
>Well, foreign keys enforce constraints so it's not unreasonable that the
>lock might extend to the referenced tables.
>
>Could you try it from the command-line? Open up one psql session, begin a
>transaction with the artists update and then try the exhibits update in a
>second session - it should hang until the first transaction completes/rolls
>back or a timeout occurs.
>
>If this reproduces your problem, we'll know where we are. If it doesn't I'd
>guess it's in the JDBC drivers.
>
>HTH
>
>- Richard Huxton
>