From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | Mican Bican <mican58(at)gmail(dot)com> |
Cc: | jdbc mailinglist <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: lock problem when dont commit |
Date: | 2005-03-03 12:19:39 |
Message-ID: | 422700DB.1050503@fastcrypt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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(at)fastcrypt(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Sergio Lob | 2005-03-03 21:24:19 | BUG #1523: precision column value returned from getTypeInfo() always has value 9 |
Previous Message | Stéphane RIFF | 2005-03-03 10:06:46 | Re: Connection pool problem |