Mysql – Cannot update certain rows in innodb tables

deadlockinnodbMySQL

I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html

When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

My questions are:

  1. What kind of situation is "a single SQL statement is rolled back as a result of an error" referring to? Does autocommit=1 count? Any example?
  2. I could imagine that if this happens, it leaves a lock to the rows affected and make any subsequent update to these rows fail, until a mysql restart or something that clears the lock, is that so?

Thanks for any input!

EDIT

OK I realized that maybe I wasn't asking the right question. Here is my situation:

I'm experiencing a weird locking issue with my innodb tables (MySQL version 5.1.61 shipped with latest Debian Squeeze). The symptom is that after running for a few days, certain rows suddenly cannot be updated. When observing locks with innotop, I can see that every update to those rows is waiting for X lock, but nobody is actually holding the lock, so all those updates wait till timeout. And the symptom could last for a very very long time (actually I haven't seen it to an end). Sometimes a flush table xxx could solve the problem, but other times I had to flush all tables or this wouldn't go away. The only clue I had was that before this happened, there were almost always deadlock about certain stored procedure (wrapped in transaction) and an auto-commit enabled statement in innodb status.

The stored procedure was like:

BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION SET var_is_error = 1;
  DECLARE var_cur CURSOR FOR SELECT items from item_table;
  SELECT some stuff;
  START TRANSACTION;
  REPEAT
    FETCH var_cur INTO item:
      UPDATE stuff set number = number + 1;
      ...MORE SELECTS AND UPDATES;
  Until some condition END REPEAT
  IF var_is_error = 0 THEN
    COMMIT;
  ELSE
    ROLLBACK;
END

and the autocommit = 1 statement

UPDATE `inventory` SET locked=IF(locked-2>0,locked-2,0),ordered=ordered+2 WHERE (`sku` = 10103)

According to innodb status, the auto-committed statement was holding the lock. I don't see a "WE ROLLED BACK TRANSACTION XX" normally seen in innodb status after a deadlock and there are just many lines of hex dumps about record locks, I guess more than innodb status could hold.

That's why I posted the original questions (and now I realize that I misunderstood that paragraph). I was actually trying to ask that is there a possibility that somehow row locks could be left behind after transaction rollbacks, that could affect the system for a very long time? Or am I looking at the wrong direction? Anyone else had a similar problem?

Best Answer

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

  • Your SQL UPDATEs a single row but generates an error
  • The UPDATE causes a rollback of the one row
  • The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)