SQL Server deadlocks

I know this is more IT than computer science, but maybe one of you guys might have an idea.

I've done a bit of Googling, but to little avail.

I'd like to create a deadlock in SQL Server to test another application's behaviour. There are 5 types of deadlock listed in this MSDN article. I've written something for the first one, but and stuck for ideas on how to create a deadlock of the 'worker thread' variety.

Any ideas?

 

Log in or Sign up to hide all adverts.

In webdesign projects it's usually prudent to use a Parser between the database and any client-end application, it's usually for the standard application of sanatization of data inputs but can also stretch to doing deadlock tests. So my simplest suggestion would be to write a parser.

 

Log in or Sign up to hide all adverts.

Not sure I need to parse anything - I've written basic deadlock scripts but some of the rare deadlocks are very hard to cause intentionally.

I'll keep my sql server questions out of sciforums - it's a bit niche

Please Register or Log in to view the hidden image!

 

Log in or Sign up to hide all adverts.

By all means keep them in the forum Blue, I'm sure on occasion someone will happen along that are capable of giving you a more directed response. It's just a little difficult for some to understand what it is you are attempting. I mean the way you make it sound is that you want to allow multiple accesses directly into your SQL from multiple applications, and you want to be able to control the deadlocks they cause. I could of course be wrong in my assumption, but asking if I am will help others understand what it is you are trying to do.

 

It's hard to cause intentionally because it's difficult to directly control the scheduler. What platform are you running (i.e. what language and OS)?

I have quite a bit of concurrency experience, so I might be able to help.

Basically what it's saying is start up several threads. Use one of those to acquire a lock on a data table, then make it wait on a semaphore, or just do a busy-wait. Then the OS (or JVM if you're in Java) will place that thread into the wait queue as it waits for the semaphore to be charged. Use your other thread to make a synchronous request to the same data table that's locked. If you can't do it synchronously, then fake it by using a loop. This second thread will then be placed into the OS's IO-blocked queue. After that blocking call, you have to charge the semaphore to unblock the first thread.

If those are the only two threads in your application, the one that is IO-blocked can't charge the semaphore until the first thread releases the data table. But the first thread can't release the database lock until the second thread increments the semaphore.

There are 4 necessary and sufficient conditions for deadlock to be able to occur in a system:
mutually exclusive resources,
incremental acquisition of these resources,
no pre-emption,
wait-for cycle.

In this case, the database is mutually exclusive through its locking mechanism. The OS enforces the pre-emption policy. You have to ensure, through your code, that there is a wait-for cycle for the requests, and that they hold-and-wait (incremental acquisition).

 

Hi RubiksMaster,

By 'Worker Thread' deadlock, I mean the situation referenced in the linked article. I.e. SQL Server exceeds the limit of worker threads it can generate, so the deadlocked resource is thread availability itself. Pretty hard to get a deadlock in a definite number of steps!

For my most basic deadlock scenarios (which have been completed) I spin off two threads that, as expected, each acquire locks on resources in the wrong order. I Manage the acquiring of resources by the use of fixed delays in each of the scripts, which are started simultaneously. Thus Thread A has resource 1 and wants 2, whilst B has 2 and wants 1. By simply substituting the resource type in the script, I'm able to create about 5 types of simple resource deadlock.

But I think my team wants more! MARS, parallel execution, memory etc as per article. But it's hard to get those resources without screwing the whole computer!

Language is SQL and C#, platform is .NET and all SQL Server versions post 2k.