Deadlock and MTS

[herve]

Hi,
When many users run some stored procedures I 've got some deadlocks. How to avoid that?
We run large stored procedures code which are using sometime the same table.
What is the best way for using the transaction isolation level, fillfactor indexes, procedure cache configuration ...etc to avoid that.

In addition, I am using MTS and sometimes the Tempdb is also locked, is it a Microsoft bug (again) ?

Herve Meftah

[David Pierri]

You need to set priorities in your stored procedures to use exclusive record locks. Some applications use (believe it or not) cursors on their code to keep track of updates.
You can also make sure that you checkpoint after running a large stored procedure so that the tempdb and the record itself gets released for updates. It is true it may slow down your server a bit, but you will avoid having deadlocked transactions.

Increase the size of your tempdb so that large transactions can fit in tempdb.

Clean up your stored procedure code so that processes don't hit very large unindexed tables. If tables are indexed, then have a schedule task to rebuild your indexes twice a day.

MTS should help you manage your transactions by streamlining requests to the SQL server, but if the capacity for transaction handling is not there, it does not matter how many MTS's you have.

Sorry I can't give you more, most of these are just simple common sense approaches to having a quick transactional process. Time your stored procedures so that you can have an idea
of how long it is taking your users to run these procedures.
I hope I didn't confuse you.

David


------------
herve at 8/4/99 11:53:49 AM

Hi,
When many users run some stored procedures I 've got some deadlocks. How to avoid that?
We run large stored procedures code which are using sometime the same table.
What is the best way for using the transaction isolation level, fillfactor indexes, procedure cache configuration ...etc to avoid that.

In addition, I am using MTS and sometimes the Tempdb is also locked, is it a Microsoft bug (again) ?

Herve Meftah