Let's say we have a big table with hundreds of million of rows (or more).

On one connection, we perform a query requiring full table scan under READ COMMITTED isolation level:

SELECT * FROM bigtable;

On a different connection, we perform an update to a row in the table with auto commit on, slightly after the SELECT statement started executing in the other connection:

UPDATE bigtable SET value_col = <new_value> WHERE pk_col = <key>;

Is the SELECT statement, which is executed under READ COMMITTED isolation level, guaranteed to read all rows as they are in the table when the SELECT statement starts executing? Or could the SELECT statement read the value as updated by the second connection?

  • 1
    You should add which DBMS you are using (Postgres, Oracle, ...). But in general the DBMS should present a consistent view of the data at the point when the statement started. But not all DBMS actually do that. – a_horse_with_no_name Apr 13 '15 at 10:44
  • @a_horse_with_no_name: I have tagged it as database-agnostic, so a quote from the standard would suffice. Whether you want to expand on specific database is up to you :) – nhahtdh Apr 13 '15 at 10:47
  • I support your question because Martin Kleppmann says that the phenomenon is called a read skew. But, read committed says that it locks the DB during the read select. Since you keep the read lock, it should prevent any writes/commits and read skew should be impossible under Read Committed. – Little Alien Dec 6 '16 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.