The I in ACID works more like a dial than a yes or a no, and it probably isn't set where you think it is.

Isolation level

How much one transaction can see of others running at the same moment. Stronger levels hide more, and may refuse a transaction and make it retry. Weaker levels let strange things through. Dirty read, non-repeatable read and lost update are the names of those strange things.

Every isolation bug needs two transactions at once, so your tests will never hit one, and the agent can't reason its way to one unless you ask. And the default may not be what you assume. Say which level a piece of code relies on, and if you go stronger, say what retries.

Say it to your agent

“This reads a balance and then writes it, so at Read Committed it can lose an update. Run it at REPEATABLE READ, catch SQLSTATE 40001 and retry the whole transaction up to 3 times. Add a test with two concurrent connections that shows the retry happening.”

Not to be confused with Thread safety

A lock in your code protects one process. Your database is shared by every copy of your server, so a lock in one copy protects nothing against another.

Where you see it

PostgreSQL: "Read Committed is the default isolation level in PostgreSQL." MySQL: "The default isolation level for InnoDB is REPEATABLE READ." So the same code can behave differently on each. Postgres also says its "Read Uncommitted mode behaves like Read Committed", so our bench, which does let you see a dirty read, follows the standard there and not Postgres. Stronger levels have a cost: "Applications using this level must be prepared to retry transactions due to serialization failures."

Isolation level

Before the agent

The agent

The harness

Everything it can get wrong

The operator

The workshop