If a session has an open transaction that is 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.
The states of transactions in second concurrent session are:
|
Transaction |
Isolation Level |
|||
|---|---|---|---|---|
|
0 |
1 |
2 |
3 |
|
|
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 |
1SAP ASE 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, SAP ASE prevents “phantom rows” by exclusively locking more rows than just the qualifying rows. In this case, SAP ASE blocks the second session on these additional unqualified rows.
3 Although the second session issues these DMLs at isolation level 0, SAP ASE executes them at isolation level 2.
4SAP ASE does not support select for update at isolation level 0.