Lock a table during tests

Question

Post reply

Lock a table during tests

Ray Cacciatore

Ten Centuries

Points: 1260
More actions
February 19, 2010 at 8:26 am

#230191

I have a bug in an application that seems to crash when it's trying to update a row on a table. I know for a fact that the table is being updated by another application at the same time, which is why it crashes, actually it gives a timeout error.
My boss has asked me to reproduce the problem in the test environment. Is there a way to lock a table for an indeterminate amount of time to simulate the process that's inserting records, while my app is updating?
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

sure, here's a basic example.

in any query window with SSMS:

begin tran

select * from example with (tablockx)

the with(TABLOCKX) gives you an excluseive table lock.

now if you try in a second query window to do a select * from example , or with your application, you will see it spin endlessly, or time out due to a connection.

eventually, you'll need to either ROLLBACK or COMMIT your tran, or close the window so SSMS rollsback your tran for you.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 2

You could begin a transaction, update a record and then not commit until you want to end the lock.

Converting oxygen into carbon dioxide, since 1955.

Ray Cacciatore Ten Centuries Points: 1260 More actions · Answer 3

Lowell, I tried your code but somehow I was able to update data in the locked table.

I tried :

BEGIN TRANSACTION

SELECT * FROM tblMyTable WITH (TABLELOCKX)

and I got an error message:

"TABLELOCKX" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

Then I tried :

BEGIN TRANSACTION

SELECT* FROM tblMyTable TABLELOCKX

and it executed without error. But I was able to open another window and run an update on the table successfully.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Try:

BEGIN TRANSACTION

SELECT* FROM tblMyTable with (TABLOCKX)

MJ

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

I wouldn't recommend SELECT * without a WHERE clause for this because it could bring back billions of rows of data. SELECT TOP 1.. will suffice with the TABLOCKX hint. 🙂

What the OP REALLY needs to do however is find out WHY the blocking is happening in the first place. That means evaluating queries for missing indexes and possibly refactoring code to improve query plans and transaction duration.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service