Table/database lock

Databases
1

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?

2

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.

Steve

3

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.

4

Hi

SHOW ENGINE INNODB STATUS

Can be used to inspect any deadlock reasons. Maybe this can help diagnose what type or why the lock exists?

5

I don’t know if this will help http://www.xaprb.com/blog/2007/09/18/how-to-debug-innodb-lock-waits/ ?

Steve

6

Is it just one query that is having the issues with deadlocking or are all queries having trouble?