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.

Pifer

Answer 1

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,

Swerve

Answer 2

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.

Offoffbroadway

So, you can update a column, by checking that old-state has expected value, but you can't update it twice, because if the updates arrived in the wrong order, they would both be discarded. So it would only work if there was enough time between the two updates, that you can be sure they arrive in the right order. – Pifer