Select query Locks the table, why does this happen

Question

I have a MyISAM table as I work with MATCH AGAINST and I have a select query which locks the entire table. Why does it happen?

Here is some output:

enter image description here

Unrelated - but what program are you using to generate that output? — fin1te, Jul 06 '11 at 12:08
That is `SHOW FULL PROCESSLIST` and `phpMyAdmin`'s Processes tab — Pentium10, Jul 06 '11 at 12:10
http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html — hakre, Jul 06 '11 at 12:14
I've came across of that, but select queries could cause locks? — Pentium10, Jul 06 '11 at 12:16
Yes, there are locks by read operations as well. They have a lower priority than locks by write operations, but they exist. — hakre, Jul 06 '11 at 12:25

score 2 · Accepted Answer · edited May 23 '17 at 10:32

2

I believe this question, Any way to select without causing locking in MySQL? may help you out. It appears to be locking because it's a MyISAM table.

edited May 23 '17 at 10:32

Community

1
1

answered Jul 06 '11 at 14:02

Drazisil

3,070
4
33
53

That's true, but I need the full text indexes. – Pentium10 Jul 07 '11 at 20:40

score 1 · Answer 2 · answered Jul 13 '11 at 21:22

MyISAM is "table level locking", meaning that the table can handle only one query at the time. So, as @Drazisil told, you have few options: optimize your query to reduce locking problem - stop using MySQL plain text feature (which is very poor performance) - split your big query into smaller queries - improve your indexes OR switch to innodb which is "row level locking"

Select query Locks the table, why does this happen

2 Answers2