Detecting Lock Conflicts

Detecting locks in Oracle 10g using the EM Database Control makes your job easy; no need to query against V$SESSION, V$TRANSACTION, V$LOCK, and V$LOCKED_OBJECT to see who is locking what resource. In Figure 7-2, you can see the tables locked by the user SCOTT after executing the following statement:

SQL> lock table hr.employees, hr.departments
2 in exclusive mode;
Table(s) Locked.

Figure 7-2: The Database Locks screen in EM Database Control

SCOTT has an EXCLUSIVE lock on both the EMPLOYEES and DEPARTMENTS table. You can drill down on the locked object by clicking one of the links in the Object Name column; similarly, you can review other information about SCOTT’s session by clicking one of the links in the Session ID column.

Need for Database Locks

1) Boss A requests the "Smith" employee row.

2) The Boss sees that Smith's performance rating is a "B" and prepares to give Smith a 5% pay raise..
Boss B accesses the "Smith" employee row.

3) Boss B changes Smith's performance_rating to "10" (making Mr. or Ms. Smith deserving of a 10% raise.)

4) Boss A, is unaware that Boss B has changed the performance_rating and gives Smith a 5% raise.