DynamoDB atomically updating counter

user_527302 0
Viewed 668

DynamoDB rookie here, interested to learning about NoSQL databases.

I have a scenario where I have a table which has a partition key of userId, a sort-key of time and a numeric handle. The handle is a sequential counter that increments by 1.

Here is an example of the table:

userId, time, handle
0     , 123 , 1
0     , 456 , 2
1     , 123 , 1
1     , 234 , 2
0     , 789 , 3
1     , 345 , 3

for a given userId, handles cannot have duplicates

What I want to be able to do is add a new record for userId 0, for time 891 and have a handle 1 greater than the last written record for userId 0 - which would be the penultimate row in the database, that is, 3 + 1 = 4.

The naive way is to query the database for userId of 0, sorting by the last time stamp (if that is even possible) to get the handle (3). That is the first request. You would then create a put_item request on the database which adds 1 to the handle (3 + 1 = 4) and creates a new record.

Clearly there is a race condition here, where inbetween the read query and creating the put_item request, another lambda/API/endpoint could have committed a new record to the database with the same handle (4), e.g. (1, 888, 4). When I commit my the original record (0, 891, 4), the handle is 4 when it should now be 5.

Is it possible to perform this read and write operation in a single transaction (maybe I have the wrong mindset).

Let me know if my question is not clear.

dynamodb-queries amazon-dynamodb atomic concurrency
2 Answers

You probably need a second table to hold an atomic counter that holds the highest handle for each userId. When you need to add a record you atomicly increment the counter for the userId in the second table, then take the value from the counter and use it for the new record.

user_30834680

What you are trying to do: “assign a monotonically increasing numeric value as a unique identifier” is an anti pattern with distributed databases (noSql, or otherwise)

Take a moment and consider the problem.

If you can use a GUID as a unique identifier then you no longer need to query to determine the last used identifier and are always guaranteed a unique value.

If you need the IDs to have some notion of order then you should consider a strategy whereby you generate the Ids from a timestamp plus some some small random value. If you attempt to insert a record with a duplicate identifier you just retry.

Otherwise, the way to use arbitrary monotonically increasing integers is to store those at the user level (same table, or separate) but each time you update a user record you will be updating the same key so you loose scalability.

Finally - if for each user you only have a very small number of records to store, consider a schema where you combine those into a single item.

user_630740
Related