0

Does MyISAM tables automatically lock a table for each select or insert statement ....

I mean if I query the database as select * from tablename then does the MySQL engine first lock it for read automatically and unlock it after showing results or do we have to first mention lock tablename read explicitly and then read the data....??

I have this doubt cause we have a database with some lack of rows and around some 50- 100 rows gets inserted or updated each second. We never used lock statements while inserting or updating and we are still not having any concurrency problems.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
prashanthp
  • 1

2 Answers2

1

MyISAM only locks for INSERT, UPDATE, and DELETE (a.k.a. DML)

These issue full table locks each time (See MyISAM Documentation on Locking granularity).

SELECTs get blocked by those statement.

The exception is an INSERT with concurrent_insert=2 defined.

Performing an explicit lock is unnecessary, although you are free to do so.

You may need to check you application for failed connections or DML failures.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

also nice information can be found in MySQL default logs like mysqld.log, slow query log, binary logging and after executing the query 'SHOW STATUS' the variable (Table_locks_waited) will indicate if you have concurrency problems or not

Raymond Nijland
  • 1,088
  • 7
  • 16