How to fix 'Lock wait timeout exceeded' when deleting single row of MySQL table?

Question

I'm making a website using Cherrypy, and I'm passing an array of IDs to a page to delete from the database. I don't seem to be able to delete any.

All other SQL statements seem to work, but running my DELETE FROM MACHINE WHERE ID=1008 causes the page to fail to load, and after a while I get Lock wait timeout exceeded; try restarting transaction.

def delete(self, table, paramater=""):
        sql = "DELETE FROM {0}".format(table)
        if paramater != "":
            sql += " WHERE {0}".format(paramater)
        print(sql)
        self.cursor.execute(sql)
        self.db.commit()

delete("MACHINE", "ID="+ids[0])

Structure: https://i.stack.imgur.com/lOhFV.jpg

We need to see the table structure ... `SHOW CREATE TABLE MACHINE` .. InnoDB will table lock the complete table when ID column is non indexed — Raymond Nijland, Aug 01 '19 at 13:59
@RaymondNijland ```('MACHINE', 'CREATE TABLE `MACHINE` (\n `ID` varchar(255) NOT NULL,\n `CREATED_AT` datetime DEFAULT NULL,\n `HOST` varchar(255) DEFAULT NULL,\n `NAME` varchar(255) NOT NULL,\n `SCREENSHOT` longblob,\n PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1')``` — clubby789, Aug 01 '19 at 14:05
Ok there is a index so thats not the problem -> [How to debug Lock wait timeout exceeded on MySQL?](https://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded-on-mysql) — Raymond Nijland, Aug 01 '19 at 14:07
Most examples I've seen of this problem occur with large numbers of actions, but I am only performing 2 delete statements — clubby789, Aug 01 '19 at 14:09
You still will have to debug -> `SHOW ENGINE INNODB STATUS` or and `SHOW FULL PROCESS LIST` and add that info in the question.. — Raymond Nijland, Aug 01 '19 at 14:13
I can't run that because the DELETE makes the whole site lock up — clubby789, Aug 01 '19 at 14:16
use a dev machine (same os / same software versions) copy the db from production... — Raymond Nijland, Aug 01 '19 at 14:20
I mean that once the delete statement runs, I can't run the STATUS without restarting the server — clubby789, Aug 01 '19 at 14:21

score 0 · Accepted Answer · answered Aug 01 '19 at 15:11

0

The issue turned out to be that I hadn't put quotes around the ID. The ID was stored as a varchar, so this was preventing it from obtaining the lock.

answered Aug 01 '19 at 15:11

clubby789

2,543
4
16
32

How to fix 'Lock wait timeout exceeded' when deleting single row of MySQL table?

1 Answers1