Hands-On 11 (Monitoring and Detecting Lock Contention)

As a DBA, you are responsible for monitoring and detecting a lock contention in case of a performance problem. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Monitoring and detecting a lock contention

Locking a table in the exclusive mode

Locking a table in the shared mode

Displaying locks contention

Killing a session

Using the V$LOCK view

Using the DBA_OBJECTS view

Using the V$LOCKED_OBJECT view

Using the V$SESSION view

The SID column

The SERIAL# column

Types of locks

TX-Transaction Enqueue

TM-DML Enqueue

UL- User Supplied

Commands:

LOCK TABLE IN EXCLUSIVE MODE

LOCK TABLE IN SHARE MODE

ALTER SYSTEM KILL SESSION

-- Hands-On 11 (Monitoring and Detecting Lock Contention)
-- Monitoring and Detecting Lock Contention.
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a40
col parameter format a40
col username format a10
col object_name format a20
col owner format a10
col type format a4
pause

--Start

CLEAR SCR
-- In this exercise you will learn how to monitor
-- and detect a lock contention, lock a table exclusively,
-- query locks contention, and kill a session.

-- First, connect to SQLPlus as the SYSTEM/MANAGER user.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- The Oracle server automatically manages object locking, 
-- so most application developers don't need to focus
-- on lock management.

-- We can lock a table manually.
-- Lock the iself.emp table exclusively.

pause

LOCK TABLE iself.emp IN EXCLUSIVE MODE
/

-- Now, the EMP table is in the exclusive mode.
-- We strongly advise you not to lock any tables
-- exclusively unless you have to.

pause

CLEAR SCR
-- Lock the iself.emp table in the shared mode.

pause

LOCK TABLE iself.emp IN SHARE MODE
/

-- Now, the iself.emp table is in the shared mode.

pause

CLEAR SCR
-- Go to another session and login as the iself user.
-- Update an employee record. (UPDATE emp SET sal = 1000
-- WHERE empno = 7788;


pause
pause


CLEAR SCR
-- Query the V$LOCK view to show information about locked objects.

-- Notice that the TYPE column can be TX, TM, and UL.
-- If TYPE equals TX, it means TRANSACTION ENQUEUE.
-- If TYPE equals TM, it means DML ENQUEUE.
-- If TYPE equals UL, it means USER SUPPLIED.

pause


SELECT o.owner, o.object_name, o.object_type, l.type
FROM dba_objects o, v$lock l
WHERE o.object_id = l.id1
AND o.owner = 'ISELF'
/

-- Notice that the EMP table is in the EXCLUSIVE lock.

pause

CLEAR SCR
-- Query the V$LOCKED_OBJECT view to show locks
-- acquired by transactions in the system.

pause

SELECT oracle_username, object_id, session_id
FROM v$locked_object
/

-- Notice that the SYS user is locking the ISELF user.
-- Please take a note on its SESSION_ID column.

pause

CLEAR SCR
-- Check if the session has a locked transaction.

pause

SELECT username, sid, serial#
FROM v$session
WHERE sid IN (SELECT session_id FROM v$locked_object)
/

-- Note the SID and SERIAL# columns.

pause

CLEAR SCR
-- Kill the insignificant session.

pause

ALTER SYSTEM KILL SESSION '&SID,&SERIAL'
/

-- The session was killed.

pause

CLEAR SCR
-- Query the V$LOCKED_OBJECT view again.

pause

SELECT oracle_username, object_id, session_id
FROM v$locked_object
WHERE oracle_username <> 'SYS'
/

-- No locked objects. This is one way to resolve
-- lock contention.
-- 

pause


CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause