| From: | Konstantin <kostya2702(at)rambler(dot)ru> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org> |
| Subject: | Re: BUG #4648: needless deadlock on tables having foreign-key |
| Date: | 2009-02-13 08:30:39 |
| Message-ID: | 172745599.1234513839.206304664.53080@mcgi16.rambler.ru |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [Thu, 12 Feb 2009 19:10:34 -0500]:
> [ shrug... ] The "implementation artifact" is that you didn't get a
> deadlock *earlier*.
I agree that such behavior is more plain rather than current.
> You can't expect to update referenced rows and
> referencing rows in the same transaction and not risk deadlock against
> other transactions doing the same thing.
No. One transaction works with referenced rows only. Another with
referencing rows only.
I expect predictability, then flexibility (locks optimization).
So what is your recommendation?
To avoid deadlock do I need lock all referencing rows if I need to
update referenced row(s)?
Something like ` select * from bill1 where pid = 1 or aid = 1 for
update; ` in the first session of example?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Nitin | 2009-02-13 11:29:18 | BUG #4651: Postgresql connection error with PHP 5. |
| Previous Message | Scott Carey | 2009-02-13 05:53:45 | 8.3.5: Query Planner takes 15+ seconds to plan Update or Delete queries on partitioned tables. |