Two updates deadlocking because where clause chooses the wrong index
With MySQL 4.1.20 using InnoDB. I have a table which is storing timestamped temporal data. In much simplified form, it contains three fields like:
section int not null,
as_of datetime not null,
is_current enum('Y','N') not null,
primary key (section, as_of)
key (is_current)
The implied constraint is that for each section number there is exactly one row that is marked as is_current='Y'; and that one has the most recent timestamp within that section.
As part of the larger transaction that inserts new records, when it knows that the new record will become the new current one (latest timestamp), it will issue an update to mark the old record as not current, something like this:
update mytable set is_current = 'N' where
section = 123
and as_of < 'YYYY-MM-...'
and is_current = 'Y';
I don't necessarily know the previously current timestamp exactly, so I can't use an as_of =; I have to use < instead.
Since the data is naturally partitioned by the 'section' field, I will have multiple processes (clients) potentially inserting new data simultaneously; each process working exclusively with a different section number.
However, a deadlock can sometimes happen on the update statement (when both clients are executing the same update statement, but with different section numbers). Looking at the details, it appears to happen because the two update statements are choosing to use the key on the is_current field first, rather than the primary key. This makes some sense since that secondary key actually results in fewer rows having to be scanned in this case. However because the primary key is not being used (which would have guaranteed that the same rows were never referenced), the two updates can end up locking the same rows as part of their record scan even if they don't ultimately try to update the same rows. This can result in a deadlock in some cases.
Is there any way to avoid this; say by somehow forcing the update to use the slightly less efficient primary key first? Or is there a better practice to designing these types of timestamped history tables, where you want a fast way to always find the most recent/current row?
Deron Meranda