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
yougive an actual realistic example -- ie, not doing a select for> update and then never updating the row or with an
explanationof what> the programmer is attempting to accomplish with such an unusual> sequence? The rest of the post
talksabout 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