Results 1 to 13 of 13

Thread: lock table

  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: lock table

    can any tell what is the exact syntax to lock table in a database

    [PHP]






    ankurkaushik@gmail.com

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    according the doc :
    -LOCK TABLE--+-table-name-+--IN--+-SHARE-----+--MODE---------><
    '-nickname---' '-EXCLUSIVE-'
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jun 2007
    Posts
    197

    Smile

    thanks for giving me suggestion its working

    but after locking table i can select table ,insert data,alter ,delete
    so what will be use of locking table??

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ankur02018
    after locking table i can select table ,insert data,alter ,delete
    so what will be use of locking table??
    That's exactly why you would lock the table: so that *you* can insert, delete, etc. in the table, while *others* cannot interfere.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Jun 2007
    Posts
    197

    Smile

    suppose i connect to database as

    connect to cricket user ism using ism

    then

    lock table bcci in share mode

    now I can insert , update , delete etc to that table

    even other user can also do by connecting that particular database
    how can we apply bar to other user on that table by locking

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Locks are released on commit. If you don't switch autocommit off (which I suspect is the case) your lock is released right after the LOCK statement is issued.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by n_i
    Locks are released on commit. If you don't switch autocommit off (which I suspect is the case) your lock is released right after the LOCK statement is issued.
    no i think this is not the case because autocommint is on

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ankur02018
    no i think this is not the case because autocommint is on
    It's really easy to check: just take a locks snapshot after the LOCK statement.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jun 2007
    Posts
    197
    Quote Originally Posted by n_i
    It's really easy to check: just take a locks snapshot after the LOCK statement.
    can you tell in brief how we can check our autocommit status

  10. #10
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by ankur02018
    can you tell in brief how we can check our autocommit status
    yeah I got the command to check your auto commit status


    but cant find find whats use of lock tables ?
    Attached Thumbnails Attached Thumbnails autocommit.JPG  

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you acquire a lock in "shared" mode, then other users will be able to access the table and select from it. They won't be able to write to the table.

    Different question: why exactly do you want to lock the table? DB2 (or any other decent DBMS) will take care of locking for you and your application should not even worry about it.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Jun 2007
    Posts
    197

    Smile

    please make me clear why locking tables

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    First, you don't have to lock tables yourself.

    Sometimes it may be beneficial to explicitly lock a table to prevent DB2 from acquiring row locks which may later on be escalated to a table lock. But this is something you only do if really necessary.
    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
  •