Lock and unlock the table

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

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

|||Could you please explain your problem? Locking entire table hurts concurrency and performance. Maybe there are other ways to do this.|||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.

|||

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|||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.