Column locking in innodb?

Question

I know this sounds weird, but apparently one of my columns is locked.

select * from table where type_id = 1 and updated_at < '2010-03-14' limit 1;

select * from table where type_id = 3 and updated_at < '2010-03-14' limit 10;

the first one would not finish running even in a few hours, while the second one completes smoothly. the only difference is the type_id between the 2 queries.

a bit of background, the first statement screwed up before which i had to kill manually.

Thanks in advance for your help - i have an urgent data job to finish, and this problem is driving me crazy

Answer 1

Have you tried running an ANALYZE TABLE on the affected table?

You may want to consider converting over to MyISAM tables, as InnoDB tables have problems and restrictions.

Answer 2

A column cannot be locked - but an index range can. Show innodb status will normally tell you after which active transaction yours is waiting.

Answer 3

It seems like you do not have an index on (type_id, updated_at) and your table is rather large.

Creating the index should fix the problem:

CREATE INDEX type_updated_idx ON table (type_id, updated_at);

Assuming, you have enough permissions to make data definition statements.