Re: SSI patch version 8 - Mailing list pgsql-hackers

On 01/10/2011 06:03 PM, Kevin Grittner wrote:
> Due to popular request (Hey, David's popular, right?), I'm posting a
> patch for Serializable Snapshot Isolation (SSI), although I don't
> yet have everything in it that I was planning on submitting before
> the CF.  I will probably be submitting another version before the
> deadline with the following, but there should be plenty here for
> people to test and benchmark.  We're done with the major refactoring
> needed to address concerns raised in earlier reviews, and I don't
> expect the remaining work to destabilize what's there or to have a
> significant impact on performance.
I think I found a problem. This is using SSI v8. The table definition:

create table test_t (id integer, val1 text, val2 integer);

create index test_idx on test_t(id) where val2 = 1;

The data:

insert into test_t (select generate_series(0, 10000), 'a', 2);
insert into test_t (select generate_series(0, 10), 'a', 1);

The transactions:
T1:
hot2=> begin transaction isolation level serializable;
BEGIN
hot2=> select * from test_t where val2 = 1; id | val1 | val2
----+------+------  0 | a    |    1  1 | a    |    1  2 | a    |    1  3 | a    |    1  4 | a    |    1  5 | a    |
1 6 | a    |    1  7 | a    |    1  8 | a    |    1  9 | a    |    1 10 | a    |    1
 
(11 rows)

hot2=> update test_t set val2 = 2 where val2 = 1 and id = 10;
UPDATE 1
-- The concurrent transaction:
T2:
hot2=> begin transaction isolation level serializable;
BEGIN
hot2=> select * from test_t where val2 = 1; id | val1 | val2
----+------+------  0 | a    |    1  1 | a    |    1  2 | a    |    1  3 | a    |    1  4 | a    |    1  5 | a    |
1 6 | a    |    1  7 | a    |    1  8 | a    |    1  9 | a    |    1 10 | a    |    1
 
(11 rows)

hot2=> update test_t set val2 = 2 where val2 = 1 and id = 9;
UPDATE 1
hot2=> commit;
COMMIT
-- Now, t1 can commit, too. Even though there is a serialization anomaly
T1:
hot2=> commit;
COMMIT

If the test_idx is changed:
(outside any transaction...)
hot2=> drop index test_idx;
DROP INDEX
hot2=> create index test_idx on test_t(id, val2);
CREATE INDEX


T1:
hot2=> begin transaction isolation level serializable;
BEGIN
hot2=> select * from test_t where val2 = 1; id | val1 | val2
----+------+------  0 | a    |    1  1 | a    |    1  2 | a    |    1  3 | a    |    1  4 | a    |    1  5 | a    |
1 6 | a    |    1  7 | a    |    1  8 | a    |    1
 
(9 rows)

hot2=> update test_t set val2 = 2 where val2 = 1 and id = 8;
UPDATE 1

T2:
hot2=> select * from test_t where val2 = 1; id | val1 | val2
----+------+------  0 | a    |    1  1 | a    |    1  2 | a    |    1  3 | a    |    1  4 | a    |    1  5 | a    |
1 6 | a    |    1  7 | a    |    1  8 | a    |    1
 
(9 rows)

hot2=> update test_t set val2 = 2 where val2 = 1 and id = 7;
UPDATE 1
hot2=> commit;
ERROR:  could not serialize access due to read/write dependencies among 
transactions
HINT:  The transaction might succeed if retried.
T1:
hot2=> commit;
COMMIT

So, something seems to be broken when using partial indexes.
 - Anssi