Demonstrate the effect of automatic shared and exclusive locks, using DML and DDL
commands.
1. Connect to your database with SQL*Plus as user SYSTEM.
2. Create a table, and insert a row into it.
ocp10g> create table t1(c1 number);
Table created.
ocp10g> insert into t1 values(1);
1 row created.
ocp10g> commit;
Commit complete.
3. Open a second session, again connecting with SQL*Plus as user SYSTEM.
4. In session 1, issue a DML command that will take an exclusive lock on the
row and a shared lock on the table.
ocp10g> update t1 set c1=2 where c1=1;
1 row updated.
5. In session 2, issue a DDL statement against the table.
ocp10g> alter table t1 add (c2 date);
alter table t1 add (c2 date)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
The attempt to add a column to the table fails, because the exclusive table
lock necessary for a DDL statement conflicts with the shared lock already
granted for a DML statement. Note that whereas a DML statement will wait
and continually retry until it gets its lock (in other words, it hangs), DDL
statements terminate immediately with an error.
6. In session 1, commit the transaction.
ocp10g> commit;
Commit complete.
7. In session 2, repeat Step 5. This time it will succeed because there are no
shared DML locks blocking the exclusive DDL lock.
8. In session 1, lock the whole table.
ocp10g> lock table t1 in exclusive mode;
Table(s) Locked.
9. In session 2, insert a row. The session will hang.
ocp10g> insert into t1 values (1,sysdate);
10. In session 1, release the table lock by issuing a COMMIT. Note that a
ROLLBACK would do just as well.
ocp10g> commit;
Commit complete.
11. Session 2 will now be released, and the insert will complete; issue a COMMIT
to terminate the transaction and release the row exclusive lock.
12. Leave both sessions open; they will be used in later exercises.   _{Your Comment}