Question :
How is pessimistic locking with read and write locks related to isolation levels?
Readers block writers and writers block readers. This behaviour is always constant irrespective of isolation level chosen.
Does this mean, with pessimistic locking, isolation level simply doesn’t make sense?
These DBMS concurrency topics are quite complex to understand in depth.
Answer :
Here is what I think might be two simple-to-explain isolation modes:
“Consistent” — SELECT; other stuff; then SELECT of the same rows will get the same rows, even if other threads introduced new rows.
“Dirty” — You can see rows that were inserted by other even if they will ultimately be undone (by ROLLBACK, etc).
The implementation keeps multiple copies of each row, at least until COMMIT/ROLLBACK. Think of it this way… Each copy has a timestamp-like number that the SELECT can use to decide which copy of the row it should see:
Consistent — the latest row before the SELECT started.
Dirty — all rows are visible, so pick the latest.
Note that you need SELECT ... FOR UPDATE to keep other threads from messing with row(s) that you might want to Update (or otherwise modify).
Note that AUTO_INCREMENT has some builtin features that make it less vulnerable to isolation modes. However, a drawback is “burned ids”. For example, an INSERT IGNORE that does not “insert” (due to a dup unique key) will have already allocated the id, and won’t give it back.