Database Reference
In-Depth Information
SQL> SELECT s.sid, s.serial#, s.event, t.start_time, t.status
FROM v$transaction t, v$session s
WHERE s.taddr=t.addr
AND s.sid=139;
SID SERIAL# START_TIME STATUS
---------- ---------- -------------------- ----------------
139 74 07/30/07 15:34:25 ACTIVE
SQL> SELECT employee_id FROM hr.employees WHERE employee_id=180 FOR UPDATE NOWAIT;
EMPLOYEE_ID
-----------
180
As
APP_USER
, try to delete the row in
EMPLOYEES
with
EMPLOYEE_ID=180
. The session has to
wait for the DBA to release the lock.
DBB> DELETE FROM hr.employees WHERE employee_id=180
/
As a DBA, mark APP_USER's session for disconnection.
SQL> ALTER SYSTEM DISCONNECT SESSION '139,74' POST_TRANSACTION;
System altered.
As a DBA, verify that the transaction is still active and that APP_USER is still waiting for the
row lock, then
COMMIT
, thus releasing the lock on
EMPLOYEES
.
SQL> SELECT s.sid, s.serial#, s.event, t.start_time, t.status
FROM v$transaction t, v$session s
WHERE s.taddr=t.addr
AND s.sid=139;
SID SERIAL# EVENT START_TIME STATUS
--- ------- ----------------------------- ----------------- ------
139 74 enq: TX - row lock contention 07/30/07 15:34:25 ACTIVE
SQL> COMMIT;
Commit complete.
In the other window,
dbb.pl
displays the number of rows processed, which we respond to
with a
COMMIT
statement.
1 Row(s) Processed.
DBB> COMMIT
/
0 Row(s) Processed.
Right after the
COMMIT
, the DBMS disconnects APP_USER's session. The reconnect due to
TAF starts a new session.
DBB> SET ROLE SELECT_CATALOG_ROLE
/
0 Row(s) Processed.
DBB> SELECT sid, serial#, logon_time, client_identifier, module, action, failed_over
