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

[Deron Meranda]

I've been working on this some more.

Unfortunately the UPDATE verb does not appear to support the index hints that SELECT does, such as USE INDEX or IGNORE INDEX. Those could have been used to avoid deadlocks by avoiding using the index on is_current (although it would result in many more rows being retrieved).

I think the only solution is to get it to use the primary key instead. Since the primary key is composite, you need to match it exactly or the optimizer will choose the secondary index on is_current instead since it thinks it's cheaper. Previously I was using a where matching the primary key (A,B) with (A = CONST, B < CONST). Even limiting B like (A = CONST, B IS BETWEEN CONST AND CONST) will not work. It has to be (A = CONST, B = CONST) before the primary key is used.

So the trick is that when adding a new row, I have to know the old row that was previously the current on. Since these timestamps are not predictable (they are datetimes, not dates), I have to first do a select to find them... in pseudo code, all in the same transaction:

begin transaction;
select as_of from mytable where section = 123 and is_current = 'Y';
<fetch all the rows>
<for each as_of value VAL in above do:>
update mytable set is_current = 'N' where section = 123 and as_of = VAL;
<next>
insert into mytable values ( ...<values for new row>... );
commit;

This avoids getting exclusive locks on the is_current index, and thus the possibility for deadlocks.

Note I could have used select with a max(as_of) when I'm sure there is only one row with is_current='Y' for that section, but the result is about the same.


I'm still curious if there is a better way to design these sorts of history tables.

Deron Meranda