Hi,
On 08/25/2010 10:35 PM, Simon Riggs wrote:
If the row is key share locked (as opposed to tuple share locks we
already have), then an UPDATE would only work if it was a non-HOT
UPDATE.
I think you meant it the other way around: an UPDATE on a key share
locked tuple only works if it *was*
I thought it would be interesting to see how other databases handle
this peculiar deadlock situation.
I didn't have access to any Oracle or Sybase databases, but for what
it's worth I've tested MySQL.
Results:
1. Process 1 successfully made its update and managed to commit.
2. Process 1 second
On Fri, 2010-08-20 at 15:59 -0400, Tom Lane wrote:
Josh Berkus j...@agliodbs.com writes:
Hmmm. It seems to me that we'd need a sharelock on the referenced row
both times.
No, we don't. The first update knows that it's updating a pre-existing
referencing row and not changing the FK
Simon,
On 08/25/2010 11:53 AM, Simon Riggs wrote:
..we want to ensure that the PK value..
..or any other possibly referenced attributes?
Markus
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
On Wed, 2010-08-25 at 15:51 +0200, Markus Wanner wrote:
Simon,
On 08/25/2010 11:53 AM, Simon Riggs wrote:
..we want to ensure that the PK value..
..or any other possibly referenced attributes?
Don't think that's relevant.
referenced meaning by an RI constraint, which only ever refers to
2010/8/25 Simon Riggs si...@2ndquadrant.com:
referenced meaning by an RI constraint, which only ever refers to
PKs in other tables.
FK constraints can also point to non-PK UNIQUE columns.
Nicolas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your
On Wed, Aug 25, 2010 at 10:02 AM, Simon Riggs si...@2ndquadrant.com wrote:
On Wed, 2010-08-25 at 15:51 +0200, Markus Wanner wrote:
Simon,
On 08/25/2010 11:53 AM, Simon Riggs wrote:
..we want to ensure that the PK value..
..or any other possibly referenced attributes?
Don't think that's
On Wed, 2010-08-25 at 16:14 +0200, Nicolas Barbier wrote:
2010/8/25 Simon Riggs si...@2ndquadrant.com:
referenced meaning by an RI constraint, which only ever refers to
PKs in other tables.
FK constraints can also point to non-PK UNIQUE columns.
You're exactly correct and I now
2010/8/25 Simon Riggs si...@2ndquadrant.com:
On Wed, 2010-08-25 at 16:14 +0200, Nicolas Barbier wrote:
2010/8/25 Simon Riggs si...@2ndquadrant.com:
referenced meaning by an RI constraint, which only ever refers to
PKs in other tables.
FK constraints can also point to non-PK UNIQUE
On Wed, Aug 25, 2010 at 3:20 PM, Simon Riggs si...@2ndquadrant.com wrote:
FK constraints can also point to non-PK UNIQUE columns.
You're exactly correct and I now understand Markus' comment. Do you
think that exact meaning prevents my proposal from being useful?
I think it just shows it
Nicolas Barbier nicolas.barb...@gmail.com writes:
2010/8/25 Simon Riggs si...@2ndquadrant.com:
You're exactly correct and I now understand Markus' comment. Do you
think that exact meaning prevents my proposal from being useful?
Not at all, because I guess that updates to non-UNIQUE columns
On 08/25/2010 04:57 PM, Tom Lane wrote:
It strikes me that a possibly useful simplification of the idea is a
lock type that allows HOT updates and not non-HOT ones; or more
precisely not ones that change any indexed columns --- if the row ends
up having to go off-page for lack of space, that
It strikes me that a possibly useful simplification of the idea is a
lock type that allows HOT updates and not non-HOT ones; or more
precisely not ones that change any indexed columns --- if the row ends
up having to go off-page for lack of space, that need not concern us.
While an
Josh Berkus j...@agliodbs.com writes:
It strikes me that a possibly useful simplification of the idea is a
lock type that allows HOT updates and not non-HOT ones; or more
precisely not ones that change any indexed columns --- if the row ends
up having to go off-page for lack of space, that
On Wed, Aug 25, 2010 at 6:34 PM, Tom Lane t...@sss.pgh.pa.us wrote:
That is true, but tracking exactly which indexes are relevant for that,
at the extremely low level that this would have to take effect, doesn't
seem like a bright plan to me. It's already ugly beyond words that
heapam.c knows
Greg Stark gsst...@mit.edu writes:
It's still not a very practical idea at least at first glance. It
would mean storing a variable sized list of columns somewhere that can
be consulted when the update happens. I don't know how the share lock
infrastructure works but I don't think it's obvious
On Wed, 2010-08-25 at 14:10 -0400, Tom Lane wrote:
Greg Stark gsst...@mit.edu writes:
It's still not a very practical idea at least at first glance. It
would mean storing a variable sized list of columns somewhere that can
be consulted when the update happens. I don't know how the share
On 8/25/10 1:35 PM, Simon Riggs wrote:
If the row is key share locked (as opposed to tuple share locks we
already have), then an UPDATE would only work if it was a non-HOT
UPDATE. Yes, that would save us some effort in working out whether to
allow the UPDATE or not. It *is* more restrictive
Joel Jacobson j...@gluefinance.com writes:
a) both processes have been granted a RowExclusiveLock on table B. How can
both be granted a RowExclusiveLock on the same table? Since the table only
contains one row, it must be a lock on the same row, which should be
impossible, right?
This
Tom Lane t...@sss.pgh.pa.us wrote:
You didn't tell us exactly what the FK relationship is
The original post has an attachment with a self-contained example,
starting with table creation.
I suspect the reason for the deadlock is that one process is
trying to update a row that references
Kevin Grittner kevin.gritt...@wicourts.gov writes:
The surprising thing is that a particular row is (using the
identifiers from the attachment):
Process 2 updates a particular row without blocking.
Process 1 updates the same row, which blocks.
Process 2 updates the same row again (with
Hm, in my example, there are no INSERTs in the two conflicting transactions?
The suggestion on adding an ON INSERT trigger would have no effect as far as
I can see.
The comment from trigger.c is also about INSERT, can't see how it affects
us.
I don't understand exactly why this deadlock occurs,
Joel Jacobson j...@gluefinance.com writes:
I don't understand exactly why this deadlock occurs, but the one thing I
cannot understand is why process 2 is not allowed to update the same row,
which it has already updated in the same transaction.
It *is* allowed to, and in fact has already done
Process 1 updates A in its transaction, which is still going on when process
2 updates B, requiring a sharelock on A, which it is granted. But when
process 2 does its second update of B, also of course requiring a sharelock
on A, it is not granted.
I fully agree it must obtain a sharelock on the
Joel Jacobson j...@gluefinance.com writes:
I fully agree it must obtain a sharelock on the FK, but I cannot understand
why it is granted it the first time, but not the second time?
It *isn't* granted it the first time, because it doesn't try to acquire
it the first time. That FK check gets
It *is* allowed to, and in fact has already done so. The problem is
that it now needs a sharelock on the referenced row in order to ensure
that the FK constraint remains satisfied, ie, nobody deletes the
referenced row before we commit the update. In the general case where
the referencing
Optimized away, check, OK, but why? Because there is no new data in the FK
(table A) at the point of the first update of table B in process 2? But when
process 1 updates A, the FK B-A points to new data, which leads to process
2 tries to acquire a sharelock, which is not granted due to the update
Josh Berkus j...@agliodbs.com writes:
Hmmm. It seems to me that we'd need a sharelock on the referenced row
both times.
No, we don't. The first update knows that it's updating a pre-existing
referencing row and not changing the FK value. If someone were to try
to delete the referenced row,
I wrote:
In principle we don't need to sharelock the referencing row in either
update in this example, since the original row version is still there.
s/referencing/referenced/ ... sorry bout that ...
regards, tom lane
--
Sent via pgsql-hackers mailing list
29 matches
Mail list logo