DynamoDB - Inconsistent query results when querying for a single item

Question

I have a table in DynamoDB that has a key schema like this:

class MyTableKeySchemaT(TypedDict):
    pkey: str # HASH
    id: int   # RANGE

The id in this table is a serially increasing integer.

I have a use case where I query this table to obtain the maximum id. My query looks like this:

def get_max_id(pkey: str) -> int:
    table = self._get_table("my-table")
    response = table.query(
        Select="SPECIFIC_ATTRIBUTES",
        Limit=1,
        KeyConditionExpression=Key('pkey').eq(pkey),
        ProjectionExpression="id",
        ScanIndexForward=False,
        ConsistentRead=True,
    )
    if len(response["Items"]) == 0:
        return -1
    return response["Items"][0]["id"]

This is fairly straightforward operation: Read the table in reverse sorted order and return the first item.

In my use-case, I have a process that periodically calls this function for a given pkey, and I am observing that the query for the same pkey will occasionally not return anything (hence the -1):

get_max_id("customer1") -> 7564
get_max_id("customer1") -> 7565
get_max_id("customer1") -> -1
get_max_id("customer1") -> 7566
get_max_id("customer1") -> 7567

If I print out details from the response I see what looks like this:

Count=1, ScannedCount=1, Items=[{'id': Decimal('7564')}], LastEvaluatedKey={'id': Decimal('7564'), 'pkey': 'customer1'}
Count=1, ScannedCount=1, Items=[{'id': Decimal('7565')}], LastEvaluatedKey={'id': Decimal('7565'), 'pkey': 'customer1'}
Count=0, ScannedCount=1, Items=[], LastEvaluatedKey={'id': Decimal('7566'), 'pkey': 'customer1'}
Count=1, ScannedCount=1, Items=[{'id': Decimal('7566')}], LastEvaluatedKey={'id': Decimal('7566'), 'pkey': 'customer1'}
Count=1, ScannedCount=1, Items=[{'id': Decimal('7567')}], LastEvaluatedKey={'id': Decimal('7567'), 'pkey': 'customer1'}

How is this possible?

First of all, Count and ScannedCount should be the same. From the documentation:

Count (integer) --

The number of items in the response.

If you used a QueryFilter in the request, then Count is the number of items returned after the filter was applied, and ScannedCount is the number of matching items before the filter was applied.

If you did not use a filter in the request, then Count and ScannedCount are the same.

QueryFilter is deprecated; should read FilterExpression. Neither of which I use in my query.

Second, the LastEvalulatedKey shows me the key that I want, but this is inclusive. Meaning that it has already been scanned, but determined that it was not a match.

Any ideas?

Lee Hannigan · Answer

Do you use TransactWriteItems for writing items to your table? If so, this can happen as Transactions are a 2 phase operation, 1 to prepare, 1 to commit.

Now i'm assuming you are always writing to the tail of your PK, as you mention you have an increasing SK. If you make that write by using TransactWriteItems then the Query can pick up the Prepare stage of the operation, and when filtering out items on the return path, it will actually drop that item as it was not yet committed, in return you get no items for your Query.

You could overcome this by setting Limit=2 and if you receive 2 items, drop one and keep the other.

DynamoDB - Inconsistent query results when querying for a single item

1 Answers