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.

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 11 of 21 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 27, '11 at 8:35a
activeMay 4, '11 at 9:05p
posts21
users8
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase