locked
Lock and unlock the table RRS feed

  • Question

  • Hi,
    How would I lock a table so that the access calls from other applications are put in "wait" by sql server till I unlock ?

    How would I do this ?



    Thanks,
    Fahad
    Friday, February 23, 2007 4:47 PM

Answers

  • There is no "good" way to do what you are looking for. The best you could do would be to start a transaction on the first process and when it is done, COMMIT. 

    The 2nd process would have to be set to:

     SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    Also, you would have to make sure the 2nd process doesn't start before the 1st opens the transaction.  I would schedule them 5 mins apart.

    Monday, February 26, 2007 8:07 PM

All replies

  • TABLOCK table hint

    See SQL Server 2005 Books Online topic Table Hint (Transact-SQL) 

    http://msdn2.microsoft.com/en-US/library/ms187373.aspx

     

    Possibly SERIALIZABLE

    See SQL Server 2005 Books Online topic

    SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

    http://msdn2.microsoft.com/en-US/library/ms173763.aspx

    Friday, February 23, 2007 5:38 PM
  • Could you please explain your problem? Locking entire table hurts concurrency and performance. Maybe there are other ways to do this.
    Friday, February 23, 2007 8:39 PM
  • MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

    What is it you are trying to do and why?

    If you just want to make sure someone doesn't read partially updated data, use a transaction.

    Friday, February 23, 2007 9:32 PM
  •  Tom Phillips wrote:
    MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

    What is it you are trying to do and why?

    If you just want to make sure someone doesn't read partially updated data, use a transaction.



    I need a synchronization between two processes which are accessing a table, both are initiated with a second of difference, one prepares data for other and other consumes it. I want 2nd one to wait till 1st one is done. I dont wanna spend hours to do mutexes and semaphores things, I wonder if I could utilize this cool and time-saving feature of MSSQL, Performance is not a problem. These processes will run at midnight.


    Thankyou

    Monday, February 26, 2007 2:34 PM
  • There is no "good" way to do what you are looking for. The best you could do would be to start a transaction on the first process and when it is done, COMMIT. 

    The 2nd process would have to be set to:

     SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    Also, you would have to make sure the 2nd process doesn't start before the 1st opens the transaction.  I would schedule them 5 mins apart.

    Monday, February 26, 2007 8:07 PM