How to lock a table in SQL 2005

You would ask yourself why anyone would want to lock a table on purpose. Well… We just had an interesting bug related to table locking. A job in SQL was scheduled to run every night and it took over 10 minutes to complete. During this time it was locking a table exclusively. Of course, in response to this, our middle tier was blowing chunks as it tried to insert records into the locked table. Our insert would result in SQL time out and create an unpredictable behavior then on.
I needed to simulate the table lock situation so I could make the code more resilient. At a minimum I wanted to be able to trap the time out and log it. Also, we wanted to rollback everything up to that point as well.




Here are the steps to Lock and Unlock a table


1- Open a window in Microsoft SQL server Management Studio and open a new SQL window:
2- Use the following code as a template to setup your Lock/Unlock mechanism.

Begin transaction
update top (1) Product WITH (TABLOCK)  set Name=’Cheetos’ where ProductID=100
RollBack

3- TO LOCK the table highlight the first two lines (Begin trans + update) and execute them by hitting CTRL+E. At this point table is locked.
4- TO UNLOCK the table highlight the last line (Rollback) and execute it by hitting CTRL+E.


Use the 3 and 4 to repeatedly to lock and unlock the table. Since each time you are rolling back, there will be no change to the content of the table.

After 3 the table Products will be locked. You will not be able to select, insert, update or delete from the table products as long as this transaction is not concluded with a commit or rollback. You may want open a new window in Microsoft SQL server Management Studio and try the following:

select top 1 * from Products

The statement above will hangup and wait for the completion of the transaction we started in step 2.
Similarly, you can run your code and see the same timeout behavior.