[ https://issues.apache.org/jira/browse/IGNITE-18057?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Alexander Lapin updated IGNITE-18057: ------------------------------------- Description: h3. Motivation According to tx protocol in order to prevent phantom reads we should lock current key on every iteration that may be bigger than upper bound border. Let's check following example: tbl [3, 5, 8] select * from tbl where k >= 3 and k <= 7 may be internally converted to sorted index scan (lowerBorder=3[included], upperBorder=7[included]) thus we won't see key 8 and won't lock it. That means that tx2 may insert key 6, so that we finish with phantom read anomaly on select * from tbl where k >= 3 and k <= 7 re-run. h3. Definition of Done Aforementioned scenario won't lead to phantom read. h3. Implementation Notes We should lock ceil(upper bound key) or +INF if there's no such key, thus we should iterate over sorted index without upper bound parameter manually comparing next key with users upperBound. was: TBD. Long story short. For now, searching for lower bound looks broken in storage. Storage cursor returns either lower bound (if found) or next item that large lower bound. In second case, * TX1 starts scan and searching for a lower bound, then lock a row next to the desired lower bound. * A concurrent transaction TX2 may add a new row between the lower bound and row which TX1 is about to lock. * TX1 must release lock and retry search, but it can't detect the issue. > Sql. Fix index scan transactional consistency. > ---------------------------------------------- > > Key: IGNITE-18057 > URL: https://issues.apache.org/jira/browse/IGNITE-18057 > Project: Ignite > Issue Type: Improvement > Components: sql > Reporter: Andrey Mashenkov > Priority: Major > > h3. Motivation > According to tx protocol in order to prevent phantom reads we should lock > current key on every iteration that may be bigger than upper bound border. > Let's check following example: > tbl [3, 5, 8] > select * from tbl where k >= 3 and k <= 7 may be internally converted to > sorted index scan (lowerBorder=3[included], upperBorder=7[included]) > thus we won't see key 8 and won't lock it. > That means that tx2 may insert key 6, so that we finish with phantom read > anomaly on select * from tbl where k >= 3 and k <= 7 re-run. > h3. Definition of Done > Aforementioned scenario won't lead to phantom read. > h3. Implementation Notes > We should lock ceil(upper bound key) or +INF if there's no such key, thus we > should iterate over sorted index without upper bound parameter manually > comparing next key with users upperBound. -- This message was sent by Atlassian Jira (v8.20.10#820010)