MySQL HA_ERR_LOCK_WAIT_TIMEOUT ignoring innodb_lock_wait_timeout (deleting most of a table)

innodbMySQLmysql-5.6python

I am issuing some long-running delete queries to some databases, and I am getting the following error:

Last_Error: Could not execute Write_rows event on table DATABASE.TABLENAME; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log mysql-bin.005698, end_log_pos 91495479

I have set "innodb_lock_wait_timeout" to high value such as the query will never hit the timeout.

SELECT @@GLOBAL.innodb_lock_wait_timeout;
+-----------------------------------+
| @@GLOBAL.innodb_lock_wait_timeout |
+-----------------------------------+
|                           1000000 |
+-----------------------------------+

Any suggestions for how to avoid this error? I need to delete large amounts of data so I want to issue queries in a loop without supervision. It feels a bit like a mysql bug. MySQL version is 5.6.

EDIT: Here's the exact query. The data is sharded client-side based on the function shown (MOD(MOD(user_id,65536), shard_count) != shard_id). The majority of the data in the DBs shouldn't be there, so the delete is big, and it's cascading down to tables that are >300GB (where each user might have an inventory of 200 items, for example)

DELETE FROM User
WHERE account_type = "type1"
AND
user_id IN (
    SELECT b.user_id
    FROM (
        SELECT a.account_type, a.user_id
        FROM (
            SELECT account_type, user_id
            FROM User
            WHERE account_type = "type1"
            AND
            MOD(MOD(user_id,65536), 20) != 9
            LIMIT 1000000
        ) a
    ) b
)

EDIT: As requested, output of SHOW CREATE TABLE

mysql> SHOW CREATE TABLE User \G;
*************************** 1. row ***************************
       Table: User
Create Table: CREATE TABLE `User` (
  `account_type` varchar(20) NOT NULL DEFAULT '',
  `user_id` bigint(20) unsigned NOT NULL,
  `mod_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `team_id` int(10) unsigned DEFAULT NULL,
  `shard_key` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`account_type`,`user_id`),
  KEY `PLAY_SHKEY_I` (`shard_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

EDIT: I believe at this point that it was down to a programming error, because I wasn't calling commit() from the python client until a really big loop completed, so I think there was a huge ongoing transaction that did not commit ever (because it never made it all the way through the loop)

Best Answer

Do not use OFFSET when looping through to do DELETEs; it is likely to get slower and slower, and maybe hit even your increased timeout.

Try not to put all the deletes in a single transaction. Instead make it so that if one delete dies, rerunning the deletes will still work 'correctly'.

Don't delete more than 1000 rows at a time.

More discussion of "big deletes": MySQL Big DELETEs

Deleting 95% of a table

Now that you have explained more of what is going on, I recommend rebuilding the table by creating a new table and copying over the 5%. (This technique applies for any percentage, but its benefits are dubious if copying more than 50%.)

Note that innodb_file_per_table needs to have been on before the bloated table was build, else you won't recoup the disk space.

If that would tie up the table too long, then walk through the PRIMARY KEY in chunks of 1K rows, deleting about 95% of each chunk. (As discussed elsewhere in that link.) You won't be able to return the disk space to the OS (without a locking OPTIMIZE TABLE), but the "freed" space will eventually be used by new INSERTs.

Related Question