From: | Anssi Kääriäinen <anssi(dot)kaariainen(at)thl(dot)fi> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Dan Ports <drkp(at)csail(dot)mit(dot)edu>, "john(dot)okite(at)gmail(dot)org" <john(dot)okite(at)gmail(dot)org> |
Subject: | Re: SSI patch version 8 |
Date: | 2011-01-11 12:00:49 |
Message-ID: | 4D2C4671.9070906@thl.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2011-01-11 12:04:12 | Re: system views for walsender activity |
Previous Message | Simon Riggs | 2011-01-11 11:58:22 | Re: system views for walsender activity |