Basics of Locks and Blocking Session in Oracle 12c

YouTube video

Introduction

In this article, we will discuss the concept of blocking sessions and locks in Oracle 12c. A blocking session occurs when one session holds a lock on a row or an object and prevents another session from accessing or modifying it. We’ll explore how to identify and handle blocking sessions using SQL queries and techniques. Understanding these concepts is crucial for Oracle DBAs and developers to ensure smooth performance and resolve any issues related to locks.

Step 1: Creating the Scenario

To demonstrate the concept of blocking sessions and locks, let’s create a scenario with two sessions connected as user “Scott.” We will use the “employee” table for this demo.

In the first session, execute an update statement to modify the salary for employee number 101 without committing or rolling back the transaction.

sql
UPDATE employee
SET salary = 7000
WHERE employee_number = 101;

In the second session, execute an update statement to modify the department ID for the same employee.

sql
UPDATE employee
SET department_id = 20
WHERE employee_number = 101;

Observe that the second session hangs and does not proceed with the update. This is because the first session has acquired a lock on the row and has not yet released it.

Step 2: Identifying the Blocking Session

To identify the blocking session, we can query the V$SESSION view using a SQL query.

sql
SELECT blocking_session, sid, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

This query retrieves the blocking session ID, the SID of the blocked session, and the number of seconds it has been waiting. If we run this query, we can see the blocking session and the duration it has been waiting.

Repeat the query intermittently to observe how the “seconds_in_wait” increase, indicating the duration of the waiting period.

Step 3: Determining the Locked Object

To determine which object the lock is held on, we can query the V$LOCK view.

sql
SELECT id1
FROM v$lock
WHERE sid = [blocking session SID];

Replace [blocking session SID] with the SID retrieved from the previous query. The ID1 value represents the object ID where the lock is held.

We can further investigate the locked object using the DBA_OBJECTS view.

sql
SELECT object_name
FROM dba_objects
WHERE object_id = [object ID];

Replace [object ID] with the value obtained from the previous query. In our case, this query will reveal that the lock is on the “employee” table owned by the “Scott” user.

Conclusion

Understanding the concept of blocking sessions and locks is essential for Oracle DBAs and developers. By following the steps mentioned above, you can identify the blocking session, determine the locked object, and resolve any performance issues caused by locks. Monitoring and managing blocking sessions efficiently is crucial in maintaining a robust and efficient Oracle database.

These queries can be valuable tools when diagnosing and resolving issues related to locks and blocking sessions. Remember to run these queries periodically or whenever you encounter slow performance or hanging sessions in your database.