Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    16

    Unanswered: locks in a database

    Hi all,

    I have two queries:

    1)I want to know all the locks that are held on a database at any point of
    time. Can any one please help me on that.


    2) Please correct me if I am wrong.
    I want to CHECK and then CHANGE the lock level in my database from
    table level to row level how do I do that?

    TIA
    Ayusman

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Answer to Q1> You can see the db2 reference material for a detailed theory on Lock. Anyway its Exclusive, Shared , Intent to Update.

    Answer to Q2> You cannot change from table level locking to row level locking. on your table if INDEX es are created then row level locking can be expected. Otherwise you can expect a table level locking.
    Default Isolation Level is Cursor Stability. If you want to change that, then before you connect to the database execute the following: db2 "change isolation level to UR" for uncomtted read. CS for cursor stability and so on..

    There were some very good discussions on Locking available in this forum itself. You can search the same for better clarification.
    Last edited by JAYANTA_DATTA; 05-26-06 at 08:20.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Ayusman

    1)I want to know all the locks that are held on a database at any point of
    time. Can any one please help me on that.
    GET SNAPSHOT FOR LOCKS ON <database>

    Quote Originally Posted by Ayusman
    2) Please correct me if I am wrong.
    I want to CHECK and then CHANGE the lock level in my database from
    table level to row level how do I do that?
    Normally DB2 takes row-level locks. Only if the lock list space is exhausted the locks are escalated to the table level. You can only control that indirectly, by changing MAXLOCKS and LOCKLIST parameters, changing the transaction isolation level, and optimizing queries.

    You can force a table-leve lock by issuing the LOCK TABLE statement.

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Quote Originally Posted by n_i
    GET SNAPSHOT FOR LOCKS ON <database>



    Normally DB2 takes row-level locks. Only if the lock list space is exhausted the locks are escalated to the table level. You can only control that indirectly, by changing MAXLOCKS and LOCKLIST parameters, changing the transaction isolation level, and optimizing queries.

    You can force a table-leve lock by issuing the LOCK TABLE statement.
    Theoritically true (as default locking is row-level), but practically have seen many instances where the table is locked where a row-level locking was expected (even when MAXLOCK and LOCKLIST parameters werenot exhausted).

    To get the snapshot you need to turn your monitor for LOCK ON. If you wanna see the applications which have taken locks just try :
    db2 "list applications show detail" | grep Lock
    It also works.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Ayusman
    I want to CHECK and then CHANGE the lock level in my database from table level to row level how do I do that?
    If you're on z/OS, you may use
    ALTER TABLESPACE name LOCKSIZE ROW.
    Add "LOCKMAX 0" if you want to avoid lock escalation (but be careful with this!)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •