Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2008
    Posts
    12

    Unanswered: How to obtain a lock on the table

    Hi,

    I have written a DB2 stored procedure which updates the values of a certain column in the database currently my stored procedure has the followin query.

    UPDATE
    Table
    SET
    column_nm= IN_NEW_FIELD_VALUE
    WHERE
    column_nm = IN_OLD_FIELD_VALUE;


    Now I need to update my stored procedure so that I can obtain a lock on the table so that no one else can access the table when it is getting updated.

    Please suggest me a way how can I obtain a table level lock.


    Thanks
    Arun

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    there is a lock table command: see
    http://publib.boulder.ibm.com/infoce...c/r0000972.htm
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Different question: why exactly do you want to lock the table? I'm asking because the DBMS will automatically lock whatever needs to be locked. Maybe you are searching for a solution for a non-existing problem...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Mar 2008
    Posts
    12
    Hi,

    I am also under the impression that the tables will be implicitely locked by the DBMS but the thing is that I am performing update in more than one table in a single stored procedure call.

    So my question is whether DBMS will have the lock on the table which is being updated or it will have the lock on all the tables in the SP unless the execution of SP completes.

    If it is having the lock only at the table being updated then I will have to obtain the lock explicitely as I want my stored procedure to execute as a single atomic transaction.

    Please suggest do I realy need to have a lock. If yes, how?

    Thanks
    Arun

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    By default, DB2 for Linux, UNIX, Windows locks at the row level. When you do an update, insert, or delete, the row is locked in exclusive mode (X). Locks are not released until you issue a commit (or when the calling program issues a commit). Therefore, all your updates will be in a single unit or work (or single atomic transaction) until you commit.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Mar 2008
    Posts
    12

    How to obtain a lock on the table

    Hi

    Thanks for such a quick reply.

    I just need to understand if implicit locking is being done by DBMS itself, then in which case do we need to lock the table explicitely and how can we do so?

    Thanks
    Arun

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is unlikely that you need to lock the entire table. But if you do, see the LOCK TABLE statement in the SQL Reference Vol 2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's actually a basic principle of database systems to prevent "lost update", "read uncommitted", "non-repeatable read", and "phantom" problems. (If you don't know what I mean, have a look at some good standard database book or search a bit on the web.) There are different techniques to prevent the mentioned problems. Also read up on "isolation levels" because that is the mechanism to influence which of those issues you can live with in your application in order to get better concurrency.

    Now, DB2 will lock everything that you access or modify in your database - regardless of the table in which the respective data resides.

    The only reason where you want to lock a table explicitly is when you update nearly the complete table and want to avoid lock escalations. If you don't know what lock escalations are, you will not need the LOCK TABLE statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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