Scenario that might demonstrate different behavior for SELECTS depending on the isolation level:
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... set version=6 ... WHERE primary_key = 1234 AND version = 5 Thread B: UPDATE... set version=6 ... 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 to update any rows or will it "see" the record with version 5 and therefore overwrite what Thread A wrote?
Does it depend on the database? e.g. Oracle vs MySql vs PostgreSQL?