What are the possible failure modes of read-filter-write inside a transaction without a lock?

Question

Consider the following workflow, running in MySQL with the InnoDB engine.

1. Begin a transaction.
2. Select some rows using SELECT FROM tablename WHERE ..., rather than SELECT ... FOR UPDATE.
3. Filter those rows using code that is not executing in the database (let's say Python).
4. Perform a DELETE on some subset of the rows.
5. End the transaction.

Based on this question, it seems that this workflow is not safe because of step 2, but the answers did not give details on the failure modes.

Is this simply undefined behavior, or is there a specific, well-defined set of failure modes?

Additionally, why doesn't the ACID nature of transactions protect against unsafe behavior here?

Answer 1

The DELETEs would always be safe. For example, consider a queue where the SELECT fetches some 'work' to be done, and the DELETE signals that the work has been completed. Furthermore, let's say that nasty things would happen if the work were done twice. (Perhaps something as simple as incrementing a counter is some other table.) Without the FOR UPDATE, two threads could grab the same item from the queue; one DELETE would delete the row; the other would delete zero rows (and you might not notice it).

If, instead, Step 3 involved UPDATEing some of the rows; there is a pretty good chance that you could be throwing conflicting updates at the row(s).

That's two "failure modes". Note that they are really application or data failures, not MySQL "failures". Without the FOR UPDATE, MySQL is happy to "do the wrong thing".

Think of FOR UPDATE as saying "It's mine; keep your hands off!"

Answer 2

I eventually found a more serious problem with this scenario when it interleaves with two other transactions, which results in the deletion of unintended data. This scenario is described below, where name is a primary key, and gender is another column.

Transaction 1

1. DELETE FROM people WHERE name="foo";

Transaction 2

1. SELECT name, gender FROM people WHERE name="foo";
2. (Application logic) If gender == female, then return.
3. DELETE FROM people WHERE name="foo" // Observe that the intent is to delete foo only if foo is male, based on application logic.

Transaction 3

1. INSERT INTO people (name, gender) VALUES("foo", "female")

With the following interleaving of transactions, we end up deleting a female foo from the table, which is not the application's intent.

2.1, 2.2, 1, 3, 2.3

With SELECT...FOR UPDATE, this would not happen because the SELECT prevents both 1.1 and 3.1 from happening.