All the books I've seen so far, talking about transactions show scenarios with several SQL statements involved.
But what's about single statements? What level of isolation do they have? Is it specified somewhere in a standard? Or does it depend on RDBMS and isolation level?
Let me show a couple of examples.
UPDATE table SET value = value + 1 WHERE id = 1;
This is a composite
read-update-writeoperation. Can a parallel transaction change the value between
writeoperations? Some book states that this operation is atomic (in multithreaded programming meaning) in the most of the RDBMS.
SELECT * FROM table t1 JOIN table t2 USING (id);
If the table is quite large (or query would have some complicated filtering clause), is it possible at some isolation level that
t2.*columns would differ due to parallel update?
SELECT * FROM table1 WHERE id IN (SELECT t_id FROM table2);
Is it possible that some records from
table1are removed after sub-select has been executed? (Here I assume that
table1.idwith cascaded removal.)
Links to the useful manuals that fully explain all the details of transactions are also appreciated.