MySQL锁定系统

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

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.