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

Question

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

This was answered here before: [mysql nolock quivelant][1] [1]: http://stackoverflow.com/questions/917640/any-way-to-select-without-causing-locking-in-mysql — Jacque, Oct 21 '11 at 14:59
@Jacque although it is a good recourse the answers for that question do not work with MyISAM — dev.e.loper, Oct 21 '11 at 15:43
@DavidLively Locks are there to preserve data integrity. However, in my case I do not care if my data is stale or not. I want to do what is called a "dirty read". — dev.e.loper, Oct 21 '11 at 15:44
@dev.e.loper Then you need to use another storage engine, such as InnoDB. — nos, Oct 21 '11 at 18:07

score 2 · Answer 1 · answered Oct 21 '11 at 18:00

2

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.

answered Oct 21 '11 at 18:00

nos

223,662
58
417
506

What would be a way to prevent locking? How can I do a dirty read? – dev.e.loper Oct 22 '11 at 13:47
@dev.e.loper You switch to innodb and set the isolation level to "read uncomitted". – nos Oct 22 '11 at 15:07

score 0 · Answer 2 · answered Oct 21 '11 at 14:53

0

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

answered Oct 21 '11 at 14:53

Mikhail

8,692
8
56
82

score -1 · Answer 3 · answered Oct 21 '11 at 15:36

-1

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.

answered Oct 21 '11 at 15:36

Pete Wilson

8,610
6
39
51

MyISAM puts a table level read lock on tables when doing selects, InnoDB applies more granular locking. But the point is, even selects applies locking, though for some storage engines it depends on the isolation level. (And other DBs goes a different route and uses MVCC to further avoid locking) – nos Oct 21 '11 at 18:04

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

3 Answers3