How the single SQL statement is isolated

concurrencyconsistencyisolation-leveltransaction

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.

  1. UPDATE table SET value = value + 1 WHERE id = 1;

    This is a composite read-update-write operation. Can a parallel transaction change the value between read and write operations? Some book states that this operation is atomic (in multithreaded programming meaning) in the most of the RDBMS.

  2. 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 t1.* and t2.* columns would differ due to parallel update?

  3. SELECT * FROM table1 WHERE id IN (SELECT t_id FROM table2);

    Is it possible that some records from table1 are removed after sub-select has been executed? (Here I assume that table2.t_id references table1.id with cascaded removal.)

  4. CTE…

Links to the useful manuals that fully explain all the details of transactions are also appreciated.

Best Answer

For SQL Server:

But what's about single statements?

In SQL Server, there is 4 transaction isolation levels (in pessimistic locking model). Default transaction isolation level is Read Committed, and locks are placed on a statement level. If you have a transaction that has 2 statements inside it that retrieve same data, and in the middle of that transaction (right after 1st statement but before 2nd statement started) some other transaction modifies the data that is retrieved by first transaction, then first transaction will return different data in 1st and 2nd statements.

That is known as non-repeatable read problem. This can be solved by increasing transaction isolation level to REPEATABLE READ where locking happens on transaction level (not on a statement level)

  1. UPDATE table SET value = value + 1 WHERE id = 1;

Can a parallel transaction change the value ?

No it can't. This is a single statement, and during it Exclusive lock is placed on rows that are being modified.
No other transactions or statements can modify these rows until our update statement is complete

  1. SELECT * FROM table t1 JOIN table t2 USING (id);

is it possible at some isolation level that t1.* and t2.* columns would differ due to parallel update?

Yes, in READ UNCOMMITED is possible (or when with (NOLOCK) hint is specified), since Shared lock is not applied. In other isolation levels - no, because Shared lock is being placed on rows from t1 and t2, and during it rows can't be modified (Exclusive lock can't be acquired by other transactions)

  1. SELECT * FROM table1 WHERE id IN (SELECT t_id FROM table2)

Is it possible that some records from table1 are removed after sub-select has been executed?

In READ UNCOMMITTED is possible. In higher isolation levels - not possible, because during execution of this statement, Shared lock is placed on all selected rows of table1 and table2, and no other transaction is able to modify the rows of table1 and table2 (put Exclusive lock) until our select statement completes and Shared lock released from table1 and table2

Here you can read more information about SQL Server's isolation levels: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15

Related Question