This is said in Oracle7 Server Concepts Manual, Data Concurrency,
Additional Considerations for Serializable Isolation:
---
Both read committed and serializable transactions use row-level locking, and
both will wait if they try to change a row updated by an uncommitted
concurrent transaction. The second transaction that tries to update a given
row waits for the other transaction to commit or rollback and release its
lock. If that other transaction rolls back, the waiting transaction
(regardless of its isolation mode) can proceed to change the previously
locked row, as if the other transaction had not existed.
However, read committed and serializable transactions behave differently if
the other (blocking) transaction commits. When the other transaction commits
and releases its locks, a read committed transaction will proceed with its
intended update...
^^^^^^^^
---
What does this mean? Will Oracle update this row (just updated by other
Xaction)? In any case or only if qualification is ok for this row now
(qual was ok for unchanged version of row but could be changed by
concurrent Xaction)?
Could someone run in Oracle test below?
1. CREATE TABLE test (x integer, y integer)
2. INSERT INTO test VALUES (1, 1);
INSERT INTO test VALUES (1, 2);
INSERT INTO test VALUES (3, 2);
3. run two session T1 and T2 (in read committed mode)
4. in session T2 run
UPDATE test SET x = 1, y = 2 WHERE x <> 1 OR y <> 2;
5. in session T1 run
UPDATE test SET y = 3 WHERE x = 1;
6. in session T2 run
COMMIT;
7. in session T1 run
SELECT * FROM test; -- results?
8. in session T1 run
COMMIT;
9. now in session T2 run
UPDATE test SET x = 2;
10. in session T1 run
UPDATE test SET y = 4 WHERE x = 1;
11. in session T2 run
COMMIT;
12. in session T1 run
SELECT * FROM test; -- results?
TIA,
Vadim
