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).
This table is really sensitive, and it will messed up if I can't prevent this
Thanks for your answer
To answer your question - B & C are blocked until the timeout expires. I can't find the timeout setting for MyISAM right now - it's set in mysqld.conf for InnoDB.
As others have said - InnoDB is a better engine if you need transaction support.
However, consider whether you really need locks at all. They create unpredictable bottlenecks in performance, especially in systems with many concurrent users. While table-level locks shouldn't lead to the dreaded deadlock scenario, table-level locks mean that only one process can use the table at any time; if each database operation takes 1 second (not unreasonable in huge databases), any more than 2 concurrent users of the system will notice performance degradation; 10 concurrent users would see the system become very slow, especially if you have to join across multiple tables.
Transactions are a much better solution in most cases.
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.
To get good performance with transaction support better to use INNODB instead of MYISAM. INNODB works on row level locking. Same time multiple users can access the table for changing different records.