http://www.oradb.net/sql/sqlplus_003.htm然后看懂下面的进程
Transaction 1 Time Point Transaction 2
LOCK TABLE scott.dept
IN ROW SHARE MODE;
Statement processed 1
2 DROP TABLE scott.dept;
DROP TABLE scott.dept
*
ORA-00054
(exclusive DDL lock not possible
because of T1's table lock)
3 LOCK TABLE scott.dept
IN EXCLUSIVE MODE NOWAIT;
ORA-00054
4 SELECT LOC
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
LOC
- - - - - - -
DALLAS
1 row selected
UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
(waits because T2 has locked same
rows) 5
6 ROLLBACK;
(releases row locks)
1 row processed.
ROLLBACK; 7
LOCK TABLE scott.dept
IN ROW EXCLUSIVE MODE;
Statement processed. 8
9 LOCK TABLE scott.dept
IN EXCLUSIVE MODE
NOWAIT;
ORA-00054
10 LOCK TABLE scott.dept
IN SHARE ROW EXCLUSIVE
MODE NOWAIT;
ORA-00054
11 LOCK TABLE scott.dept
IN SHARE ROW EXCLUSIVE
MODE NOWAIT;
ORA-00054
12 UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
1 row processed.
13 ROLLBACK;
SELECT loc
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
LOC
- - - - - -
DALLAS
1 row selected. 14
15 UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
(waits because T1 has locked same
rows)
ROLLBACK; 16
17 1 row processed.
(conflicting locks were released)
ROLLBACK;
LOCK TABLE scott.dept
IN SHARE MODE
Statement processed 18
19 LOCK TABLE scott.dept
IN EXCLUSIVE MODE NOWAIT;
ORA-00054
20 LOCK TABLE scott.dept
IN SHARE ROW EXCLUSIVE
MODE NOWAIT;
ORA-00054
21 LOCK TABLE scott.dept
IN SHARE MODE;
Statement processed.
22 SELECT loc
FROM scott.dept
WHERE deptno = 20;
LOC
- - - - - -
DALLAS
1 row selected.
23 SELECT loc
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
LOC
- - - - - -
DALLAS
1 row selected.
24 UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
(waits because T1 holds
conflicting table lock)
ROLLBACK; 25
26 1 row processed.
(conflicting table lock released)
ROLLBACK;
LOCK TABLE scott.dept
IN SHARE ROW
EXCLUSIVE MODE;
Statement processed. 27
28 LOCK TABLE scott.dept
IN EXCLUSIVE MODE
NOWAIT;
ORA-00054
29 LOCK TABLE scott.dept
IN SHARE ROW
EXCLUSIVE MODE
NOWAIT;
ORA-00054
30 LOCK TABLE scott.dept
IN SHARE MODE NOWAIT;
ORA-00054
31 LOCK TABLE scott.dept
IN ROW EXCLUSIVE
MODE NOWAIT;
ORA-00054
32 LOCK TABLE scott.dept
IN SHARE MODE NOWAIT;
ORA-00054
33 SELECT loc
FROM scott.dept
WHERE deptno = 20;
LOC
- - - - - -
DALLAS
1 row selected.
34 SELECT loc
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
LOC
- - - - - -
DALLAS
1 row selected.
35 UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
(waits because T1 holds
conflicting table lock)
UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
(waits because T2 has locked same
rows) 36
(deadlock)
Cancel operation
ROLLBACK; 37
38 1 row processed.
LOCK TABLE scott.dept
IN EXCLUSIVE MODE; 39
40 LOCK TABLE scott.dept
IN EXCLUSIVE MODE;
ORA-00054
41 LOCK TABLE scott.dept
IN ROW EXCLUSIVE MODE
NOWAIT;
ORA-00054
42 LOCK TABLE scott.dept
IN SHARE MODE;
ORA-00054
43 LOCK TABLE scott.dept
IN ROW EXCLUSIVE
MODE NOWAIT;
ORA-00054
44 LOCK TABLE scott.dept
IN ROW SHARE MODE
NOWAIT;
ORA-00054
45 SELECT loc
FROM scott.dept
WHERE deptno = 20;
LOC
- - - - - -
DALLAS
1 row selected.
46 SELECT loc
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
(waits because T1 has conflicting
table lock)
UPDATE scott.dept
SET deptno = 30
WHERE deptno = 20;
1 row processed. 47
COMMIT; 48
49 0 rows selected.
(T1 released conflicting lock)
SET TRANSACTION READ ONLY; 50
SELECT loc
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
BOSTON 51
52 UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 10;
1 row processed.
SELECT loc
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
BOSTON
(T1 does not see uncommitted
data) 53
54 COMMIT;
SELECT loc
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
(same results seen even after T2
commits) 55
COMMIT; 56
SELECT loc
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
NEW YORK
(committed data is seen)
Transaction 1 Time Point Transaction 2
LOCK TABLE scott.dept
IN ROW SHARE MODE;
Statement processed 1
2 DROP TABLE scott.dept;
DROP TABLE scott.dept
*
ORA-00054
(exclusive DDL lock not possible
because of T1's table lock)
3 LOCK TABLE scott.dept
IN EXCLUSIVE MODE NOWAIT;
ORA-00054
4 SELECT LOC
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
LOC
- - - - - - -
DALLAS
1 row selected
UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
(waits because T2 has locked same
rows) 5
6 ROLLBACK;
(releases row locks)
1 row processed.
ROLLBACK; 7
LOCK TABLE scott.dept
IN ROW EXCLUSIVE MODE;
Statement processed. 8
9 LOCK TABLE scott.dept
IN EXCLUSIVE MODE
NOWAIT;
ORA-00054
10 LOCK TABLE scott.dept
IN SHARE ROW EXCLUSIVE
MODE NOWAIT;
ORA-00054
11 LOCK TABLE scott.dept
IN SHARE ROW EXCLUSIVE
MODE NOWAIT;
ORA-00054
12 UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
1 row processed.
13 ROLLBACK;
SELECT loc
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
LOC
- - - - - -
DALLAS
1 row selected. 14
15 UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
(waits because T1 has locked same
rows)
ROLLBACK; 16
17 1 row processed.
(conflicting locks were released)
ROLLBACK;
LOCK TABLE scott.dept
IN SHARE MODE
Statement processed 18
19 LOCK TABLE scott.dept
IN EXCLUSIVE MODE NOWAIT;
ORA-00054
20 LOCK TABLE scott.dept
IN SHARE ROW EXCLUSIVE
MODE NOWAIT;
ORA-00054
21 LOCK TABLE scott.dept
IN SHARE MODE;
Statement processed.
22 SELECT loc
FROM scott.dept
WHERE deptno = 20;
LOC
- - - - - -
DALLAS
1 row selected.
23 SELECT loc
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
LOC
- - - - - -
DALLAS
1 row selected.
24 UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
(waits because T1 holds
conflicting table lock)
ROLLBACK; 25
26 1 row processed.
(conflicting table lock released)
ROLLBACK;
LOCK TABLE scott.dept
IN SHARE ROW
EXCLUSIVE MODE;
Statement processed. 27
28 LOCK TABLE scott.dept
IN EXCLUSIVE MODE
NOWAIT;
ORA-00054
29 LOCK TABLE scott.dept
IN SHARE ROW
EXCLUSIVE MODE
NOWAIT;
ORA-00054
30 LOCK TABLE scott.dept
IN SHARE MODE NOWAIT;
ORA-00054
31 LOCK TABLE scott.dept
IN ROW EXCLUSIVE
MODE NOWAIT;
ORA-00054
32 LOCK TABLE scott.dept
IN SHARE MODE NOWAIT;
ORA-00054
33 SELECT loc
FROM scott.dept
WHERE deptno = 20;
LOC
- - - - - -
DALLAS
1 row selected.
34 SELECT loc
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
LOC
- - - - - -
DALLAS
1 row selected.
35 UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
(waits because T1 holds
conflicting table lock)
UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 20;
(waits because T2 has locked same
rows) 36
(deadlock)
Cancel operation
ROLLBACK; 37
38 1 row processed.
LOCK TABLE scott.dept
IN EXCLUSIVE MODE; 39
40 LOCK TABLE scott.dept
IN EXCLUSIVE MODE;
ORA-00054
41 LOCK TABLE scott.dept
IN ROW EXCLUSIVE MODE
NOWAIT;
ORA-00054
42 LOCK TABLE scott.dept
IN SHARE MODE;
ORA-00054
43 LOCK TABLE scott.dept
IN ROW EXCLUSIVE
MODE NOWAIT;
ORA-00054
44 LOCK TABLE scott.dept
IN ROW SHARE MODE
NOWAIT;
ORA-00054
45 SELECT loc
FROM scott.dept
WHERE deptno = 20;
LOC
- - - - - -
DALLAS
1 row selected.
46 SELECT loc
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
(waits because T1 has conflicting
table lock)
UPDATE scott.dept
SET deptno = 30
WHERE deptno = 20;
1 row processed. 47
COMMIT; 48
49 0 rows selected.
(T1 released conflicting lock)
SET TRANSACTION READ ONLY; 50
SELECT loc
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
BOSTON 51
52 UPDATE scott.dept
SET loc = 'NEW YORK'
WHERE deptno = 10;
1 row processed.
SELECT loc
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
BOSTON
(T1 does not see uncommitted
data) 53
54 COMMIT;
SELECT loc
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
(same results seen even after T2
commits) 55
COMMIT; 56
SELECT loc
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
NEW YORK
(committed data is seen)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货