Re: preventing deadlocks
От | Tsirkin Evgeny |
---|---|
Тема | Re: preventing deadlocks |
Дата | |
Msg-id | 43BCDA17.6070806@mail.jct.ac.il обсуждение исходный текст |
Ответ на | Re: preventing deadlocks (Bruno Wolff III <bruno@wolff.to>) |
Ответы |
Re: preventing deadlocks
(Bruno Wolff III <bruno@wolff.to>)
|
Список | pgsql-admin |
Bruno Wolff III wrote: >On Wed, Jan 04, 2006 at 10:51:55 +0200, > Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote: > > >>>What you want to do in the trigger isinsert a new row into a table that >>>contains the change in count, instead of trying to update a single row >>>for each value/ident (btw, you'll probably get better performance if you >>>make ident an int instead of a numeric). >>> >>> >>Why? >> >> > >Because this doesn't block other processes and still gives you correct >results. > > > I understand THAT ,I meant why int will give me more performance. >>>So now you'll have a list of >>>changes to the value, which you will periodically want to roll up into a >>>table that just stores the count. >>> >>> >>Interesting idea.Thanks.However it pretty complicates things ,maybe there >>will be simpler solution.Something i did not thought about at all - i think that >> >> > >Not that avoids blocking. If you don't have a lot of concurrent queries then >this may not be an issue for you. > > > Well i DO have a lot of concurrent queries,that is the whole point. >>counting is something that everybody does. >> >> > >No it isn't. Smetimes they aren't needed at all, sometimes approximate values >are good enough, and even when they are needed, it is often the case that >it is better for count queries to run slower so that other queries run faster. > > > It was just a hope of mine. >>[1] I have also a hope that i can create a trigger that locks counter >>table once a 'select for update' was done on one of the tables i count. >>However how can i say if a select that fires a trigger is a 'for update' >>one? >> >> > >If you have a counter table and do an UPDATE that will lock that row. >If you have multiple tables that you keep counts for you will want to lock >the whole counter table using a LOCK command or else you can get deadlocks. >Note this means that inserts and deletes from any of the tracked tables >will block inserts and deletes of those tables in other concurrent queries. > > > That does not help me .What i want is : select 1 from table a where whatever for update; issuing this should fire a trigger that does: select 1 from table counter where a.pkey=counter.ident or something however this should only be done for select FOR UPDATE! And i can't know that inside the trigger! >>[2] Maybe there is a MVCC or something solution like Bruno suggested (that >>i did not realy understood thought). >> >> > >The explanation given at the top is the gist of the solution that uses MVCC >advantagesously. > > OK.
В списке pgsql-admin по дате отправления: