MySQL锁之表级锁

在MySQL中支持表级锁的主要有MyISAM存储引擎和InnoDB存储引擎，其中MyISAM存储引擎只支持表级锁，而InnoDB既支持表级锁也支持行级索。一般使用表级锁时使用MyISAM较多。

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

共享读锁也叫共享锁，某一事物读取数据时加锁，其他事物在读取数据时亦可加共享锁，但是不能存在有事物加排它锁的情况。如果有加排它锁的其他事物需要执行，那么必须等待释放共享锁。

独占写锁也叫排它锁，某一事物在写数据时需要加上排它锁，确保其他事物不能修改数据。如果某一事物加上了排它锁，那么同一时间不能同时存在另外的排它锁和共享锁。如果有加排他锁或共享锁的其他事物需要执行，那么必须等待排它锁的释放。

请求锁模式            是否兼容 当前锁模式	None	读锁	写锁
读锁	是	是	否
写锁	是	否	否

可见，对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

根据下图所示的例子可以知道，当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

       MyISAM存储引擎的写阻塞读例子

session_1	session_2
获得表film_text的WRITE锁定 mysql> lock table film_text write; Query OK, 0 rows affected (0.00 sec)
当前session对锁定表的查询、更新、插入操作都可以执行： mysql> select film_id,title from film_text where film_id = 1001; +---------+-------------+ | film_id | title       | +---------+-------------+ | 1001    | Update Test | +---------+-------------+ 1 row in set (0.00 sec)   mysql> insert into film_text (film_id,title) values(1003,'Test'); Query OK, 1 row affected (0.00 sec)   mysql> update film_text set title = 'Test' where film_id = 1001; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1 Warnings: 0	其他session对锁定表的查询被阻塞，需要等待锁被释放： mysql> select film_id,title from film_text where film_id = 1001; 等待
释放锁： mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)	等待
	Session2获得锁，查询返回： mysql> select film_id,title from film_text where film_id = 1001; +---------+-------+ | film_id | title | +---------+-------+ | 1001    | Test  | +---------+-------+ 1 row in set (57.59 sec)

上图是显示加锁，但在 MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。例如，有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order_detail，其中记录有各订单每一产品的金额小计subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条SQL：

Select sum(total) from orders; Select sum(subtotal) from order_detail;

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail表可能已经发生了改变。因此，正确的方法应该是：

Lock tables orders read local, order_detail read local; Select sum(total) from orders; Select sum(subtotal) from order_detail; Unlock tables;

要特别说明以下两点内容。

·上面的例子在LOCK TABLES时加了“local”选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录，有关MyISAM表的并发插入问题，在后面的章节中还会进一步介绍。

· 在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。

一个session使用LOCK TABLE命令给表film_text加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；同时，另外一个session可以查询表中的记录，但更新就会出现锁等待。

        session1                                                                                                                                                     session2

     获得表film_text的READ锁定

当前session可以查询该表记录                                                                                                  其他session也可以查询该表的记录

    当前session不能查询没有锁定的表                                                                                          其他session可以查询或者更新未锁定的表

    当前session中插入或者更新锁定的表都会提示错误：                                                         其他session更新锁定表会等待获得锁：

    释放锁                                                                                                                                         Session获得锁，更新操作完成：

当使用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错

在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

参看自：深入浅出MySQL——数据库开发、优化与管理维护