Re: SSI patch version 8

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