Q about read committed in Oracle...
Sorry for re-posting - this message has right charset...
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
On Tue, Jul 14, 1998 at 07:14:10PM +0800, Vadim Mikheev wrote:
Could someone run in Oracle test below?
I could, but how do I make Oracle use read committed mode?
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
Michael
--
Dr. Michael Meskes meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!
Dr. Michael Meskes wrote:
On Tue, Jul 14, 1998 at 07:14:10PM +0800, Vadim Mikheev wrote:
Could someone run in Oracle test below?
I could, but how do I make Oracle use read committed mode?
"...You can set the isolation level of a transaction by using
one of these commands at the beginning of a transaction:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ ONLY;
..."
TIA,
Vadim
On Tue, Jul 14, 1998 at 07:14:10PM +0800, Vadim Mikheev wrote:
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;
Blocked until 6 is executed.
6. in session T2 run
COMMIT;
7. in session T1 run
SELECT * FROM test; -- results?
X Y
---------- ----------
1 3
1 3
1 2
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;
Blocked again until after 11. Nothing is updated.
11. in session T2 run
COMMIT;
12. in session T1 run
SELECT * FROM test; -- results?
X Y
---------- ----------
2 3
2 3
2 2
Michael
--
Dr. Michael Meskes meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!
On Tue, Jul 28, 1998 at 04:31:38AM +0800, Vadim Mikheev wrote:
Dr. Michael Meskes wrote:
On Tue, Jul 14, 1998 at 07:14:10PM +0800, Vadim Mikheev wrote:
Could someone run in Oracle test below?
I could, but how do I make Oracle use read committed mode?
"...You can set the isolation level of a transaction by using
one of these commands at the beginning of a transaction:
Hmm, do I have to re-set it after a commit? I didn't do that though.
Shall I re-run?
Michael
--
Dr. Michael Meskes meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!
First, thanks Michael!
It's nice to see expected results but I still have some
new questions - please help!
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
4. in session T2 run
UPDATE test SET x = 1, y = 2 WHERE x <> 1 OR y <> 2;
5. in session T1 run
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE test SET y = 3 WHERE x = 1;
--
-- 1st record will be changed by T2, qual for new record
-- version will be OK, but T1 should be aborted (???)
--
6. in session T2 run
COMMIT;
7. in session T1 run
ROLLBACK; -- just to be sure -:)
8. now in session T2 run
UPDATE test SET x = 2;
9. in session T1 run
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE test SET y = 4 WHERE x = 1 or x = 2;
11. in session T2 run
COMMIT;
12. in session T1 run
SELECT * FROM test; -- results?
^^^^^^^^^^^^^^^^^^
I would like to be sure that T1 will update table...
TIA,
Vadim
Vadim wrote:
It's nice to see expected results but I still have some
new questions - please help!
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
4. in session T2 run
UPDATE test SET x = 1, y = 2 WHERE x <> 1 OR y <> 2;
2 rows updated.
5. in session T1 run
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE test SET y = 3 WHERE x = 1;
blocks
--
-- 1st record will be changed by T2, qual for new record
-- version will be OK, but T1 should be aborted (???)
--
6. in session T2 run
COMMIT;
7. in session T1 run
ROLLBACK; -- just to be sure -:)
UPDATE test SET y = 3 WHERE x = 1
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
SQL> rollback;
Rollback complete.
8. now in session T2 run
UPDATE test SET x = 2;
3 rows updated.
9. in session T1 run
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE test SET y = 4 WHERE x = 1 or x = 2;
blocks
11. in session T2 run
COMMIT;
Commit complete.
in T1: 3 rows updated.
12. in session T1 run
SELECT * FROM test; -- results?
^^^^^^^^^^^^^^^^^^
I would like to be sure that T1 will update table...
X Y
---------- ----------
2 4
2 4
2 4
So it does.
Andreas
Import Notes
Resolved by subject fallback
On Thu, Jul 30, 1998 at 04:40:13PM +0800, Vadim Mikheev wrote:
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
4. in session T2 run
UPDATE test SET x = 1, y = 2 WHERE x <> 1 OR y <> 2;
5. in session T1 run
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE test SET y = 3 WHERE x = 1;
UPDATE test SET y = 3 WHERE x = 1
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
--
-- 1st record will be changed by T2, qual for new record
-- version will be OK, but T1 should be aborted (???)
--
6. in session T2 run
COMMIT;
7. in session T1 run
ROLLBACK; -- just to be sure -:)
8. now in session T2 run
UPDATE test SET x = 2;
9. in session T1 run
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE test SET y = 4 WHERE x = 1 or x = 2;
blocked
11. in session T2 run
COMMIT;
12. in session T1 run
SELECT * FROM test; -- results?
^^^^^^^^^^^^^^^^^^
I would like to be sure that T1 will update table...
X Y
---------- ----------
2 4
2 4
2 4
Michael
--
Dr. Michael Meskes meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!
On Thu, Jul 30, 1998 at 11:32:44AM +0200, Andreas Zeugswetter wrote:
Vadim wrote:
It's nice to see expected results but I still have some
new questions - please help!...
It seems you had the results in the wrong order Andreas.
Michael
--
Dr. Michael Meskes meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!