Database Race Condition

Default non transactional behavior

Given multiple database operations without Database transaction enabled. The order of which one should be execute first is not determined. Therefore:

• One might overwrite the other operation
• Result is not deterministic — sometimes it's this sometimes it's that

[!note]
By default, even without transaction block, each SQL statement is executed in an automatic (autocommit) mode. Which means each statement is a transaction of its own.

Therefore even without transactional, it still uses both Read lock and Write lock for normal INSERT, UPDATE, PUT, DELETE, SELECT ...

As a result, it's impossible to have exactly 2 individual SQL statement to execute in the exact same time for WRITE (for SELECT, since it's a shared lock, it can be executed in the same time)

Problems in multithreading environment

Dirty read

Happen when 1 transaction reading an uncommited data (dirty).

For example:

In the example above, if transaction 1 rollback, the read is invalid

Non repeatable read

Imagine we only choose to commit to read commited data. Non-repeatable read happen when reading the data which is commited, however when re-read the data it has been changed to a different value already

In this case, since Transaction 2 changed the data, re-reading the data from Transaction 1 will lead to a different result.

Phantom read

Phantom (ghost) read happens when there is an unexpected data happen in our result when we re-read it.

So for example given the situation above. Let's assume that we locked the record we're reading. Phantom read problem still happen when we're querying the record as a range, and there are unexpected record appeared / disappeared in our range.

For example if we see in here, later on when we query again, there is another row.