Minimizing Deadlocks with M$-Sql server


2008-06-19 05:26:57 PM
delphi43
Hello,
We recently enabled our app to use MS-SQL 2005. Previously our main DB
has been InterBase and Firebird.
In IB/FB work everything runs so smoothly (because of versioning??), but
we hit very often into deadlocks (and other mysteries) in M$-SQL server
(and of course we can not reproduce it in our test environment :( ).
which would be the settings (or coding schemes) in the DBX4, which would
minimize the deadlocks.
Hope that I don't have to implement some update/insert ->if deadlock
->re insert/update mechanism.
If someone could enlighten me what different ways I can minimize
deadlocks and maybe ways I can handle em' (does not matter how it should
be done, but at least I'd know the some of the options on this).
1. Like which transaction isolation I should use (for all queries)
and/or selects, should I mar some tables (internally) in list of "static
tables" which would enable read uncommitted transaction.
2. Can I set the deadlock priorities with DBX4, important
updates/inserts would go trough and ones with lesser importance would
just retry (this would be quite simple to implement). As far as I know
there is such was in MS-SQL server supported
3. Any other things I can make by redefining the DB some how (can't do
much restructuring, but at least in FB/IB it is quite optimal), meaning
that should secondary indexes etc be defined with some other keywords etc...
-Tommi Prami-