I'm finding that my database tables occasionally seem to get completely locked. I have user visit logging tables which are fairly busy, and the first to cause errors if anything's locked. I've taken snapshots of the show processlist, and I can't see why they're locked:
12535027 dbuser 192.168.100.001:41694 mydb Query 152 Locked INSERT INTO mytable.....
12535028 dbuser 192.168.100.001:41697 mydb Query 149 Locked INSERT INTO mytable.....
12535030 dbuser 192.168.100.001:41703 mydb Query 146 Locked INSERT INTO mytable.....
12535036 dbuser 192.168.100.001:41733 mydb Query 126 Locked INSERT INTO mytable.....
12535039 dbuser 192.168.100.001:41740 mydb Query 122 Locked INSERT INTO mytable.....
12535045 dbuser 192.168.100.001:41756 mydb Query 112 Locked INSERT INTO mytable.....
12535046 dbuser 192.168.100.001:41758 mydb Query 111 Locked INSERT INTO mytable.....
12535047 dbuser 192.168.100.001:41759 mydb Query 111 Locked INSERT INTO mytable.....
12535052 dbuser 192.168.100.001:41773 mydb Query 96 Locked INSERT INTO mytable.....
12535054 dbuser 192.168.100.001:41775 mydb Query 90 Locked INSERT INTO mytable.....
12535074 dbuser 192.168.100.001:41799 mydb Query 35 Locked INSERT INTO mytable.....
If all of the queries are locked and none of them are running, what's causing the lock? How would I see this?
Are these table MyISAM or InnoDB?
If they are MyISAM then any reason why they could not be changed to InnoDB?
Although this is a very general comment and your locking problems could be something very different - your post does not give much to go on - InnoDB does not suffer from the same database locking problems that MyISAM can.
Yes, I'm aware there's not much to go on, but I wasn't sure what else would be useful - I'm a bit stumped here!
The tables locked here are InnoDB.
SHOW ENGINE INNODB STATUS
Can be used to inspect any deadlock reasons. Maybe this can help diagnose what type or why the lock exists?
Is it just one query that is having the issues with deadlocking or are all queries having trouble?