Thread: Deadlock with tsearch2 index ...
Just want to make sure that this is, in fact, what is expected: client1: begin; client1: update articles set some_col = <foo> where id = <bar>; client2: update articles set some_col2 = <foo2> where id = <bar>; client1: update articles set some_col3 = <foo> where id = <bar>; client1: ** deadlock ** client2 can't finish its 'transaction', and is therefore preventing client1 from continuing ... ? Assuming that this is, in fact, 'normal', is there a way of breaking the deadlock? If this is *not*, in fact, 'normal', is there any more information that I can provide to debug this? This is a 7.4.6 database right now ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> Just want to make sure that this is, in fact, what is expected:
> client1: begin;
> client1: update articles set some_col = <foo> where id = <bar>;
> client2: update articles set some_col2 = <foo2> where id = <bar>;
> client1: update articles set some_col3 = <foo> where id = <bar>;
> client1: ** deadlock **
> client2 can't finish its 'transaction', and is therefore preventing
> client1 from continuing ... ?
Hmm, I can see why client2 might be blocked by client1, but if client1
holds the lock it should be able to continue to update the table.
I take it from your title that this only happens if there's a tsearch2
index on the table? Can you put together a test case?
regards, tom lane
On Tue, 31 May 2005, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> Just want to make sure that this is, in fact, what is expected:
>
>> client1: begin;
>> client1: update articles set some_col = <foo> where id = <bar>;
>> client2: update articles set some_col2 = <foo2> where id = <bar>;
>> client1: update articles set some_col3 = <foo> where id = <bar>;
>> client1: ** deadlock **
>
>> client2 can't finish its 'transaction', and is therefore preventing
>> client1 from continuing ... ?
>
> Hmm, I can see why client2 might be blocked by client1, but if client1
> holds the lock it should be able to continue to update the table.
>
> I take it from your title that this only happens if there's a tsearch2
> index on the table? Can you put together a test case?
I haven't tried this myself, but the client wrote this very quick script
that apparently recreates it every time:
test.sql:
---------------------------------------
/* load contrib/btree_gist.sql into database */
begin;
create table test ( id serial primary key, age int
);
create index test_age_key on test using gist(age);
insert into test values (nextval('test_id_seq'), 1);
commit;
/*
client1:
begin; update test set age = 2 where id = 1;
client2:
update test set age = 2 where id = 1;
client1:
update test set age = 2 where id = 1;
...deadlock...
*/
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Tue, 31 May 2005, Tom Lane wrote:
>> I take it from your title that this only happens if there's a tsearch2
>> index on the table? Can you put together a test case?
> I haven't tried this myself, but the client wrote this very quick script
> that apparently recreates it every time:
So it does. What's happening is that since client2 wants to update the
same row client1 already did, client2 blocks for client1 to commit and
release the row lock. Which is reasonable. But for non-concurrent
indexes (ie, gist and rtree), we take out AccessExclusiveLock at the
start of the statement, and release it again at the end of the
statement. So client1 released its index lock, and now client2 is
holding it while blocked on the row, and then client1 needs it back
in order to do its second update.
AFAICS, a near-term fix for this would require either that
(1) we don't release exclusive index locks at end of statement, but
hold them till end of transaction; then client1 would still be holding
the index lock and client2 would be blocked on that, not on the row.
Or
(2) we acquire and release the index lock for each *tuple* rather than
each statement. Then client2 doesn't hold the index lock while it's
waiting for the row lock to clear.
Neither of these cures sounds attractive :-(. I think #1 would probably
do as much to create deadlock cases as to prevent them. #2 would avoid
the deadlock but the performance cost would be high.
The real solution is to upgrade GIST to be concurrent. Oleg and Teodor
have made some noises about that in the past, but nothing's been done
about it that I've heard of.
regards, tom lane
On Tue, 31 May 2005, Tom Lane wrote:
>
> The real solution is to upgrade GIST to be concurrent. Oleg and Teodor
> have made some noises about that in the past, but nothing's been done
> about it that I've heard of.
unfortunately, we still couldn't find 2-3 months for dedicated work on
concurrency&recovery for GiST. I'm trying to find support here in Russia
for our work.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
On Tue, 31 May 2005, Tom Lane wrote: > (2) we acquire and release the index lock for each *tuple* rather than > each statement. Then client2 doesn't hold the index lock while it's > waiting for the row lock to clear. > > Neither of these cures sounds attractive :-(. I think #1 would probably > do as much to create deadlock cases as to prevent them. #2 would avoid > the deadlock but the performance cost would be high. But ... this wouldn't affect SELECT operations, would it? And only GiST related operations? Would the performance loss be noticeable? And, would the performance cost not be worth getting rid of the deadlocks, until the concurrency issues can be properly dealt with? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
> The real solution is to upgrade GIST to be concurrent. Oleg and Teodor > have made some noises about that in the past, but nothing's been done > about it that I've heard of. This whole GiST concurrency think really needs to be looked at :( There is so much cool stuff that can be done with it, and now that there are som fantastic uses of it, such as tsearch2 and pg_trgm, people are starting to run into these walls :( WAL logging would also be nice, especially since Tom mentioned in the manual that it was just a Small Matter Of Programming :D Chris
> unfortunately, we still couldn't find 2-3 months for dedicated work on > concurrency&recovery for GiST. I'm trying to find support here in Russia > for our work. How much money (US Dollars) would you need? Chris
Christopher Kings-Lynne wrote: >> unfortunately, we still couldn't find 2-3 months for dedicated work on >> concurrency&recovery for GiST. I'm trying to find support here in Russia >> for our work. > > > How much money (US Dollars) would you need? > Command Prompt could jump on that as well. We could help sponsor a bit. Sncerely, Joshua D. Drake > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org
On Wed, 2005-06-01 at 09:30 +0800, Christopher Kings-Lynne wrote: > This whole GiST concurrency think really needs to be looked at :( I spent some time looking at it toward the end of last year, but unfortunately I didn't have enough time to devote to it to get a working implementation (it is quite a lot of work). I'm still interesting in working on it, although I won't have any time until after 8.1. -Neil
>> How much money (US Dollars) would you need? >> > > Command Prompt could jump on that as well. We could help sponsor a bit. Maybe we could start a funding project for it? USD convert to lots of roubles I assume, so it'd be good like that. Perhaps someone (not me - too busy) on the PostgreSQL Foundation could organise contacting some companies to see if we can get a little out of several :) ie. Command Prompt, Fujitsu, Pervasive, EnterpriseDB, etc.? Oleg & Teodor: how about letting us know what funding target we'd need? Chris
On Wed, 1 Jun 2005, Christopher Kings-Lynne wrote:
>>> How much money (US Dollars) would you need?
>>>
>>
>> Command Prompt could jump on that as well. We could help sponsor a bit.
>
> Maybe we could start a funding project for it?
>
> USD convert to lots of roubles I assume, so it'd be good like that. Perhaps
What do you mean ? It's common misleading that life in Russia is cheap.
From my experience living in California is cheaper than here in Moscow.
10 years ago it was profitable to work in US some time, but not now :(
> someone (not me - too busy) on the PostgreSQL Foundation could organise
> contacting some companies to see if we can get a little out of several :)
>
> ie. Command Prompt, Fujitsu, Pervasive, EnterpriseDB, etc.?
>
> Oleg & Teodor: how about letting us know what funding target we'd need?
We'd love to start tomorrow working on GiST, but we have some current
obligations we need to sort out.
>
> Chris
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
On T, 2005-05-31 at 14:41 -0300, Marc G. Fournier wrote: > Just want to make sure that this is, in fact, what is expected: > > client1: begin; > client1: update articles set some_col = <foo> where id = <bar>; > client2: update articles set some_col2 = <foo2> where id = <bar>; > client1: update articles set some_col3 = <foo> where id = <bar>; > client1: ** deadlock ** > > client2 can't finish its 'transaction', and is therefore preventing > client1 from continuing ... ? > > Assuming that this is, in fact, 'normal', is there a way of breaking the > deadlock? > > If this is *not*, in fact, 'normal', is there any more information that I > can provide to debug this? This is the infamous 'lock for update' of foreign key master. It should be fixed for 8.1 by creating a shared row lock, but for 8.0 and below you can either drop the FK constraint, or replace it with a CHECK using a function that does a select. -- Hannu Krosing <hannu@tm.ee>
On Wed, 1 Jun 2005, Oleg Bartunov wrote: > We'd love to start tomorrow working on GiST, but we have some current > obligations we need to sort out. It will probably take more then a day to do fund raising, which is what Christopher is suggesting ... but they need some sort of 'value' for a goal ... you 'estimated' 2-3 months of dedicated work at one point in this thread ... is that for one person or two or ?? What sort of monetary value would be attached to that sort of thing? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Wed, 1 Jun 2005, Marc G. Fournier wrote:
> On Wed, 1 Jun 2005, Oleg Bartunov wrote:
>
>> We'd love to start tomorrow working on GiST, but we have some current
>> obligations we need to sort out.
>
> It will probably take more then a day to do fund raising, which is what
> Christopher is suggesting ... but they need some sort of 'value' for a goal
> ... you 'estimated' 2-3 months of dedicated work at one point in this thread
> ... is that for one person or two or ?? What sort of monetary value would be
> attached to that sort of thing?
2-3 months for 2 persons (me and Teodor) and 1.5K/mo for each.
We were planning to implement concurrency and recovery support as
described in Cornacker, Mohan, Hellerstein "Concurrency and Recovery in Generalized Search Trees".
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83