From: | Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | Florian Pflug <fgp(at)phlo(dot)org>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle |
Date: | 2010-05-14 08:40:55 |
Message-ID: | AANLkTinuk32XsZg5LgW6ZPkuO-IJM0faA5tdOJY5_w_z@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
2010/5/14 Greg Stark <gsstark(at)mit(dot)edu>:
> On Thu, May 13, 2010 at 10:25 PM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
>
>> C1: BEGIN
>> C1: SELECT * FROM t WHERE id = 1 FOR UPDATE
>> C2: BEGIN
>> C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> C2: SELECT * FROM t -- Take snapshot before C1 commits
>> C1: COMMIT
>> C2: DELETE FROM t WHERE id = 1
>> C2: COMMIT
>
> Can you give an actual realistic example -- ie, not doing a select for
> update and then never updating the row or with an explanation of what
> the programmer is attempting to accomplish with such an unusual
> sequence? The rest of the post talks about FKs but I don't see any
> here...
The link with FKs is as follows:
* The example does not use a real FK, because the whole purpose is to
do the same as FKs while not using the FK machinery.
* The example uses only one table, because that is enough to
illustrate the problem (see next items).
* C1 locks a row, supposedly because it wants to create a reference to
it in a non-mentioned table, and wants to prevent the row from being
deleted under it.
* C2 deletes that row (supposedly after it verified that there are no
references to it; it would indeed not be able to see the reference
that C1 created/would create), and C1 fails to detect that.
* C2 also fails to detect the problem, because the lock that C1 held
is being released after C1 commits, and C2 can happily go on deleting
the row.
* The end result is that the hypothetical reference is created,
although the referent is gone.
Nicolas
From | Date | Subject | |
---|---|---|---|
Next Message | Florian Pflug | 2010-05-14 09:46:01 | Re: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle |
Previous Message | Yeb Havinga | 2010-05-14 07:13:17 | Re: List traffic |