Deadlock found when trying to get lock; try restarting transaction

[delpino]

Hello everybody,

since changing from Mysql to Innodb for stability reason I get this error message once in a while:

Deadlock found when trying to get lock; try restarting transaction

Anybody knows how to avoid this? How are your experiences with Innodb with HM? Is it more stable than Mysql? Mysql seems to need a repair quite often.

[Seb]

What kind of scripts are you running?

InnoDB is not something you run instead of MySQL, it's a storage engine for it.

[delpino]

Sorry I meant I use MyISAM instead of Innodb.. the error occurs in my own script during these queries:

UPDATE chinese_user_data_words SET word_loop=1 WHERE word_loop=0 AND user=3534 AND value > -1 AND value < 1208536954 ORDER BY value LIMIT 1

UPDATE chinese_user_data_words SET word_loop=1 WHERE word_loop=0 AND user=5223 AND value > -1 AND value < 1208311896 ORDER BY value LIMIT 1

[Seb]

I've only worked with deadlock scenarios on MSSQL so not sure about MySQL...

Try;

SET AUTOCOMMIT = 0;
LOCK TABLES chinese_user_data_words WRITE;
UPDATE chinese_user_data_words
SET word_loop=1
WHERE word_loop=0 AND user=3534 AND value > -1 AND value < 1208536954
ORDER BY value LIMIT 1;
COMMIT;
UNLOCK TABLES;

Are there many rows in the table? Consider adding an index to the 'user' field (unless there already is)

[delpino]

It has 131,422 rows .. the biggest table in my database. Does that mean the more rows the higher the probability of a deadlock problem?
I've got a combined unique index (I think thats whats it called) on word_id, list_id and user.

[Seb]

Essentially yes, it will increase the probability because it will take longer time to find the row you want to update. But assuming you surround your update queries with the code I posted above, and it works as intended, no two operations can update the table on the same time. Instead they should queue up and wait for the other one to finish before it proceeds.

That combined index you have is useless in this scenario unless 'user' is the first field. It's because your query can't take advantage of it's sorted order unless it's used in conjunction with also 'word_id' and 'list_id'.

My advice is, try to surround your update queries with the lock functions as described above and see it that solves your problem. Adding a sole index to to 'user' could also speed them up slightly.

If that doesn't work and no one else here knows a solution post it up in the mysql forum and describe your exact problem, what you're trying to do and what table structure you have. I'd be interested to hear.

[delpino]

Thanks, I'll take your advice.

[delpino]

I've added an index for user and it speeds up things. Hopefully it will also fix the deadlock problem. Anyway thanks for the help.

By the way in phpmyadmin it says "InnoDB free: 6144 kB" at the top of the table structure overview, what does that mean?

[Seb]

Google said it's the allocated space for the table. It should increase/decrease whenever you add/remove records so no worries.