Re: Predicate locking

30.04.2011 22:18, Kevin Grittner wrote:
>> Vlad Arkhipov wrote:
>> 29.04.2011 21:18, Kevin Grittner wrote:
>>
>>> Vlad Arkhipov wrote:
>>>
>
>
>>>> But even if it would work it would not help me anyways. Because
>>>> my constraint is much more complex and depends on other tables, I
>>>> cannot express it in terms of exclusion constraints.
>>>>
>>> Are you aware of the changes to the SERIALIZABLE transaction
>>> isolation level in the upcoming 9.1 release?
>>>
>>> http://wiki.postgresql.org/wiki/Serializable
>>> http://wiki.postgresql.org/wiki/SSI
>>>
>>> If you can wait for that, it might be just what you're looking
>>> for.
>>>
>
>
>> I would not like to make the whole transaction serializable because
>> of performance and concurrency reasons.
>>
>
> I'm curious -- what do you expect the performance and concurrency
> impact to be? You do realize that unlike SELECT FOR UPDATE,
> SERIALIZABLE in PostgreSQL 9.1 will not cause any blocking beyond
> what is there in READ COMMITTED, right?
>
Does 9.1beta contain the new SERIALIZABLE isolation level? If so, I can
show you some concurrency issues.

First I created a table:
create table t (id bigint, value bigint);
insert into t values (1, 1);
insert into t values (2, 1);
create index t_idx on t(id);
Then I started two transactions.

1.
begin transaction;
set transaction isolation level serializable;
select * from t where id = 2; // and do some logic depending on this result
insert into t (id, value) values (-2, 1);

2.
begin transaction;
set transaction isolation level serializable;
select * from t where id = 3; // and do some logic depending on this result
insert into t (id, value) values (-3, 0);

Then I commited the both and the second one raised an exception:
ERROR: could not serialize access due to read/write dependencies among
transactions
SQL state: 40001

However the second transaction does not access the records that the
first one does. If I had predicate locks I could avoid this situation by
locking the records with the specified id.