MyISAM锁表解析及应对方法

    最近服务器上经常出现mysql进程占CPU100%的情况，使用show processlist命令后，看到出现了很多状态为LOCKED的sql。使用show status like 'table%'检查Table_locks_immediate和Table_locks_waited，发现Table_locks_waited偏 大。出问题的表是MyISAM，分析大概是MyISAM的锁表导致。

    MyISAM适合于读频率远大于写频率这一情况。而我目前的应用可能会出现在某一时段读写频率相当。大致如下：

• 一个客户端发出需要长时间运行的SELECT
• 其他客户端在同一个表上发出INSERT或者UPDATE，这个客户将等待SELECT完成
• 另一个客户在同一个表上发出另一个SELECT；因UPDATE或INSERT比SELECT有更高有优先级，该SELECT将等待UPDATE或INSERT完成，也将等待第一个SELECT完成

    也就是说对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！
    MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write
Lock）。MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。
    所以对MyISAM表进行操作，会有以下情况：
    a、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
    b、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。 
    由于MySQL认为写请求一般比读请求要重要，所以如果有读写请求同时进行的话，MYSQL将会优先执行写操作。这样MyISAM表在进行大量的更新操作时（特别是更新的字段中存在索引的情况下），会造成查询操作很难获得读锁，从而导致查询阻塞。 

    解决方案大概有如下几种：

1. MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。
   • 0 不允许并发操作
   • 1 如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
   • 2 无论MyISAM表中有没有空洞，都允许在表尾并发插入记录
2. 使用--low-priority-updates启用mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下，在先前情形的最后的SELECT语句将在INSERT语句前执行。
3. 为max_write_lock_count设置一个低值，使得在一定数量的WRITE锁定后，给出READ锁定
4. 使用LOW_PRIORITY属性给于一个特定的INSERT，UPDATE或DELETE较低的优先级
5. 使用HIGH_PRIORITY属性给于一个特定的SELECT
6. 使用INSERT DELAYED语句