CODEWITHSUNDEEP
mysqlmyisam
tomdp
tomdp

Reputation: 79

MyISAM locks on large select statements

On a MyISAM engine MySQL server (5.1)

We have some large query to make a report (1 million results). I notice that these select statements lock the tables. I don't find a real explanation for this on the internet. From what I understand the tables should only lock when switching between select and insert (etc) statements.

I found someone with the same problem http://devoluk.com/mysql-myisam-table-lock-issue.html

When i convert the tables to innodb the table locks don't happen. Unfortunately converting in production takes days, so its not really an option.

Did someone have the same issue, or how to make it non blocking?

Upvotes: 1

Views: 1560

Answers (1)

Rick James
Rick James

Reputation: 142366

Thread 1: SELECT ... -- long running
Thread 2: INSERT/DELETE/UPDATE/etc ... -- will wait for thread 1 to finish
Thread 3: SELECT ... -- will wait for thread 2

A SELECT does a shared-read lock, which allows other SELECTs, but not any writes.

MyISAM cannot have "deadlocks". (The referenced article is probably misusing that term.)

Also, check out INSERT DELAYED, but be aware of its caveats.

And LOW_PRIORITY

Upvotes: 2

Related Questions