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

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..

query: insert into exhibit_distributions (exhibit_distribution_id, exhibit_id, created_by, exhibit_type_id, medium,
image_path_small,image_path_big, length, breadth, w 
idth, dimensional_aspect, unframed_volume_weight, framed_volume_weight, override_vw_computation, exhibit_code,
exhibit_options,unframed_exhibit_restriction, framed_exhibit_restriction, up_for_sale, gallery_id, site_section) values
(717,619,1001,3,'watercolor','test','test',97.0,100.0,null,'cms',2.0,24.416,'f',null,'NULL','WORLD','WORLD','T',52,'GALLERY')
ProcessQuery
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"
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


On Tue, Feb 13, 2001 at 09:14:49PM +0530, Anand Raman wrote:
>hi guys
>
>On Tue, Feb 13, 2001 at 10:07:57AM -0500, Tom Lane wrote:
>>"Richard Huxton" <dev@archonet.com> writes:
>>> Well, foreign keys enforce constraints so it's not unreasonable that the
>>> lock might extend to the referenced tables.
>>
>
>thanx tom
>I got this point after pondering over it for some time..
>
>
>>Precisely.  The SELECT FOR UPDATE is done to ensure that the referenced
>>rows don't go away before the update of the referencing table commits.
>>
>
>>I'm guessing that Anand's not shown us all the operations involved,
>>and that when things hang up it's because there are two different
>>transactions trying to lock overlapping sets of referenced rows.
>>Hard to tell from the info we have, however.
>
>as far as the entire picture is concerned here it comes..
>
>EXHIBITS ------> EXHIBIT_DISTRIBUTIONS --- EXHIBIT_PRICES
>
>where EXHIBITS is the primary table and has a one to many relationaship
>with EXHIBIT_DISTRIBUTIONS. EXHIBIT_DISTRIBUTIONS has a one to one
>relationship with EXHIBIT_PRICES.
>
>In my web applicaiton i am first inserting the rows into EXHIBITS table
>and in the next page ask the user to insert details regarding the
>EXHIBIT_DISTRIBUTIONS. I pass the primary key from the first form to the
>second and subsequently use it for inserting rows in
>EXHIBIT_DISTRIBUTIONS.
>
>The entire process works smoothly for the first few time.. However it
>hangs when i repeat the process for more than 3 times..
>
>I fail to understand if there is a problem then it would manifest itself
>in the very first time.. HOwever why should it show upon the third
>insert..
>
>I can attach the postgres log files and the creation scripts if it helps..
>
>Thanks for any suggestion..
>
>Anand
>>
>>            regards, tom lane