If a session has an open transaction executing select for update at isolation levels 1, 2, or 3, a second concurrent session issuing data manipulation language (DML) statements on the same table may be blocked, depending on the type of transaction issued by the second session and its isolation level.
Transaction |
Isolation Level |
|||
|---|---|---|---|---|
0 |
1 |
2 |
3 |
|
1 Adaptive Server does not block select commands at isolation level 1 unless the select list contains a large object (LOB) such as text, image, or unitext. |
||||
2 If the first session issues select for update at isolation level 3, Adaptive Server prevents “phantom rows” by exclusively locking more rows than just the qualifying rows. In this case, Adaptive Server blocks the second session on these additional unqualified rows. |
||||
3 Although the second session issues these DMLs at isolation level 0, Adaptive Server executes them at isolation level 2. |
||||
4Adaptive Server does not support select for update at isolation level 0. |
||||
select qualified rows |
Not blocked |
Not blocked1 |
Blocked |
Blocked |
select unqualified rows |
Not blocked |
Not blocked |
Not blocked2 |
Not blocked2 |
update qualified rows |
Blocked3 |
Blocked |
Blocked |
Blocked |
update unqualified rows |
Not blocked2,3 |
Not blocked2 |
Not blocked2 |
Not blocked2 |
select for update qualified rows |
N/A4 |
Blocked |
Blocked |
Blocked |
select for update unqualified rows |
N/A4 |
Not blocked2 |
Not blocked2 |
Not blocked2 |
delete qualified rows |
Blocked3 |
Blocked |
Blocked |
Blocked |
delete unqualified rows |
Not blocked2,3 |
Not blocked2 |
Not blocked2 |
Not blocked2 |
insert |
Not blocked2,3 |
Not blocked2 |
Not blocked2 |
Not blocked2 |
