Are race conditions possible in SQL? I would like to understand what the SQL standard says about parallel queries and race conditions. If there is no standard, then I would be interested in the approach adopted by popular databases.

I understand that 2+ read-only queries can run in parallel, but can a read/write or write/write pair run in parallel as well?

For example, suppose a simple aggregation such as

SELECT AVG(col1) FROM tbl;


And while that is running, I will issue an

INSERT INTO tbl (col1) VALUES (3);


Is the INSERT executed while the SELECT is running or will the INSERT be blocked until execution finishes?

In other words, are race-conditions possible in SQL?

Additionally, I would appreciate a discussion on how row, page and table locks are used in this context.

Thanks
According to the SQL standard a single SQL query sees a consistent state of the table(s) it operates on. So it must not see any changes while it's running. But not all DBMS products completely comply with that (notably those that allow dirty reads). For example Postgres or Oracle will not block the INSERT while the SELECT is running (or vice versa). But different DBMS do behave differently in that situation depending on their configuration. - a-horse-with-no-name