DynamoDB Condition Checks are a very powerful (and somewhat) misunderstood capability that makes application development much easier.
Consider this most basic “financial” use-case. I have a table that contains customer account balances. Alice has $500 and Bob has $200. Bob wishes to give Alice $100. This is something really simple that screams “RDBMS” but is actually non-trivial with an RDBMS.
We all know that we need transactions because …
But consider this simple twist – How do you prevent Bob’s account balance from going negative?
There is (and I’d love to be proved wrong) no simple SQL solution to this in RDBMS without using one of (a) constraints, (b) triggers, or (c) stored procedures to implement this operation. The easiest is to stick a >= 0 constraint on the account balance. If Bob tries to give Alice more than $200, the update will fail with a constraint violation.
Why do you need one of these things? Consider this (flawed) implementation with an RDBMS.
--
-- WARNING: This implementation is flawed
--
BEGIN;
UPDATE BALANCES
SET BALANCE = BALANCE + 300
WHERE USER = "ALICE";
-- The update below may, or may not update Bob's balance!
UPDATE BALANCES
SET BALANCE = BALANCE - 300
WHERE USER = "BOB" AND BALANCE > 300;
COMMIT;This will complete successfully with the first update giving Alice the money and the second succeeding without doing anything!
This is where the Condition Check in DynamoDB comes along. Consider this with DynamoDB
us-east-1> create table balances ( owner string ) primary key ( owner hash ) billing mode on demand;
CREATE
us-east-1> insert into balances ( owner, balance ) values ( "alice", 500 ), ( "bob", 200 );
INSERT
INSERT
us-east-1> select * from balances;
{balance: 200, owner: bob}
{balance: 500, owner: alice}
us-east-1> begin;
us-east-1> update balances set balance = balance + 300 where owner = "alice";
us-east-1> update balances set balance = balance - 300 where owner = "bob" and balance > 300;
us-east-1> commit;
Transaction failed. TransactionCanceledException. PLRD70PMCA382RM2S0T2OF4O7VVV4KQNSO5AEMVJF66Q9ASUAAJG. Transaction cancelled, please refer cancellation reasons for specific reasons [None, ConditionalCheckFailed]
ABORT
us-east-1> We’re being told that the second write in the transaction had a ConditionCheckFailure (see the reasons List, first had None, second had CCF).
Now, we could redo the same thing with a valid amount, less than $200 like this.
us-east-1> select * from balances;
{balance: 200, owner: bob}
{balance: 500, owner: alice}
us-east-1> begin;
us-east-1> update balances set balance = balance + 100 where owner = "alice";
us-east-1> update balances set balance = balance - 100 where owner = "bob" and balance > 100;
us-east-1> commit;
COMMIT
us-east-1> select * from balances;
{balance: 100, owner: bob}
{balance: 600, owner: alice}
us-east-1> Now that’s so much more civilized! The WHERE clause “balance > 100” turns into a condition check, and if it fails, so does the transaction.
In DynamoDB, that is all a single API call like so (illustrated using “explain”):
us-east-1> explain begin;
us-east-1> update balances set balance = balance + 100 where owner = "alice";
us-east-1> update balances set balance = balance - 100 where owner = "bob" and balance > 100;
us-east-1> commit;
TransactWriteItems({
"TransactItems": [{
"Update": {
"Key": {
"owner": {
"S": "alice"
}
},
"UpdateExpression": "SET #akaa1 = #akaa1+:vkaa1",
"TableName": "balances",
"ConditionExpression": "attribute_exists(#akaa2)",
"ExpressionAttributeNames": {
"#akaa1": "balance",
"#akaa2": "owner"
},
"ExpressionAttributeValues": {
":vkaa1": {
"N": "100"
}
}
}
}, {
"Update": {
"Key": {
"owner": {
"S": "bob"
}
},
"UpdateExpression": "SET #alaa1 = #alaa1-:vlaa1",
"TableName": "balances",
"ConditionExpression": "attribute_exists(#alaa2) AND #alaa1 > :vlaa2",
"ExpressionAttributeNames": {
"#alaa1": "balance",
"#alaa2": "owner"
},
"ExpressionAttributeValues": {
":vlaa1": {
"N": "100"
},
":vlaa2": {
"N": "100"
}
}
}
}],
"ClientRequestToken": "1F087670-87E3-4973-8062-9A0A54E7F200"
})
us-east-1>
Notice that there is the update expression that updates the balance down by $100, but also a condition check that requires an item to exist that meets the requirement
owner = "bob" and balance > 100Nifty!
In SQL, different databases give you constructs like this (from SQLServer)
UPDATE BALANCES
SET BALANCE = BALANCE - 300
WHERE OWNER = "Bob" AND BALANCE > 300;
IF @@ROWCOUNT = 0 ...You’d then be able to decide whether or not you actually updated a row, and take corrective action.
DynamoDB UpdateItem() provides a condition check expression (even without a transaction).
us-east-1> select * from balances;
{balance: 100, owner: bob}
{balance: 600, owner: alice}
us-east-1> update balances set balance = balance - 200 where owner = "bob" and balance > 200;
UPDATE (0 read, 0 modified, 1 ccf)
us-east-1>That Update gets transformed into this API call.
us-east-1> explain update balances set balance = balance - 200 where owner = "bob" and balance > 200;
UpdateItem({
"TableName": "balances",
"Key": {
"owner": {
"S": "bob"
}
},
"UpdateExpression": "SET #aoaa1 = #aoaa1-:voaa1",
"ConditionExpression": "attribute_exists(#aoaa2) AND #aoaa1 > :voaa2",
"ExpressionAttributeNames": {
"#aoaa1": "balance",
"#aoaa2": "owner"
},
"ExpressionAttributeValues": {
":voaa1": {
"N": "200"
},
":voaa2": {
"N": "200"
}
}
})
us-east-1> Observe that in addition to specifying the Key (owner = bob), it provides a condition expression too!
Hype Cycles 