Results 1 to 4 of 4

Thread: How to lock

  1. #1
    Join Date
    Feb 2006
    Posts
    5

    Unanswered: How to lock

    Hi,

    I have a school project in which I have to use locking to handle multiple users. Is there a locking command to make Exclusive and Shared locks?

    I'm totally in the dark about this. Any good tutorials you can point me to?

    I'm using "Command Line Processor for DB2 SDK 8.2.2"

    Thanks a lot!
    Sincerely,
    Arthur.

  2. #2
    Join Date
    Dec 2005
    Posts
    39
    Yes, there is a locking command to make use of exclusive/shared locks. To lock a table you need to use -

    LOCK TABLE <name> IN EXCLUSIVE MODE, similarly you can use SHARE for share mode.

    the best documentation would be -

    http://publib.boulder.ibm.com/infoce.../v8//index.jsp

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You may want to look at isolation levels ... CS, RR, RS and UR. You can control the manner in which db2 locks the rows for a SELECT statement using these isolation levels ...

    This is probably the tutorial you want to start with :
    http://www-128.ibm.com/developerwork...11&S_CMP=LPDB2

    Otherwise, locking is internally handled by DB2 ... You don't need to do locking using LOCK TABLE statements ..

    The LOCK TABLE Statements are normally used when you want to avoid row locks escalationing to table lock - rather you issue LOCK at the very beginning ...

    HTH

    Sathyaram

    Quote Originally Posted by bboy_vitasoy
    Hi,

    I have a school project in which I have to use locking to handle multiple users. Is there a locking command to make Exclusive and Shared locks?

    I'm totally in the dark about this. Any good tutorials you can point me to?

    I'm using "Command Line Processor for DB2 SDK 8.2.2"

    Thanks a lot!
    Sincerely,
    Arthur.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 is a “pessimistic locking” database in that it normally does all the locking for you based on the SQL statement that is run (as Sathyaram has said).

    The isolation level determines how long read (share) locks are held and does not normally affect locks taken as a result of insert, update, delete, or select for update. However, a share lock on a row can prevent an update of that row by another application (until the share lock is released), so isolation level can be very important.

    Locks are released when a commit or rollback is issued (explicitly or implicitly).

    The biggest problem you may have in DB2 is to avoid lock waits or deadlocks that occur because of too much locking. Such event can be avoided by careful coding of SQL in the correct order, or the same order by multiple applications accessing the same tables.

    Excessive lock waits or deadlocks can also occur if DB2 automatically escalates locks from row to page level. Do a search on lock escalation in the DB2 forum if you need more info.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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