Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: keeping a lock on table or row

    I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can do it with optimizer hints:

    HOLDLOCK, UPDLOCK, PAGLOCK, TABLOCK, TABLOCKX

  3. #3
    Join Date
    Jan 2004
    Posts
    7
    thanks for your reply!

    Would you elborate for me how to do that, I'm not familiar with SQL Server.

    I would like to be able to do this in a single statement if possible, perhaps something to append to a regular update statement to hold the lock? (I tried appending 'holdlock' to the statement but that dosen't appear to be correct syntax)

    thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not True


    BEGIN TRAN


    Some code.....

    won't COMMIT until you COMMIT the TRAN with COMMIT TRAN....

    Sure in SQL you have to start the transaction..

    and in Oracle that's a setting that you can change...

    Also, I really hope this is not for an application your building and just in SQL+

    In any event getting in and out as FAST as possible should be the way to think...regardless of what you're doing....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    begin tran
    select * from your_table (TABLOCK) where 1=2
    update your_table set...where...
    commit tran

Posting Permissions

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