How to use DynamoDB conditional putItem on a non primary key attribute to guarantee uniqueness?

Question

I am implementing a feature called default naming for one of our product and I would like to see how conditional putItem can help me achieve our business logic need.

Here is the problem:

We have a legacy dynamoDB table which stores some entity information. The schema is: entityId, name, manufacturer

entityId is primary key and it is actually an UUID, name and manufacturer are just attributes.

Now our feature needs to generate a default name when we insert a new entry to the table. The default name needs to be unique per manufacturer, and we would like to use sequential number to represent it.

E.g. In the table we have 2 existing items

123,  lucy, apple
3672, tom, apple

now if I want to insert another entry of manufacturer apple to the table, I need to generate “third” as the name attribute

5678, third, apple

It seems easy, I can just do a read on all the entities of apple and get a count, then use count+1 as the default value for the name attribute.

However, our system is distributed, multiple request may come in, and race condition may happen. If I don’t do any prevention. Two requests may insert two entries into the table

5678, third, apple
9374, third, apple

instead we actual wants them to be

5678, third, apple
9374, forth, apple

or

5678, forth, apple
9374, third, apple

To solve the problem what I am thinking is using putItem with ConditionalExpression. For the above example, first do a read, got the count 2, then if two concurrent requests try to insert

"5678, third, apple" and "9374, third, apple" into the table, then one of them should got an exception, and we can increment the count again and retry.

The code of would be

try {
    Expected expect = new Expected("name").notExist();
    PutItemSpec putItemSpec = new PutItemSpec().withItem(item).withExpected(expected);
   entityTable.putItem(putItemSpec);
} catch (ConditionalExpression ex) {
   .. increment count and do retry
}

However I found that doesn’t work, there is no exception thrown. Am I misunderstanding the conditional put? How can I accomplish my goal?

Using ConditionalExpression this way can't help you to ensure uniqueness of a non-key field. Please see my answer here. It seems to fit exactly to your situation. — xtx
@xtx ok thanks. Your post can solve my problem. But I do have a question on the conditional put, does conditional put only works on attribute that is hash key? — Peiti Li

Dhwani Katagade Dhwani Katagade · Accepted Answer · 2020-01-03T14:51:29

ConditionExpression cannot be used for this purpose. ConditionExpression can be a bit misleading in DynamoDB for those coming from the RDBMS world. It is not to be thought of as an RDBMS table constraint check.

Explanation

ConditionExpression is specified on PutItem, UpdateItem, and DeleteItem operations. These operations operate on a unique key as defined for the table, which could be either just a hash key or a hash + sort key.

So, when these operations are run, the unique key identifies a unique item in the table. The cases could be as follows

PutItem - another item with the same unique key already exists
UpdateItem - the item to be updated is found
DeleteItem - the item to be deleted is found

The ConditionExpression just operates on this selected item to perform the condition check. So it cannot be used to check conditions on other items in the same table, to potentially check for uniqueness among other non key attributes.

Ref: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ConditionExpressions.html

Recommendation

To handle this case appropriately, the key design for the table should be reviewed. See https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/best-practices.html

How to use DynamoDB conditional putItem on a non primary key attribute to guarantee uniqueness?

1 Answers