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