Do isolation levels only apply to SELECTS and not UPDATES?
database, mysql, oracle, postgresql
Solution
Assuming you meant to show an "optimistic locking" pattern as used by many ORMs, like:
Thread A: UPDATE ... SET ..., version = 6 WHERE primary_key = 1234 AND version = 5
Thread B: UPDATE ... SET ..., version = 6 primary_key = 1234 AND version = 5
then in all sensible isolation levels (I'm not 100% sure about READ UNCOMMITTED - most DBs don't even support it) thread B will match no rows and have no effect.
In PostgreSQL for example, thread B will initially match the same row as A, but block on a row update lock until thread A commits or rolls back. At this point it'll re-check the condition and find that it no longer matches if thread A committed, so it'll do nothing. The row locking will mean that serialization conflicts never come into play in this particular case.
In any sane database only one of the two updates will succeed - the second will either match zero rows or abort with a serialization failure, depending on the isolation level and DB implementation. This is true even in MySQL with InnoDB (see detailed explanation and demo in this answer) at least in 5.5. If you're using MyISAM then correctness and reliability are clearly not big concerns for you ;-)
I'm not aware of any database that applies different isolation rules to `UPDATE`s vs `SELECT`s. After all, an `UPDATE` needs the same isolation guarantees for its `WHERE` clause, subqueries, etc, as a `SELECT`. `UPDATE`s can deadlock which `SELECT`s can't (in PostgreSQL; apparently they can in MySQL+InnoDB). Unlike `SELECT`, `UPDATE`s are subject to serialization failures in `SERIALIZABLE` isolation mode - but they have the same visibility rules.
PostgreSQL's documentation on concurrency control explains this pretty well.
Problem
Do isolation levels only apply to SELECTS and not UPDATES? Scenario that demonstrated different isolation behavior for SELECTS ``` 1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete 2) 0:02 Thread B runs a query that returns the same 1000 rows 3) 0:05 Thread A updates the last 1 rows in this result set and commits them 4) 0:07 Thread B's query returns* ``` Depending on the isolation level, the result set in #4 will either contain Thread A's changes or it won't. Is the same true for UPDATES? The following is an example scenario: ``` Thread A: UPDATE ... WHERE primary_key = 1234 AND version = 5 Thread B: UPDATE ... WHERE primary_key = 1234 AND version = 5 ``` If both Thread A and Thread B enter their transactions at the same time, and Thread B performs its update after Thread A, will Thread B's update fail or will it "see" the record with version 5 and therefore succeed? Does it depend on the database? e.g. Oracle vs MySql vs PostgreSQL?