Cannot update certain rows in innodb tables

Question

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?

Answer 1

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.

• Reasons for occasionally slow queries?
• Trouble deciphering a deadlock in an innodb status log
• Will these two queries result in a deadlock if executed in sequence?

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)

Answer 2

1. What kind of situation is "a single SQL statement is rolled back as a result of an error" referring to?

from InnoDB Error Handling

If you run out of file space in the tablespace, a MySQL Table is full error occurs and InnoDB rolls back the SQL statement.

A lock wait timeout causes InnoDB to roll back only the single statement that was waiting for the lock and encountered the timeout.

A duplicate-key error rolls back the SQL statement

A row too long error rolls back the SQL statement

Does autocommit=1 count?

from 14.6.8. The InnoDB Transaction Model and Locking

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error. See Section 14.6.12, “InnoDB Error Handling”.

From this I deduce: if autocommit is enabled then the transaction is not rolled back if “InnoDB Error Handling” says that only the statement is rolled back. But I am not sure if my interpretation is correct.

Any example?

If you start a transation and issue two update statements and the second one fails because of a Table is full error then the locks by the second update statement are not released after the error until the transaction is commited or rolle back, The locks of the first statement aren't released too until the transaction ist commited or rolled back.

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?

All locks of your transaction are released after commiting or roling back the transaction. It is not necessary to restart the database. the update of the rows in the same transaction should not fail if you issue another update statement for this row because they are locked by you from the failing sql statement in the same transaction