Re: lock problem when dont commit

As Tom pointed out this behaviour will not change in the immediate
future.  I think your best bet is not to hold the lock for so long.

I generally prepare all the data and do the insert in one statement.

Dave

Mican Bican wrote:

> Tom Lane schrieb:
>
>> Dave Cramer <pg@fastcrypt.com> writes:
>>
>>
>>> Tom Lane wrote:
>>>
>>>
>>>> Maybe a conflict on referenced foreign-key rows?
>>>>
>>>
>>
>>
>>
>>> can you explain how that happens?
>>>
>>
>>
>> When you insert or update a row in a table that has an FK reference,
>> it takes out a row-level lock on the referenced row in the master table.
>> This is needed to prevent someone else from deleting the master row
>> before commit.  (Since the someone else can't see your uncommitted row,
>> nothing but a lock could stop them from thinking it's OK to remove the
>> master row.)
>>
>> So if two sessions are trying to insert rows that reference the same
>> master row, they conflict --- because the only type of row-level lock
>> we have at the moment is exclusive.  I believe Alvaro is looking into
>> supporting shared row-level locks for 8.1, which'd make this problem
>> go away.
>>
>>             regards, tom lane
>>
>>
>>
> Ok I think thats the problem.. because
>
> client1:
>
> ...
> conn.setAutoCommit(false)
> insert into liefAdress(adress,postcode) //id is autoincrement insert
> into liefAdress(adress2,postcode2)
> ....
> ....
>
>
> postcode is a FK to a list with postcodes. When I want insert different
> postcodes they are no locked.. there is only locked when the postcode are
> same. But why there are locked? Have anybody a good
> Idea to deal with this problem..?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561