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
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 *
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 restric
On Wed, 2010-08-25 at 14:10 -0400, Tom Lane wrote:
> Greg Stark 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
> > inf
Greg Stark 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 that there i
On Wed, Aug 25, 2010 at 6:34 PM, Tom Lane 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 enough about i
Josh Berkus 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 need not conc
> 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 improv
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 need
Nicolas Barbier writes:
> 2010/8/25 Simon Riggs :
>> 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 are way
> more common that updates to UNIQU
On Wed, Aug 25, 2010 at 3:20 PM, Simon Riggs 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 needs more thought.
2010/8/25 Simon Riggs :
> On Wed, 2010-08-25 at 16:14 +0200, Nicolas Barbier wrote:
>> 2010/8/25 Simon Riggs :
>>
>> > "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 co
On Wed, 2010-08-25 at 16:14 +0200, Nicolas Barbier wrote:
> 2010/8/25 Simon Riggs :
>
> > "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 understand Markus'
On Wed, Aug 25, 2010 at 10:02 AM, Simon Riggs 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 relevan
2010/8/25 Simon Riggs :
> "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 subscription:
htt
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
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:
http://www.postgresql.org/mailpref/pgsql
On Fri, 2010-08-20 at 15:59 -0400, Tom Lane wrote:
> Josh Berkus 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 some
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 immediat
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-on
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 SERIA
> 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 fig
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 (pgsql-hac
Josh Berkus 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, they would see th
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
> 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 referenc
Joel Jacobson 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 optimized away, while
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 F
Josh Berkus 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 SSI patch will h
Joel Jacobson 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 so. The problem is
> 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.
--
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 vLo
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 alrea
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, bu
"Kevin Grittner" 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 *exactly* the same UPDATE
Tom Lane 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 some row already u
Joel Jacobson 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 complaint seems to be ba
(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 versi
38 matches
Mail list logo
