Deadlock issues

I'm trying to eliminate (or at least reduce) deadlock issues. I've already
ensured all stored procedures are accessing tables in the same order and now
I am looking at locks and transaction levels. Are row level locks the
default for stored procedures in SQL Server 2000 or do I need to issue some
command to set the default to row level? Should implementing row level
locking and reducing my current transaction isolation levels from
Serializable to RepeatableRead yield any noticable difference in reducting
deadlocks? I realize this is a bit vague but I'm a VB developer tasked
with running the new SQL Server so any best practices or suggestions on this
subject are very welcome.Row level locking is the norm as long as you have proper indexes to access
the rows by. But you will definitely see more deadlocks if you are using
serializable isolation level. Read Committed is the default and should be
used where ever possible. There are very few times when you should actually
need serializable or even repeatable read isolation levels.
Andrew J. Kelly SQL MVP
"John Cobb" <john.cobb@.acxiom.com> wrote in message
news:u2oQRNPTFHA.2676@.TK2MSFTNGP10.phx.gbl...
> I'm trying to eliminate (or at least reduce) deadlock issues. I've
> already
> ensured all stored procedures are accessing tables in the same order and
> now
> I am looking at locks and transaction levels. Are row level locks the
> default for stored procedures in SQL Server 2000 or do I need to issue
> some
> command to set the default to row level? Should implementing row level
> locking and reducing my current transaction isolation levels from
> Serializable to RepeatableRead yield any noticable difference in reducting
> deadlocks? I realize this is a bit vague but I'm a VB developer tasked
> with running the new SQL Server so any best practices or suggestions on
> this
> subject are very welcome.
>
>
>|||In addition to Andrew's comment, you might want to take a look at these:
http://support.microsoft.com/kb/169960
http://support.microsoft.com/kb/224453
http://support.microsoft.com/kb/75722
-oj
"John Cobb" <john.cobb@.acxiom.com> wrote in message
news:u2oQRNPTFHA.2676@.TK2MSFTNGP10.phx.gbl...
> I'm trying to eliminate (or at least reduce) deadlock issues. I've
> already
> ensured all stored procedures are accessing tables in the same order and
> now
> I am looking at locks and transaction levels. Are row level locks the
> default for stored procedures in SQL Server 2000 or do I need to issue
> some
> command to set the default to row level? Should implementing row level
> locking and reducing my current transaction isolation levels from
> Serializable to RepeatableRead yield any noticable difference in reducting
> deadlocks? I realize this is a bit vague but I'm a VB developer tasked
> with running the new SQL Server so any best practices or suggestions on
> this
> subject are very welcome.
>
>
>|||Some of my tables have primary keys defined but not explicit indexes. Will
the PKs allow row level locking or do I explicitly need to define indexes.
Probably will do this anyway for performance but just curious.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Osp2YVPTFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Row level locking is the norm as long as you have proper indexes to access
> the rows by. But you will definitely see more deadlocks if you are using
> serializable isolation level. Read Committed is the default and should be
> used where ever possible. There are very few times when you should
actually
> need serializable or even repeatable read isolation levels.
> --
> Andrew J. Kelly SQL MVP
>
> "John Cobb" <john.cobb@.acxiom.com> wrote in message
> news:u2oQRNPTFHA.2676@.TK2MSFTNGP10.phx.gbl...
reducting
>|||PK constraints will build an index to enforce the constraint and will
function just like any other index (plus the constraint part). So they will
allow row level locking if you are using the PK in the WHERE clause as your
SARG. Rarely is the table accessed solely by the PK. You may require other
indexes to access and lock the table properly.
Andrew J. Kelly SQL MVP
"John Cobb" <john.cobb@.acxiom.com> wrote in message
news:uQ28GYAUFHA.4092@.TK2MSFTNGP12.phx.gbl...
> Some of my tables have primary keys defined but not explicit indexes. Will
> the PKs allow row level locking or do I explicitly need to define indexes.
> Probably will do this anyway for performance but just curious.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:Osp2YVPTFHA.3176@.TK2MSFTNGP09.phx.gbl...
> actually
> reducting
>sql