Re: Two updates deadlocking because where clause chooses the wrong index

[Vinod Sugur]

Hi Deron,

We can have one more master table which has only status of latest records for example:

Create table latest_row
(section integer not null primary key,
as_of datetime not null);

Insert into latest_row
Select section,as_of
from t
where is_current ='Y';

The below update will only work on section and won't have any locking issue.

Update t, latest_row lr
set t.is_current ='N'
where t.section = lr.section and
t.as_of = lr.as_of
and lr.section =1;

~Vinod