Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
If the INSERTS are "important" consider using InnoDB.. the only reason MyISAM is pretty fast because it fires INSERTS to the OS cache and "forgets" what data is there and try to flush the complete OS cache to the disk "sometime".. still InnoDB (correctly configured) will outperform MyISAM.. note not for SELECT COUNT(*) FROM table what will force an complete table scan on the InnoDB engine. –  Raymond Nijland Dec 4 '13 at 23:21

2 Answers 2

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.