Logo Background

Oracle SQL Table Lock

  • By on January 22, 2010 | No Comments

    The TABLE LOCK is a process in database that hold the table from other SQL update-insert-delete process session. Acquiring a lock on a table will helps to maintain the data integrity of a table.

    The downfall of the Oracle table locking was sometimes the process hold the locking for long time causing the other session couldn’t perform the SQL update-insert-delete.

    If your users are complaining that they have to wait for their transactions to complete or the error ‘Could not reserve records [2 tries]. Keep trying?’, you may want to find out if object locking on the server is contributing to this problem.

    The SQL command as below was used to detect the table dead lock in Oracle database and you might want to explore on how to reduce or remove the database lock.

    SELECT /*+ RULE */ b.inst_id,a.object_id obj_id,a.owner||'.'||
    a.object_name "object_name",
    b.username, b.sid,
    b.serial#, d.spid, 
    b.status,e.ctime,c.LOCKED_MODE
    FROM dba_objects a, gv$session b, 
    gv$locked_object c, gv$process d, gv$lock e
    WHERE c.session_id=b.sid AND a.object_id=c.object_id 
    AND d.addr=b.paddr AND e.type='TM' 
    AND e.id1=c.object_id AND e.sid=b.sid AND e.inst_id=b.inst_id 
    AND c.inst_id=b.inst_id
    AND b.inst_id=d.inst_id
    --and a.owner = 'ONT'
    ORDER BY sid;

    To remove the table locking, you can either cancel the running concurrent program or use the alter session kill (not recommended unless you are sure on what you are doing).

    Previous
    Next
    » SQL Cache And Library Cache Hit Ratio
Leave a Comment