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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
