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:
- 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?
- 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!
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?