Database Blocking Sessions

Database Blocking Sessions
It is easy to fool people than to convince them that they are fools

In oracle, we often fall into performance issues due to problems inherited from sessions which block each other.

For a dba, blocking is often a problem which should be identified and solved with the confirmation from application team.

This is due to DML transactions on the table rows and locking the table to avoid other users modifying the table until the first user issues commit.

Hence oracle has a very good feature called Multiversion concurrency control(MVCC)

Sounds confusing ?Lets give an example to understand.

Say there are two users test1 and dev1.

I create a new table called emp and inserting some values to it.

SQL>create table emp( emp_id INTEGER NOT NULL PRIMARY KEY, emp_name VARCHAR2(100) NOT NULL, sur_name VARCHAR2(100) NOT NULL, role VARCHAR2(20), dob DATE )

SQL>insert into emp values(1, 'abc', 'cba', 'b', dr'1990-04-10')

SQL>insert into emp values(2, 'xyz', 'zyx', 'y', dr'1999-09-18')

Then select the rows from the table and two rows created under emp table.

SQL>select * from emp;

Create two test users.

SQL>create user test1 identified by password;

SQL>create user dev1 identified by password;

grant emp table access to the users test1 and dev1.

SQL>grant connect,resource,select,insert,update on sys.emp to dev1;

SQL>grant connect,resource,select,insert,update on sys.emp to test1;

connect to test1 user

SQL>conn test1

update role column on emp table

SQL>update emp set role=eng where emp_id=2;

connect to dev1 user and update same column with different value

SQL>conn dev1

SQL>update emp set role=eng1 where emp_id=2;

Now two users are trying to modify the same column and same row

The test1 user modified the rows and not commited the transaction

This creates a lock on the table until the user issues a commit

when dev1 user also modify the role column where emp_id=2 ,the session gets hung until test1 user releases the lock with commit.This also causes performance issues if multiple DML transactions occur

identify the blocking sessions with query

SQL>select sid,serial#,username,blocking_session,wait_class,seconds_in_wait

Warning !!! : In production databases ,once there is a confirmation from application team we can kill the sessions. Dont kill the session without any proper clarity.

Get the sid and serial# from v$session and apply them

SQL>alter system kill session '&sid,&serial#';

Once the test1 user session killed ,the dev1 user session can modify the row and should issue a commit

“””This post is for learning purpose don’t blindly implement in production”””

Leave a Reply

%d bloggers like this: