.png)
![‘Companion’ Ending Breakdown: Director Drew Hancock Tells All About the Film’s Showdown and Potential Sequel: ‘That’s the Future I Want for [Spoiler]’](https://variety.com/wp-content/uploads/2025/02/MCDCOMP_WB028.jpg?#)
What is the purpose of setting an isolation level for an INSERT INTO statement?
I want to better understand how isolation levels work, and here is my current understanding: Isolation levels determine how a transaction is isolated from concurrent transactions. They are typically described in terms of "read" actions, such as "repeatable read" or "read uncommitted". This suggests that isolation levels control whether your transaction can see uncommitted records from other transactions. In other words, it seems that isolation levels primarily govern how deeply a transaction can "read into" the state of other ongoing transactions. However, my understanding is that the isolation level setting does not dictate whether the current transaction's uncommitted records should be visible to other transactions, but rather whether the current transaction should be able to see uncommitted changes made by others. If my understanding is correct (or at least partially correct), why do many database systems allow you to specify the isolation level at the statement level for operations like INSERT INTO, which do not read data from other transactions (assuming it is not an INSERT INTO ... SELECT, where the isolation level of the SELECT would be relevant)? Some examples and further considerations In DB2 for i series you can execute this statement: CREATE TABLE SCLAUZERO.ISO_LEV_TEST( C1 INT PRIMARY KEY, C2 VARCHAR(16)); INSERT INTO SCLAUZERO.ISO_LEV_TEST (C1, C2) VALUES (0, 'TEST1') WITH RR; Where WITH RR is defined as isolation-clause and in this case sets the isolation level of this INSERT INTO statement to Repeatable Read. A brief research has led me to the following temporary conclusion: Oracle does not have this feature SQL Server has a concept of 'hint' which seems to differ from what I have described Postgres does not have this feature DB2 for LUW has this feature Therefore, from this brief exploration, it appears that the DB2 family introduces the concept of isolation levels for DML statements. I initially used INSERT INTO as an extreme case where the absence of a read is more evident, but upon reconsideration with a more attentive perspective, I would say the following: In both DB2 for i and DB2 LUW, it is possible to specify an isolation level for all DML constraints: INSERT ... WITH UPDATE ... WITH DELETE ... WITH In fact, aside from a "pure" insert (without a select or computed fields such as subselects), it is clear that UPDATE and DELETE must read the table data and may encounter the classic issues of dirty reads, etc. This may be the purpose of this isolation clause: to define the isolation level for implicit reads even in DML commands. This DB2-family feature seems to be a pretty abstract and theoretical feature, because it's very likely that the database will try to use locking to prevent modification by transaction B of a record that is being modified by transaction A. This would explain why most database systems do not consider this 'implicit read' in DML statements. When locking mechanisms are in place, they tend to prevent, for instance, a concurrent UPDATE from even reading the data, thus negating the need to specify what it should see. As you can see, there is a lot of speculation here, and it is frustrating, but I need a conceptual framework before designing tests to verify whether these assumptions are correct.
I want to better understand how isolation levels work, and here is my current understanding:
Isolation levels determine how a transaction is isolated from concurrent transactions. They are typically described in terms of "read" actions, such as "repeatable read" or "read uncommitted". This suggests that isolation levels control whether your transaction can see uncommitted records from other transactions. In other words, it seems that isolation levels primarily govern how deeply a transaction can "read into" the state of other ongoing transactions.
However, my understanding is that the isolation level setting does not dictate whether the current transaction's uncommitted records should be visible to other transactions, but rather whether the current transaction should be able to see uncommitted changes made by others.
If my understanding is correct (or at least partially correct), why do many database systems allow you to specify the isolation level at the statement level for operations like INSERT INTO, which do not read data from other transactions (assuming it is not an INSERT INTO ... SELECT, where the isolation level of the SELECT would be relevant)?
Some examples and further considerations
In DB2 for i series you can execute this statement:
CREATE TABLE SCLAUZERO.ISO_LEV_TEST( C1 INT PRIMARY KEY, C2 VARCHAR(16));
INSERT INTO SCLAUZERO.ISO_LEV_TEST (C1, C2) VALUES (0, 'TEST1') WITH RR;
Where WITH RR is defined as isolation-clause and in this case sets the isolation level of this INSERT INTO statement to Repeatable Read.
A brief research has led me to the following temporary conclusion:
- Oracle does not have this feature
- SQL Server has a concept of 'hint' which seems to differ from what I have described
- Postgres does not have this feature
- DB2 for LUW has this feature
Therefore, from this brief exploration, it appears that the DB2 family introduces the concept of isolation levels for DML statements.
I initially used INSERT INTO as an extreme case where the absence of a read is more evident, but upon reconsideration with a more attentive perspective, I would say the following:
In both DB2 for i and DB2 LUW, it is possible to specify an isolation level for all DML constraints:
- INSERT ... WITH
- UPDATE ... WITH
- DELETE ... WITH
In fact, aside from a "pure" insert (without a select or computed fields such as subselects), it is clear that UPDATE and DELETE must read the table data and may encounter the classic issues of dirty reads, etc. This may be the purpose of this isolation clause: to define the isolation level for implicit reads even in DML commands. This DB2-family feature seems to be a pretty abstract and theoretical feature, because it's very likely that the database will try to use locking to prevent modification by transaction B of a record that is being modified by transaction A.
This would explain why most database systems do not consider this 'implicit read' in DML statements. When locking mechanisms are in place, they tend to prevent, for instance, a concurrent UPDATE from even reading the data, thus negating the need to specify what it should see.
As you can see, there is a lot of speculation here, and it is frustrating, but I need a conceptual framework before designing tests to verify whether these assumptions are correct.
