Link to home
Create AccountLog in
Avatar of melwong

asked on

Why is the table still locked after swiching to InnoDB?

I used to have a lot of these lock tables when i execute "mysqladmin processlist"
| 721   | Locked                   | update matches_details set status='Full-time' where match_id='1193'

So I convert the table to InnoDB. I have less locked now about half, but why are some still locked?
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

You may have code setting a transactional lock some where.  Are these locks that are unexpected or problematic?
Avatar of wolfgang_93
Flag of Canada image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of NovaDenizen

Does the table have an index on match_id?  If there is no index then it will have to lock the complete table while it does scan of every row in the table.
Avatar of melwong


Yes, match_id is indexed.
One way or another you have many multiple-statement transactions going on that lock the same rows (or pages?  I forget how InnoDB does it), forcing them all to run serially.
Avatar of melwong