Locking and Latching - Expert Oracle Database Architecture - page 224

is still active. If the lock is not active, the session is allowed access to the data. If the lock is still active, that session will
ask to be notified as soon as the lock is released. Hence, you have a queuing mechanism: the session requesting the
lock will be queued up waiting for that transaction to complete, and then it will get the data.
Here is a small example showing how this happens, using three V$ tables:
•

V$TRANSACTION , which contains an entry for every active transaction.

•

V$SESSION , which shows the sessions logged in.

•

V$LOCK , which contains an entry for all enqueue locks being held as well as for sessions that
are waiting on locks. You will not see a row in this view for each row locked by a session. As
stated earlier, that master list of locks at the row level doesn't exist. If a session has one row in
the EMP table locked, there will be one row in this view for that session indicating that fact. If a
session has millions of rows in the EMP table locked, there will still be just one row in this view.
This view shows what enqueue locks individual sessions have.

First, let's get a copy of the EMP and DEPT tables. If you already have them in your schema, replace them with the
following definitions:

EODA@ORA12CR1> create table dept
2 as select * from scott.dept;
Table created.

EODA@ORA12CR1> create table emp
2 as select * from scott.emp;
Table created.

EODA@ORA12CR1> alter table dept
2 add constraint dept_pk
3 primary key(deptno);
Table altered.

EODA@ORA12CR1> alter table emp
2 add constraint emp_pk
3 primary key(empno);
Table altered.

EODA@ORA12CR1> alter table emp
2 add constraint emp_fk_dept
3 foreign key (deptno)
4 references dept(deptno);
Table altered.

EODA@ORA12CR1> create index emp_deptno_idx
2 on emp(deptno);
Index created.

Let's start a transaction now:

EODA@ORA12CR1> update dept
2 set dname = initcap(dname);
4 rows updated.