Deadlocks in MySQL 4 with InnoDB tables

[R Fields]

Hello,

We are noticing a deadlock that happens when locking on a column that is a primary key vs. one that is not.

Specifically, we have a set of records that make up an ordered tree (each record has a left and right order).

We have several operations that modify this tree. What we use is a dummy record in the table to obtain an update lock, before modifying this tree. That way, we can make sure that only one update of the tree is ever done at the time. However, this seems to be deadlocking when we do it on the primary key for the table.

Here's how to replicate (our dummy lock record is customer_id=0, which is also the primary key):
<t1> select count(*) from customer_tree where customer_id = 0 for update;
<t2> select count(*) from customer_tree where customer_id = 0 for update;
(t2 waits as expected)
<t1> select count(*) from customer_tree for update;
(t1 deadlocks)

Now, if instead, we do this using a non-primary key, but other unique value (for example, normally our records all have positive values for the left_order and right_order, but for the dummy record we have values of -1, -1):
<t1> select count(*) from customer_tree where left_order = -1 and right_order = -1 for update;
<t2> select count(*) from customer_tree where left_order = -1 and right_order = -1 for update;
(t2 waits as expected)
<t1> select count(*) from customer_tree for update;

Here there are NO deadlocks. t2 continues to wait until t1 commits. After t1 commits, t2 processes, and everything works correctly.

Is this intended behavior?

Thank you,
-Richard