在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; |
这时,如果不先给两个表加锁,就可能产生错误的结果,因为第一条语句执行过程中,order_detail表可能已经发生了改变。因此,正确的方法应该是:
Lock tables orders read local, order_detail read local; |
要特别说明以下两点内容。
·上面的例子在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——数据库开发、优化与管理维护