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
Josh Berkus wrote:
the behavior was the same up to the second UPDATE on Process 2, at
which point there was no deadlock. Process 2 was able to commit,
at which point Process 1 failed with:
ERROR: could not serialize access due to concurrent update
Does this happen immediately, not
Kevin,
In the for what it's worth department, I tried out the current
Serializable Snapshot Isolation (SSI) patch with this test case at
the SERIALIZABLE transaction isolation level. Rather than defining
a foreign key, I ran the queries which an SSI implementation in a
SERIALIZABLE-only
(Magnus and pghackers, I've included you in this email, since it appears to
be PostgreSQL bug. The example below is general, and not specific to Glue
Finance database model. Feel free to share it with anyone.)
I've just tried to replicate the deadlock in 8.4.4 and 9.0b4.
Same problem in both
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,
On 8/20/10 7:18 AM, Tom Lane wrote:
It does go through without any deadlock, *if* there is no foreign key
involved. You didn't tell us exactly what the FK relationship is, but
I suspect the reason for the deadlock is that one process is trying to
update a row that references some row already
In my example,
Process 1:Process 2:
BEGIN;
SELECT pg_backend_pid();
BEGIN;
SELECT
pg_backend_pid();
UPDATE A SET Col1 = 1 WHERE AID = 1;
SELECT * FROM
Another question, Tom referred to a comment in
src/backend/command/trigger.c.
My example does not contain any triggers, nor insert commands. Is the
trigger.c-comment still relevant or is it a misunderstanding?
It's relevant for how the FKs are handled.
--
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
Josh Berkus j...@agliodbs.com wrote:
That's correct. This is the generic example I was talking about
earlier on -hackers. I'm not certain it's a bug per spec; I
wanted to talk through with Kevin what we *should* be doing in
this situation.
I'm certainly happy to address what impact the
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
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.
The problem is to know that, given the limited amount of information
available when performing the second update.
Ah, ok. I get it now.
Now to figure
I wrote:
If there are a lot of user-hostile behaviors there, it might be
worth looking at the possibility of bending the SSI techniques to
that end
In the for what it's worth department, I tried out the current
Serializable Snapshot Isolation (SSI) patch with this test case at
the
38 matches
Mail list logo
