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)

WHERE account_type = "type1"
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"
            MOD(MOD(user_id,65536), 20) != 9
            LIMIT 1000000
        ) a
    ) b

EDIT: As requested, output of SHOW CREATE TABLE

*************************** 1. row ***************************
       Table: User
Create Table: CREATE TABLE `User` (
  `account_type` varchar(20) NOT NULL DEFAULT '',
  `user_id` bigint(20) unsigned NOT NULL,
  `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`)

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)

  • Did you set the global innodb_lock_wait_timeout before thread that hit the timeout was created?
    – danblack
    Oct 31 '18 at 2:18
  • What do your queries look like exactly? Can you provide some examples? Oct 31 '18 at 5:26
  • Please provide SHOW CREATE TABLE User.
    – Rick James
    Nov 1 '18 at 4:29
  • Please explain the Replication topology. It sounds like you have "multi-source replication"?
    – Rick James
    Nov 2 '18 at 23:58

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.

  • Rick - Check out updated question. Oct 31 '18 at 18:10
  • Doesn't MOD(MOD(user_id,65536), 20) != 9 pick 95% of the users?? Maybe you meant =9 in order to get only 5%? What does "shard" mean? If it is referring to 20 servers, then please explain that. Then the !=9 means "all the users who don't belong on this machine"??
    – Rick James
    Nov 1 '18 at 4:26
  • @RickJames correct - to give a little context, we scaled out from 1 shard to 20 (well, 1-> 3 -> 20) with the sharding function shown. However, each shard was bootstrapped with 100% of the data. Hence this effort to clean up (as you mentioned) 95% of data on each shard. I changed/removed some column names (due to paranoia); these DBs store state for some games which recently took on millions of new users.
    – Arthur M
    Nov 2 '18 at 17:35
  • @ArthurMencke - OK, Copy over the good stuff. (See my addition.)
    – Rick James
    Nov 2 '18 at 19:35
  • We considered this approach, but the DB nodes we are purging need to continue replicating from the masters (which serve live requests). Therefore, deleting the bad data will not break replication (since the shard function ensures requests will never change the data we are deleting, but will change/add data which belongs and remains untouched by the deletion queries). It may be possible to copy the good data and replay the binlogs from the appropriate offset afterwards, but this I think requires more mysql experience than I bring
    – Arthur M
    Nov 2 '18 at 20:09

You probably hit this issue because you are running multiple delete transactions at the same time and some of these delete queries are trying to lock the same rows and they are waiting for each other.

Try to not delete the same rows in different transactions at the same time.

If you share the schema and the queries I could give you more advises.

Also there is a tool called pt-archiver which can archive/delete large amount of data from a table without any issues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.