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

From: "Anand Raman" <araman(at)india-today(dot)com>

> Hi Tom and others
>
> I have been trying to find a pattern to this problem and I have failed
> to find any.. In a recently conducted test i could insert 6 records
> without and problems so far ... However some other time i am able to
> insert just as many as 3 records..
>
> I need one more clarifications..
>
[snipped initial query]
> query: SELECT oid FROM "gallery" WHERE "gallery_id" = $1 FOR UPDATE OF
"gallery"
> query: SELECT oid FROM "exhibit_types" WHERE "exhibit_type_id" = $1 FOR
UPDATE OF "exhibit_types"
> query: SELECT oid FROM "atoday_users" WHERE "user_id" = $1 FOR UPDATE OF
"atoday_users"
> query: SELECT oid FROM "exhibits" WHERE "exhibit_id" = $1 FOR UPDATE OF
"exhibits"

You don't seem to be updating gallery,exhibit_types,atoday_users,exhibits in
the following queries. Are you sure you want SELECT...FOR UPDATE here?

> CommitTransactionCommand
> StartTransactionCommand
> query: insert into exhibit_prices(exhibit_price_id,
exhibit_distribution_id, unframed_price_inr, framed_price_inr,
unframed_crate_price_inr, framed_crate_price_inr, tax_percent,
calculate_shipping_for)
values(317,717,12000.0,25000.0,150.0,2000.0,10.0,'WORLD')
> ProcessQuery
> query: SELECT oid FROM "exhibit_distributions" WHERE
"exhibit_distribution_id" = $1 FOR UPDATE OF "exhibit_distributions"
> CommitTransactionCommand
> StartTransactionCommand
> query: insert into
inventory(exhibit_distribution_id,quantity,quantity_available)
values(717,1,1)
> ProcessQuery
> CommitTransactionCommand
> StartTransactionCommand
> query: commit
>
>
> I actually use the 2 insert statements in a transaction.. However the
> log file is outputting a CommitTransactionCommand after every insert..
> Is it true that the data is committed after every insert or only when i
> commit the entire transaction..,
>
> Thanks
> Anand

You probably have an autocommit flag set to true. I don't use JDBC myself
but there is something similar in ODBC. If that's the case though the
SELECT...FOR UPDATE stuff should expire at the end of the transaction, so it
can't cause locking problems. As you noted earlier it sounds like a
transaction *isn't* being completed

If you can dump the database schema (pg_dump -s) and put together some
sample queries I'll be happy to see if I can reproduce the problem. Bear in
mind that I'll be running from the psql command-line and it might take me a
day or so to get back to you. If the files are big, feel free to send them
to me direct rather than via the list.

- Richard Huxton