CODEWITHSUNDEEP
mysqlperformancemyisam
dev.e.loper
dev.e.loper

Reputation: 36054

Am I locking a table when performing a SELECT on a table?

This is a two part question. When I perform a SELECT on a table, am I locking that table from any usage while the SELECT is running?

If so, what is a way to not lock the table while performing a SELECT statement? I'm using MySQL 4.1.20 MyISAM.

update there is a similar question here Any way to select without causing locking in MySQL? but the answer doesn't work with MyISAM

Upvotes: 0

Views: 223

Answers (3)

nos
nos

Reputation: 229204

Yes, with MyISAM tables, the select locks the table for inserts/updates. However several selects can run concurrently (i.e. it applies a read lock). If there's no free space in the middle of the table, inserts will add data to the end of the (internal) storage, and those inserts can still be run concurrently with the selects though.

More info here. Note that MyISAM and InnoDB works very differently in this regard.

Upvotes: 2

Pete Wilson
Pete Wilson

Reputation: 8694

Think about it: are you changing any table cells when you do your SELECT? If not, there's no need to lock the table and MySQL doesn't. Table locking has to happen at UPDATE time, not at SELECT time.

Upvotes: -1

Mikhail
Mikhail

Reputation: 9007

There's table locking and row locking. I recommend you read up on database engines MyISAM with Table Locking and InnoDB Row Locking

Upvotes: 0

Related Questions