How to debug "Waiting for table level lock" processes

[James Roberts]

Hi,

I have a server with multiple databases on it. Our production databases are running fine, but our dev versions have started "hanging" after a while of use.

What I see in mysql is processes like this:

" Waiting for table level lock | UPDATE `users_saved_carts` SET...(etc.)"

and the process never dies, unless killed manually. After killing it, the next load of the page usually causes the same problem. It's not usually the same table / query even, just any updates / inserts seem to hang.

The only fix so far is to reboot the server, and then things run fine for a little while (few days, weeks). Then the problem returns.

My question is what should I do next to debug this? Does it seem like a hardware issue, mysql issue, or code issue in the php? We have a shopping cart and wordpress running.

Thanks for any advice,

-Jim