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@phlo.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