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.

  • 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
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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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