Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Locking Table While Insert

    Hi,
    I want to insert a record in a table having an identity column as primary key. I want to lock the table while inserting. so that no one should be able to insert, select, update, delete from the table. and once my insert is over, then will release the lock.


    Can I have the code for the same. M using SQL SERVER 2005.



    Thanks,
    Rahul Jha

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why why why why why?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Because while more than 1 user is trying to insert at the same time, there should not be any clash of Identity.........


    Thanks,
    Rahul Jha

  4. #4
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    No one is here to help me out................. Kindly Help.........

  5. #5
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by DBA_Rahul
    Because while more than 1 user is trying to insert at the same time, there should not be any clash of Identity.........
    You don't have to worry about that; that won't happen.

  6. #6
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Are you sure that won't happen? That means the table will be locked when any Insert or Update operation is happening? rgt?



    Thanks,
    Rahul Jha

  7. #7
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by DBA_Rahul
    Are you sure that won't happen? That means the table will be locked when any Insert or Update operation is happening? rgt?
    It means that an Identity column will never generate the same number twice.

  8. #8
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    OK, Given a different scenario, what if there si no identity column in the table and then I am trying to insert the record with out locking the table?


    Thanks,
    Rahul Jha

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    then you do not have to worry about duplicate identities.

    what no one is trying to explain to you is that MS SQL Server's locking scheme is quite adequate for the vast majority of situations. If you establish an exclusive lock on the whole table, all you will accomplish is the blocking of over spids trying to read that table and you will encounter nasty performace problems. You should do some reading about how SQL Server handles locking. The Inside SQL Server 2005 series of books goes into grueling detail. Also check BOL.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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