What kind of DB operations are both "idempotent and commutative" at the same time?

Question

I was researching Scala DB frameworks/wrappers, and came across Gizzard, from Twitter. While I was impressed at first, I cooled down when I read the restriction. They say that all DB operations you make have to be both idempotent and commutative. If I understand properly, this basically leaves almost nothing left. As an example, if I have an entity with an integer counter, and it has to be incremented. I can either use an "increment" operation, or a "set" operation. But increment would not be idempotent (if you run it twice, you get a different result then running it once), and set would not be commutative (setting first 5 and then 2 gives a different result then setting first two and then 5). So is there anything left apart from "insert-if-absent", which isn't very useful for most use-cases. What is the point of a distributed database framework which is so constrained that you basically cannot do anything useful with it? I must be missing something important.

[EDIT] Apart from "insert-if-absent" (and "delete-if-present"), I think that "compare-timestamp-and-set" would be both idempotent and commutative, if changes are queued instead of discarded, when "previous changes" are still missing. But I don't know if any DB implements that.

Answer 1

In general, idempotent actions must check state before acting. When applied to the context of database updates, this means checking the state of the data about to be changed before updating. For example:

update some_table set
some_column = 'some_new_value'
where id = 123
and some_column = 'its_current_value'; -- idempotent check

If this were run twice, the second call would do nothing.

To be commutative, the two updates would need affect different aspects of the data state (different columns/rows). That is, the validity of each idempotent check must not be affected by the other command's update action.

Answer 2

Commutative operations are just operations that cause a value to grow monotonically. Idempotent examples of said operations are:

• Inserting elements into a set,
• Setting a value to be the maximum of a number and its previous value,