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

From: "Anand Raman" <araman(at)india-today(dot)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