Revisiting Deadlocks And Blocking

ok, about a few months ago I brought up the issues we were having with deadlocks. My company is running a .net data entry application hitting against a sql 2000 box. Forget hardware since we have more then enough hardware to handle the load.

The issue we are seeing is we get a process that blocks a table, and the bulding up of block process that cause timeouts/deadlocks etc. A couple of things we see when this happens is that .net on the web server seems to open up more and more connections(it seems to double or triple the connections as the blocking begins), we start to see deadlocks and we see alot of timeouts in the app due to the blocking.

The blocking aways happen on an insert of an order or a customer. One thing I keep wondering about is how we handle the unique id for our tables. Our DBA had us put in the following:

Begin Trans
select max(id) + 1
insert....
commit trans

I've heard from a few here and in other forums this is the wrong way to go. I'm still get a gut feeling that this could start causing issues like what we are seeing. My problem is I'm not knowledgable enough to make a strong enough case with it.

What I need help is getting enough information to either point to how we manage ID's or at the very least rule them out. I've done searchs in google and some other search engines but there doesnt seem to be any good articles on how best to handle unique ids and show a good case for it. I do know I can use sql identity, or even guid with the newid() function but I need solid proof before my boss will move to one of these methods. Any links you guys can give or even your own write up will really help my case and hopefully help my headache.

Thanks Ahead of time