Lock table manually 3

[graabein]

Hi, I want to test how my application handles a deadlock on the SQL Server. Can I manually lock a table on my test database (SQL Server 2000) in Query Analyzer and run my application and debug it while the table stays locked?

Any tips on how to do this?


graabein

 

[ptheriault]

Just because you have a table lock doesn't mean you'll get a deadlock. A deadlock occurs when user A has locks on the resources that user B wants to modify, and user B has locks on the resources that user A intends to modify

The short answer to your question is no.




- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ESquared]

To create a deadlock:

[ol]
[li]Both the following occur:[ul][li]Acquire a lock on a row in Query Analzyer session A.[/li]
[li]Acquire a lock on a row in the same table in session B.[/li][/ul]
[/li]
[li]Then, both the following occur:[ul][li]Attempt to escalate the lock in session A to a table lock.[/li]
[li]Attempt to escalate the lock in session B to a table lock.[/li][/ul]
[/li]
[/ol]

The order of the items under each number is unimportant. At the first action in part 2, a block will occur and the session will wait until the lock from the other session is released.

At the second action in part 2, both processes now have resources locked that block the other from continuing. Unless the server detects this deadlock and kills one process (and rolls it back), the mutual block/lock would never end, locking resources forever.

You could also use two tables instead of one table with escalating lock size:

[ol]
[li]Both the following occur:[ul][li]Session A locks table X.[/li]
[li]Session B locks table Y.[/li][/ul]
[/li]
[li]Then, both the following occur:[ul][li]Session A requests lock on table Y.[/li]
[li]Session B requests lock on table X.[/li][/ul]
[/li]
[/ol]

Deadlock.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[danvlas]

You may try to use 2 transactions that start but do not end (a Stop)
After the code has stopped, and transactions are not yet commited or rolled back,try to modify the data locked by a transaction from the othe one (and the other way round)
To have the necessary time to do it, you'll have to start the transactions from a client program (VB, Delphi, whatever). I don't think QA would let you do that...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html