Re: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle

On 05/14/2010 03:37 AM, Greg Stark wrote:
> 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...
>

Doing a select for update and then never updating the row is a realistic
example.

I am currently designing a database where this is an issue. The
simplified schema to illustrate the problem:

create table object (
id integer primary key
);

insert into object values(1);

create table attribute (
object_id integer not null references object,
attr_type integer not null, -- references attr_types
value text not null,
valid_from timestamp not null,
valid_until timestamp
);

Now, I want to make sure there are no pairs of (object_id, attr_type)
where the valid_from, valid_until times overlap.

A problematic sequence for this schema, both transactions in isolation
level serializable:

C1: begin;
C1: select * from object where id = 1 for update;
-- check for conflicting attr_type, realistically where condition should
have overlapping check, but left out for simplicity...
C1: select * from attribute where object_id = 1 and attr_type = 1;
-- Ok, nothing overlapping, I am able to insert.
C1: insert into attribute values (1, 1, 'Anssi', now(), null);
C2: begin;
-- This blocks.
C2: select * from object where id = 1 for update;
C1: commit;
-- Check for conflicts. This select won't see the insert C1 did.
C2: select * from attribute where object_id = 1 and attr_type = 1;
-- C2 doesn't see anything conflicting
C2: insert into attribute values (1, 1, 'Matti', now(), null);
C2: commit;
-- Inconsistency.

Now, that same sequence does work for read committed isolation level (C2
sees the insert of C1), and that is my solution for now: require
applications to use read committed isolation level. This could also be
solved by issuing "update object set id = id where id = 1" instead of
using select for update. This would result in serialization error.

I know that for this particular example the upcoming exclusion
constraints would solve the problem. But if I would want to ensure that
if attr_value for attr_type 1 is 'Anssi' then attr_value for attr_type 2
is 'Kääriäinen', then exclusion constraints could not be used.

--
Anssi Kääriäinen