|
|
Posted by shark on 10/02/04 11:49
hi dan,
thanks for your help .
a few queries ............
you have mentioned
about
>1) review your indexing strategy to prevent scans
how do i do this?do u mean that i should reconsider the columns that i
use in clustered index?
will using an index hint help in this case?
thanks once again .
Dan Guzman wrote:
> > what i fail to understand from the log is
> > 1. in the log it throws an exculsive lock on the select statement
> > .(but how can a select statement hv an X clusive lock.)
> >
> > 2. moreover it showws that there is a key lock .what i cannot
> > understand is even in the update statements of the sps i am not updaing
> > the fileds of the clustered index.
>
> The exclusive key lock is probably the row-level lock from the previous
> uncommitted UPDATE and is not caused by updating key columns. The
> subsequent SELECT statement is reported as holding the lock because it's in
> the same transaction.
>
> Scans are notorious for causing deadlocks with row-level locking. Consider
> this scenario:
>
> Session 1:
> BEGIN TRAN
> UPDATE T1 row A
>
> Sesion 2:
> BEGIN TRAN
> UPDATE T1 row B
>
> Session 1:
> SELECT * FROM T1 --blocked when row B is encountered
>
> Session 2:
> SELECT * FROM T1 --blocked when row A is encountered, causing
> deadlock
>
> As far as addressing deadlocks, you can:
>
> 1) review your indexing strategy to prevent scans
> 2) specify a higher-level lock via a table hint (e.g. TABLOCK, HOLDLOCK)
> 3) retry following a deadlock
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
Navigation:
[Reply to this message]
|