Idempotence in CRUD operations

Hello, readers. Today we're going to talk about idempotence.

The concept of idempotence is paramount in database security. By saying that, of course, I feel compelled to explain what idempotence is. That term tends to come up a lot with regard to data transactions. The official definition is as follows, with regard to data transactions.

Idempotency is the property of an operation where performing it multiple times produces the same result as performing it once.

What that means is that if you apply an operation over and over, and it makes no difference to the database after the first time, that operation is idempotent.

With regard to CRUD (CREATE, READ, UPDATE, DELETE) operations, it's important to understand which ones are idempotent, and which ones run the risk of undesirable results if accidentally executed multiple times. 

Please note that code examples are in MySQL.

CREATE

Idempotence: No (except in special circumstances)
CREATE operations are not idempotent by default. If you run a CREATE operation multiple times, you are going to get multiple created rows.

INSERT INTO categories (name, description) VALUES ("Cat A", "test")

Running this three times will result in these three rows being added.

id	name	description
1	Cat A	test
2	Cat A	test
3	Cat A	test

What if the design of the table was structured this way? What if name was supposed to be unique?

CREATE table categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) UNIQUE,
  description TEXT
)

Being unique.

In that case, that particular CREATE operation would be idempotent because the database simply wouldn't allow subsequent CREATE operations with the same values in the name column.

id	name	description
1	Cat A	test

Also, if you defined an UPSERT operation instead of a CREATE, the record would only get inserted the first time. Subsequent times, the record would already exist, and therefore it would get updated... with the same values. Thus, making it idempotent. There is an exception to this exception, but that will be discussed in the UPDATE operation further down.

INSERT INTO categories (name, description)
VALUES ("Cat A", "test")
ON DUPLICATE KEY UPDATE
description = VALUES(description);

READ

Idempotence: Yes
This is the most straightforward operation in the sense that it is always idempotent. The first READ request makes no change to the database. Subsequent READ requests also make no change.

SELECT * FROM categories WHERE name = "Cat A"

READ operations are not
about changing data.

No exceptions to the rule, no nothing. This is as open-and-shut a case of idempotence as you're ever going to get in the world of computer science.

UPDATE

Idempotence: Yes (except in special circumstances)
In most cases, running an UPDATE operation on a specific record multiple times, has no effect beyond the first time. It will end up updating that record to the same values. In the query below, row id 1's name and description values would just keep being updated to "Cat A" and "test", which is basically no change.

UPDATE categories
name = "Cat A",
description = "test"
WHERE id = 1

Thus, this is idempotent. Unless...

What if the table had an Audit Field that tracked when the record was updated?

CREATE table categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  description TEXT,
  updated DATETIME
)

And what if the UPDATE operation was like this? Then the updated field would be a different value every time this operation was run, and therefore it would no longer be idempotent!

Time being tracked.

UPDATE categories
name = "Cat A",
description = "test"
updated = now()
WHERE id = 1

Also, what if the UPDATE operation wasn't on a specific row? This query updates all rows where the name field fits a particular condition. Kind of like dropping a bomb on an entire country instead of surgically taking out one terrorist. (Boy, this got dark)

UPDATE categories
name = "Cat A",
description = "test"
WHERE name like "%Cat%"

What if, just after the UPDATE operation above was run, a row was coincidentally inserted?

INSERT INTO categories (name, description) VALUES ("Cat B", "test")

Then we'd have two rows like this...

id	name	description
1	Cat A	test
2	Cat B	test

...but if the UPDATE operation was run again, this would be the result!

id	name	description
1	Cat A	test
2	Cat A	test

So, to conclude, in principle, the UPDATE operation is idempotent. But only if the values updated are absolute, and only if a very specific record is being updated.

DELETE

Idempotence: Yes (except in special circumstances)
In principle, DELETE operations are idempotent. After all, you can only delete a record successfully, once. After that, it no longer exists, so rerunning the same DELETE operation, achieves nothing. To employ a grim analogy, you can't kill someone twice.

You can only kill
someone once.

DELETE FROM categories WHERE id = 1

However, the concerns that exist for the UPDATE operation also exist for the DELETE operation. What if the DELETE operation wasn't on a specific row? This deletes all rows where the name field fits a certain filter.

DELETE FROM categories WHERE name LIKE "%CAT%"

And if, after the first DELETE operation, a record was added like so, rerunning the above DELETE operation would remove this row!

INSERT INTO categories (name, description) VALUES ("Cat B", "test")

In conclusion

A lot of what I've outlined is context. In simple cases, the answer of idempotence are similarly straightforward. But this is software development, where things are rarely that simple.

Wishing you a categorically good day,
T___T