I would like to understand what the SQL standard says about parallel queries and conditions of competition. If there is no standard, I would be interested in the approach adopted by the popular databases.
I understand that more than 2 read-only queries can be executed in parallel, but can a read / write or write / write pair also run in parallel?
For example, suppose a simple aggregation such as
SELECT AVG (col1) FROM tbl;
And while it's working, I'm going to issue a
INSERT INTO tbl (col1) VALUES (3);
INSERT executed while the
TO SELECT is running or will be the
INSERT to be blocked until the end of the execution?
In other words, race conditions are possible in SQL?
In addition, I would appreciate a discussion of how row, page, and table locks are used in this context.