MySQL Locking System

Question

I'm currently building a huge scale of database with many transactions there. (Insert, Update, Select) with MySQL MyISAM

And I'm considering to use LOCK TABLE AND UNLOCK TABLE for the sensitive tables. What will happen if :

User A has an update process (Lock the table first) and while still in process, User B and User C trying to access the same table and row (whether it's update/select).

• Will B and C immediately get the SQL error about tables being locked?
• Does MySQL has lock timeout before it returns the error? If there's a lock timeout, how do I check the lock timeout in my server?
• Any better solution than locking the tables? We are considering of creating a table for job queue. But I think the performance will be really bad.

This table is really sensitive, and it will messed up if I can't prevent this

Thanks for your answer

Answer 1

Do not use MyISAM storage engine if you want to scale more efficiently and deal with concurrency problems. The main reason to choose InnoDB instead of MyISAM is that MyISAM use per table locks (which will become the bottleneck of your performance), while InnoDB implements MVCC (multiversion concurrency control) which means locking at row level.

Also MyISAM does not support transactions and needs manual repair in case of table curruption.