Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Show Existing Locks on the Database

By: FYIcenter.com

(Continued from previous topic...)

How To View Existing Locks on the Database?

As can see from the pervious tutorial exercise, performance of the second session is greatly affected by the data lock created on the database. To maintain a good performance level for all sessions, you need to monitor the number of data locks on the database, and how long do they last.

Oracle maintains current existing data locks in a Dynamic Performance View called V$LOCK with columns like:

  • SID - Session ID to identify the session that owns this lock.
  • TYPE - The type of the lock, like TM (DML enqueue) and TX (Transaction enqueue).
  • LMODE - The lock mode in which the session holds the lock.
  • REQUEST - The lock mode in which the session requests the lock.
  • CTIME - The time since current lock mode was granted
  • BLOCK - A value of either 0 or 1, depending on whether or not the lock in question is the blocker.

The following tutorial exercise shows you how to view existing locks on the database:

(session 1)
SQL> connect HR/fyicenter

SQL> UPDATE fyi_links 
  SET url='centerfyi.com' 
  WHERE id=110;
1 row updated.
                                  (session 2)
                                  SQL> connect HR/fyicenter

                                  SQL> INSERT INTO fyi_links
                                    (url, id) VALUES 
                                    ('oracle.com', 112);
                                  1 row created.

                                  SQL> UPDATE fyi_links 
                                    SET notes='FYI Resource' 
                                    WHERE id=110;
                                  (wait on lock at id=110)

Now keep those two sessions as is. You need to open a third window to connect to the database as SYSTEM to view all current locks:

(session 3)
SQL> connect SYSTEM/password

SQL> select sid, username from v$session 
  2  where username='HR';
       SID USERNAME
---------- ------------------------------
        23 HR
        39 HR

SQL> SELECT sid, type, lmode, request, ctime, block 
  FROM V$LOCK WHERE sid in (23, 39) ORDER BY ctime DESC;
     SID TY      LMODE    REQUEST      CTIME      BLOCK
    ---- -- ---------- ---------- ---------- ----------
1     39 TX          6          0         84          1
2     39 TM          3          0         84          0
3     23 TM          3          0         27          0
4     23 TX          6          0         27          0
5     23 TX          0          6         18          0

You should read the output as:

  • Line #1 and #2 represent the lock resulted from the UPDATE statement in session #1 on row id=110.
  • Line #3 and #4 represent the lock resulted from the INSERT statement in session #2 on row id=112.
  • Line #5 represents a request of lock resulted from the UPDATE statement in session #2 on row id=110, which is blocked by the lock from line #1 and #2.

(Continued on next topic...)

  1. What Is a Transaction?
  2. How To Start a New Transaction?
  3. How To End the Current Transaction?
  4. How To Create a Test Table for Transaction Testing?
  5. How To Commit the Current Transaction?
  6. How To Rollback the Current Transaction?
  7. What Happens to the Current Transaction If a DDL Statement Is Executed?
  8. What Happens to the Current Transaction If the Session Is Ended?
  9. What Happens to the Current Transaction If the Session Is Killed?
  10. How Does Oracle Handle Read Consistency?
  11. What Is a READ WRITE Transaction?
  12. What Is a READ ONLY Transaction?
  13. How To Set a Transaction To Be READ ONLY?
  14. What Are the Restrictions in a READ ONLY Transaction?
  15. What Are the General Rules on Data Consistency?
  16. What Are Transaction Isolation Levels Supported by Oracle?
  17. What Is a Data Lock?
  18. How Data Locks Are Respected?
  19. How To Experiment a Data Lock?
  20. How To View Existing Locks on the Database?
  21. What Is a Dead Lock?
  22. How Oracle Handles Dead Locks?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...