Postgresql – SERIALIZABLE transaction information leak via serial

concurrencyisolation-levelpostgresqlsequence

I'm trying to implement a deterministic configuration for database sessions, and one problem seems to be with sequences. The following example demonstrates:

CREATE TABLE foo (id serial, n integer);

-- connection 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;

-- connection 2
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
insert into foo (n) values (1),(2),(3);

-- connection 1
select nextval('fun_id_seq') - (select count(*) from fun) as leak;

leak 
----------
       4 
(1 row)

This looks like a dirty read. If I was to run the transactions serially I would not get this result. Why is this then considered SERIALIZABLE?

Thanks

Best Answer

For various performance and concurrency reasons, sequences are non-transactional, but atomic. In other words, they always give a distinct value to each session, but they don't reuse values, nor are they "gapless" where they return a continuous sequence of numbers.

The second transaction in your example was handed distinct sequence values for each of the rows inserted, and since that is atomic, but non-transactional, the first transaction sees the sequence's nextval increased even though the transaction isolation level is SERIALIZEABLE.

The behavior is noted at the bottom in the Sequence Manipulation Functions documentation.

Neil Conway also explains this quite well in his post FAQ: Using Sequences in PostgreSQL, including a link to a method of creating a "gapless" sequence that should behave how you had initially expected, but at the cost of some concurrency and performance.

Related Solutions

PostgreSQL – Implement Insert-if-Not-Found for Serializable Transactions

This is a bit of a FAQ. You'd find more information if you searched for ON DUPLICATE KEY UPDATE (the MySQL syntax), MERGE (the SQL-standard syntax), or UPSERT. It's surprisingly hard.

The best article I've seen on it yet is Depesz's "why is upsert so complicated". There's also the SO question Insert, on duplicate update (postgresql) which has suggestions but lacks explanation and discussion of the issues.

The short answer is that, yes:

It seems the only option I have is to abort the entire transaction and try again.

When using SERIALIZABLE transactions you just have to re-issue them when they fail. Which they will. By design - and much more frequently on Pg 9.1 and above because of greatly improved conflict detection. Upsert-like operations are very high conflict, so you may land up retrying quite a bit. If you can do your upserts in READ COMMITTED transactions instead it'll help, but you should still be prepared to retry because there are some unavoidable race conditions.

Let the transaction fail with a unique violation when you insert the conflicting row. If you get a SQLSTATE 23505 unique_violation failure from the transaction and you know you were attempting an upsert, re-try it. If you get a SQLSTATE 40001 serialization_failure you should also retry.

You fundamentally cannot do that retry within a PL/PgSQL function (without dirty hacks like dblink), it must be application side. If PostgreSQL had stored procedures with autonomous transactions then it'd be possible, but it doesn't. In READ COMMITTED mode you can check for conflicting inserts made since the transaction started, but not after the statement that calls the PL/PgSQL function started, so even in READ COMMITTED your "detect conflict with select" approach simply will not work.

Read depesz's article for a much better and more detailed explanation.

Concurrent insertion in mutually exclusive tables in oracle

One way is to add discriminator column, something like

create table c (id_c int not null, val char(1))
create index idx_c1 on c(id_c,val);
alter table c add constraint chk_val check(val in ('a','b'));


alter table c add constraint c_pk primary key (id_c) using index idx_c1;
alter table c add constraint c_uq unique (id_c,val) using index idx_c1;


create table a(id_a int not null primary key, 
val char(1) default 'a' not null ,
constraint fk_t foreign key(id_a,val)  references c (id_c,val));

alter table a add constraint chk_val_a check (val ='a');

Similar for table (but check constraint should b check(val='b').

Then detail record can be in table a or b, but not in both. Overhead is small thanks to the fact Oracle allows multiple constraint supported by the same index.

Best Answer

Related Solutions

PostgreSQL – Implement Insert-if-Not-Found for Serializable Transactions

Concurrent insertion in mutually exclusive tables in oracle

Related Question