MySQL——MyISAM表级锁

锁是计算机协调多个进程或者多个线程并发访问某一个资源的机制。相对其他数据库而言，MySQL数据库的锁比较简单，最显著的特点是不同的引擎持有不同的锁机制。MyISAM和MEMORY存储引擎支持表级锁，DBD引擎支持采用页面锁，但也支持表级锁，InnoDB既支持行级锁也支持表级锁，默认为行级锁。

MySQL三种锁的特性如下：

（1）、表级锁：开销小，加锁快；不会出现死锁；锁定粒度比较大，发生锁冲突的概率最高，并发度最低。

（2）、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

（3）、页面锁：开销和加锁时间介于行级锁和表级锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

下面重点介绍行锁和表锁。

（一）、表级锁。

1.1、查询表级锁争用情况；

可以通过查询参数table_locks_waited和table_locks_immediate状态变量来分析系统上的表级锁争夺情况。

       

如果table_locks_waited的值比较高，说明存在比较严重的表级锁争用情况。

1.2、MySQL表级锁锁模式；

MySQL数据库表级锁的锁模式主要分为两种模式：表共享读锁和表共享写锁。锁模式兼容如下：

从以上表格，我们可以知道，MySQL中存储引擎为MyISAM的读操作不会阻塞其他用户对同一表的读请求，但会阻塞其他用户对同一表的写请求；MyISAM的写操作会阻塞其他用户对同一表的读和写请求。MySIAM存储引擎中，读操作和写操作之间以及写操作和写操作之间都是串行的。如下图20-2的例子可以知道，当一个线程获得一个表写锁后，只有持有写锁的线程才可以对表进行更新操作。其他线程的读写操作都必须等待，直到释放锁为止。

1.3、如何加锁；

MySQL在执行查询语句（select）前，会自动给涉及的所有表进行加锁，在执行更新语句（insert,update,delete）操作前,会自动给涉及的所有表加上写锁，这个过程并不需要用户干预，因此，用户一般不需要直接使用LOCK TABLE命令给MyISAM表自动加锁。一下实例中显示自动加锁，主要方便说明而已。

给MyISAM表显示加锁，一般是在一定程度上模拟事务的操作。实现对某一时间点多个表的一致性读取操作。

例如：有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order_detail,其中记录有各订单每一款产品的金额小计subtotal,假设这里需要查询两个表的金额合计是否吻合，需要执行一下两条语句。

select count(total) from orders;

select count(subtotal) from order_detail;

这里如果不给这两个表加上读锁，可能就会出错；在执行第一条语句的过程中，第二张表order_detail可能发生改变。

因此正确的方法是：

Lock tables orders read local,order_detail  read local;

select count(total) from orders;
select count(subtotal) from order_detail;

unlock tables;

特别说明：

（1）、上面的例子中在lock tables 时加了local，表示在满足MyISAM表并发插入条件下，允许其他用户在表尾插入记录。

（2）、在用lock tables 给表显示加锁时，必须同时取得所有涉及表的锁。并且MySQL不支持表升级。也就是说，在给表使用lock tables 后，该session只能访问显示加锁的这些表。不能访问未加锁的表。在自动加锁的情况下，MySQL的MyISAM是一次性获得所有sql中涉及的表锁，这也是MyISAM不会出现死锁的原因。

如下例子中，一个session使用lock tables 命令给表film_text加读锁，这个session可以查询锁定表的数据，但更新和访问其他未锁表都会出现错误；其他session可以查询表中的记录，但是更新就会出现锁等待。

使用lock tables 给表加锁时，必须一次性获取所有的表锁，同时一个表在sql语句中出现多少次，就要通过与sql语句相同的别名锁定多少次。否则也会出错。实例如下：

   1.4、并发插入数据。

mysql中存储引擎为MyISAM时，默认情况下，读和写是串行的，但在一定条件下MyISAM表也支持查询和插入操作的并发操作。

MyISAM存储引擎有一个系统变量concurrent_insert，专门控制其并发插入的行为。值分别为0,1,2。

（1）、concurrent_insert为0时，不允许并发插入。

（2）、concurrent_insert为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM表允许在读取数据的同时，另一个进程可以在表尾插入数据，这是MySQL的默认设置。

（3）、concurrent_insert为2时，无论MyISAM表是否有空洞，都允许在表尾并发插入数据。

如下例子中，session1获得表的READ LOCK 锁，该线程可以对表进行读取操作，但不能对该表进行更新操作，这里假设表中间不存在空洞。

可以利用存储引擎MyISAM表的并发插入特性来解决应用中对同一表的查询和插入的锁争用。

总结：对MyISAM存储引擎表，将concurrent_insert设置为2，总是运行并发插入数据；同时定期在系统空闲时段执行 

OPTIMIZE  TABLE 语句来整理空间碎片，收回因删除记录而产生的中间空洞。

1.5、MyISAM表的锁调度。

对MyISAM表，读锁和写锁都是互斥的，读写操作可以是串行。那么一个进程请求MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理这样的情况？答案是写进程先获得锁。不仅如此，即使读请求先获得锁等待队列，写请求后到，写锁也会插入到读锁之前！这是因为MySQL认为写请求比读请求更重要，这也是MyISAM表不适合大量更新操作和查询操作应用的原因。因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远堵塞，这样的情况非常糟糕，还好MyISAM表，可以通过一些参数设置来调节MyISAM表锁的调度。

（1）、通过设置启动参数low-priority-updates,使得MySQL的MyISAM引擎默认给予都请求优先权利。

（2）、通过执行命令SET  LOW_PRIORITY_UPDATES=1，使得该连接发出的更新请求优先级降低。

（3）、通过指定INSERT 、UPDATE、DELETE语句的LOE_PRIORITY属性，降低该语句的优先级。

另外MySQL对于MyISAM存储引擎，提供了一个折中的办法来调节读写冲突。即给系统参数max_write_lock_count设置一个合适的值，当表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获取锁的机会。

总结语：对于一些需要长时间运作查询的应用，如果给予读锁计划比较多，也会造成写锁的进程饿死！因此，在应用中，尽量避免长时间运行查询操作，不要总想使用一条sql语句解决问题，这种看似比较巧妙的sql语句，往往比较复杂，运行时间长，在可能的情况下，可以使用中间表等措施对sql语句进行分解，是的每一个查询可以很快完成。减少锁冲突。如果负责语句不可避免，尽量安排在数据库比较空闲的时间段执行，比如一些定期统计可以安排在晚间处理。