Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcdf91ed

Browse files
committed
Fix serializable mode with index-only scans.
Serializable Snapshot Isolation used for serializable transactionsdepends on acquiring SIRead locks on all heap relation tuples whichare used to generate the query result, so that a later delete orupdate of any of the tuples can flag a read-write conflict betweentransactions. This is normally handled in heapam.c, with tuple levellocking. Since an index-only scan avoids heap access in many cases,building the result from the index tuple, the necessary predicatelocks were not being acquired for all tuples in an index-only scan.To prevent problems with tuple IDs which are vacuumed and re-usedwhile the transaction still matters, the xmin of the tuple is part ofthe tag for the tuple lock. Since xmin is not available to theindex-only scan for result rows generated from the index tuples, itis not possible to acquire a tuple-level predicate lock in suchcases, in spite of having the tid. If we went to the heap to get thexmin value, it would no longer be an index-only scan. Rather thanprohibit index-only scans under serializable transaction isolation,we acquire an SIRead lock on the page containing the tuple, when itwas not necessary to visit the heap for other reasons.Backpatch to 9.2.Kevin Grittner and Tom Lane
1 parentc63f309 commitcdf91ed

File tree

4 files changed

+103
-1
lines changed

4 files changed

+103
-1
lines changed

‎src/backend/executor/nodeIndexonlyscan.c

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@
3030
#include"executor/nodeIndexonlyscan.h"
3131
#include"executor/nodeIndexscan.h"
3232
#include"storage/bufmgr.h"
33+
#include"storage/predicate.h"
3334
#include"utils/memutils.h"
3435
#include"utils/rel.h"
3536

@@ -52,7 +53,6 @@ IndexOnlyNext(IndexOnlyScanState *node)
5253
ExprContext*econtext;
5354
ScanDirectiondirection;
5455
IndexScanDescscandesc;
55-
HeapTupletuple;
5656
TupleTableSlot*slot;
5757
ItemPointertid;
5858

@@ -78,6 +78,8 @@ IndexOnlyNext(IndexOnlyScanState *node)
7878
*/
7979
while ((tid=index_getnext_tid(scandesc,direction))!=NULL)
8080
{
81+
HeapTupletuple=NULL;
82+
8183
/*
8284
* We can skip the heap fetch if the TID references a heap page on
8385
* which all tuples are known visible to everybody. In any case,
@@ -147,6 +149,18 @@ IndexOnlyNext(IndexOnlyScanState *node)
147149
}
148150
}
149151

152+
/*
153+
* Predicate locks for index-only scans must be acquired at the page
154+
* level when the heap is not accessed, since tuple-level predicate
155+
* locks need the tuple's xmin value. If we had to visit the tuple
156+
* anyway, then we already have the tuple-level lock and can skip the
157+
* page lock.
158+
*/
159+
if (tuple==NULL)
160+
PredicateLockPage(scandesc->heapRelation,
161+
ItemPointerGetBlockNumber(tid),
162+
estate->es_snapshot);
163+
150164
returnslot;
151165
}
152166

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: rxwy1 c1 rywx2 c2
4+
step rxwy1: DELETE FROM taby WHERE id = (SELECT min(id) FROM tabx);
5+
step c1: COMMIT;
6+
step rywx2: DELETE FROM tabx WHERE id = (SELECT min(id) FROM taby);
7+
step c2: COMMIT;
8+
9+
starting permutation: rxwy1 rywx2 c1 c2
10+
step rxwy1: DELETE FROM taby WHERE id = (SELECT min(id) FROM tabx);
11+
step rywx2: DELETE FROM tabx WHERE id = (SELECT min(id) FROM taby);
12+
step c1: COMMIT;
13+
step c2: COMMIT;
14+
ERROR: could not serialize access due to read/write dependencies among transactions
15+
16+
starting permutation: rxwy1 rywx2 c2 c1
17+
step rxwy1: DELETE FROM taby WHERE id = (SELECT min(id) FROM tabx);
18+
step rywx2: DELETE FROM tabx WHERE id = (SELECT min(id) FROM taby);
19+
step c2: COMMIT;
20+
step c1: COMMIT;
21+
ERROR: could not serialize access due to read/write dependencies among transactions
22+
23+
starting permutation: rywx2 rxwy1 c1 c2
24+
step rywx2: DELETE FROM tabx WHERE id = (SELECT min(id) FROM taby);
25+
step rxwy1: DELETE FROM taby WHERE id = (SELECT min(id) FROM tabx);
26+
step c1: COMMIT;
27+
step c2: COMMIT;
28+
ERROR: could not serialize access due to read/write dependencies among transactions
29+
30+
starting permutation: rywx2 rxwy1 c2 c1
31+
step rywx2: DELETE FROM tabx WHERE id = (SELECT min(id) FROM taby);
32+
step rxwy1: DELETE FROM taby WHERE id = (SELECT min(id) FROM tabx);
33+
step c2: COMMIT;
34+
step c1: COMMIT;
35+
ERROR: could not serialize access due to read/write dependencies among transactions
36+
37+
starting permutation: rywx2 c2 rxwy1 c1
38+
step rywx2: DELETE FROM tabx WHERE id = (SELECT min(id) FROM taby);
39+
step c2: COMMIT;
40+
step rxwy1: DELETE FROM taby WHERE id = (SELECT min(id) FROM tabx);
41+
step c1: COMMIT;

‎src/test/isolation/isolation_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@ test: ri-trigger
99
test: partial-index
1010
test: two-ids
1111
test: multiple-row-versions
12+
test: index-only-scan
1213
test: fk-contention
1314
test: fk-deadlock
1415
test: fk-deadlock2
Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
# index-only scan test
2+
#
3+
# This test tries to expose problems with the interaction between index-only
4+
# scans and SSI.
5+
#
6+
# Any overlap between the transactions must cause a serialization failure.
7+
8+
setup
9+
{
10+
CREATETABLEtabx (idintNOTNULL);
11+
INSERTINTOtabxSELECTgenerate_series(1,10000);
12+
ALTERTABLEtabxADDPRIMARYKEY (id);
13+
CREATETABLEtaby (idintNOTNULL);
14+
INSERTINTOtabySELECTgenerate_series(1,10000);
15+
ALTERTABLEtabyADDPRIMARYKEY (id);
16+
}
17+
setup {VACUUMFREEZEANALYZEtabx; }
18+
setup {VACUUMFREEZEANALYZEtaby; }
19+
20+
teardown
21+
{
22+
DROPTABLEtabx;
23+
DROPTABLEtaby;
24+
}
25+
26+
session"s1"
27+
setup
28+
{
29+
BEGINISOLATIONLEVELSERIALIZABLE;
30+
SETLOCALseq_page_cost=0.1;
31+
SETLOCALrandom_page_cost=0.1;
32+
SETLOCALcpu_tuple_cost=0.03;
33+
}
34+
step"rxwy1" {DELETEFROMtabyWHEREid= (SELECTmin(id)FROMtabx); }
35+
step"c1" {COMMIT; }
36+
37+
session"s2"
38+
setup
39+
{
40+
BEGINISOLATIONLEVELSERIALIZABLE;
41+
SETLOCALseq_page_cost=0.1;
42+
SETLOCALrandom_page_cost=0.1;
43+
SETLOCALcpu_tuple_cost=0.03;
44+
}
45+
step"rywx2" {DELETEFROMtabxWHEREid= (SELECTmin(id)FROMtaby); }
46+
step"c2" {COMMIT; }

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp