* ORA-00054 (exclusive DDL lock not possible because of T1’s table lock) 3 LOCK TABLE scott...

Strona startowa Ludzie pragną czasami się rozstawać, żeby móc tęsknić, czekać i cieszyć się z powrotem.soon as possible, but it may not be possible for the socketresources to be released until the connection has completed(and then been reset) or timed out...Vocabulary mihi - for me mensam - table oculus - eye juventutem - youth beatus - blessed vir - man Deus -...Table 3...liśmyHolmes wyciągnął rękę po rękopis i rozłożył go na kolanach...xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxcdziało, zapis się urwał...E$GPVTG,26ręce 
 

Ludzie pragną czasami się rozstawać, żeby móc tęsknić, czekać i cieszyć się z powrotem.

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
5
SET loc = ’NEW YORK’
WHERE deptno =
20;
(waits because T2 has
locked same rows)
6
ROLLBACK;
(releases row locks)
1 row processed.
7
ROLLBACK;
LOCK TABLE
8
scott.dept
IN ROW EXCLUSIVE
MODE;
Statement processed.
Data Concurrency and Consistency 22-31
Transaction 1
Time
Transaction 2
Point
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
14
FROM scott.dept
WHERE deptno = 20
FOR UPDATE OF loc;
LOC
- - - - - -
DALLAS 1 row
selected.
15
UPDATE scott.dept
SET loc = ’NEW YORK’
WHERE deptno = 20;
(waits because T1 has
locked same rows)
ROLLBACK;
16
22-32 Oracle8 Server Concepts
Transaction 1
Time
Transaction 2
Point
17
1 row processed.
(conflicting locks
were released)
ROLLBACK;
LOCK TABLE scott.dept
18
IN ROW SHARE MODE
Statement processed
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.
Data Concurrency and Consistency 22-33
Transaction 1
Time
Transaction 2
Point
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
27
IN SHARE ROW
EXCLUSIVE MODE;
Statement processed.
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
22-34 Oracle8 Server Concepts
Transaction 1
Time
Transaction 2
Point
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
36
SET loc = ’NEW YORK’
WHERE deptno = 20;
(deadlock)
(waits because T2 has
locked same rows)
Cancel operation
37
ROLLBACK;
38
1 row processed
LOCK TABLE scott.dept
39
IN EXCLUSIVE MODE;
Data Concurrency and Consistency 22-35
Transaction 1
Time
Transaction 2
Point
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
47
SET deptno = 30
WHERE deptno = 20;
1 row processed.
22-36 Oracle8 Server Concepts
Transaction 1
Time
Transaction 2
Point
COMMIT;
48
49
0 rows selected.
(T1 released
conflicting lock)
SET TRANSACTION READ
50
ONLY;
SELECT loc
51
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
BOSTON
52
UPDATE scott.dept
SET loc = ’NEW YORK’
WHERE deptno = 10;
1 row processed.
SELECT loc
53
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
(T1 does not see
uncommitted data)
54
COMMIT;
SELECT loc
55
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
(same results seen
even after T2 commits)
COMMIT;
56
Data Concurrency and Consistency 22-37
Transaction 1
Time
Transaction 2
Point
SELECT loc
57
FROM scott.dept
WHERE deptno = 10;
LOC
- - - - - -
NEW YORK
(committed data is
seen)
Oracle Lock Management Services
With Oracle Lock Management services, an application developer can include
statements in PL/SQL blocks that
•
request a lock of a specific type
•
give the lock a unique name recognizable in another procedure in the
same or in another instance
•
change the lock type
•
release the lock
Because a reserved user lock is the same as an Oracle lock, it has all the Oracle lock functionality including deadlock detection. User locks never conflict with Oracle locks, because they are identified with the prefix “UL”.
The Oracle Lock Management services are available through procedures in the
DBMS_LOCK package. For more information about Oracle Lock Management
services, see the Oracle8 Server Application Developer’s Guide.
22-38 Oracle8 Server Concepts
C H A P T E R
23 Data Integrity