对于MyISAM/Memory/Merge等引擎,mysql只提供表级锁,分为读锁和写锁,可通过lock tables手工获取,而DML会自动获取写锁;
下面着重介绍lock tables的用法
所需权限
用户必须拥有lock tables和select权限;
选项
Read [local]—可由多个会话同时获取,local用于MyISAM,允许并发插入
[low_priority] write—阻止其他会话访问该表,通常写锁比读锁优先级要高,如果某表已经被添加读锁,此时其他会话申请写锁,则所有后续读锁请求会阻塞直至写锁释放,这样会影响读锁并发;
而low_priority则允许其他读锁优先执行,使用该选项必须谨慎,很容易导致写锁饥饿等待;
对于autocommit=0的innodb表,low_priority选项无效,视为普通写锁,会导致后续读锁请求等待
算法
1对候选基表采用内部算法排序,用户无法干预此行为
2如果表同时请求读锁和写锁,优先处理写锁,除非使用了low_priority,但此选项对开启事务的innodb无效
3一次只锁定一个表,但释放时同时执行
释放表锁
1调用unlock tables,必须由执行Lock tables的同一会话执行
2若会话再次调用lock tables,则其原本获取到的锁会被隐式释放
3开启事务会隐含调用unlock tables,诸如start transaction
4占有锁的会话一旦终止其锁会被自动释放,事务也会被回滚
5 rollback不会释放表锁
表锁的限制
可用于锁定视图,即锁定该视图所有基表;
表锁仅阻止其他会话进行读写操作,若当前会话拥有某个表的锁,可执行drop table,但不可执行truncate table;
当对表进行insert delayed时不可对其添加表锁,否则insert delayed会报错,只因该insert由一个单独线程操作;
所有的单个update语句会默认获取表锁;
如要对一系列MyISAM表进行大量操作,可提前锁定表以提升性能,因为Mysql对加锁的表不会刷新其key cache;
默认写锁优先级较高,但可通过low_priority_updates/max_write_lock_count/high_priority等手段提升读锁优先级,也可修改mysys/thr_lock代码只使用一个队列,让写锁/读锁拥有相同优先级;
对于非事务引擎表,因为不支持select … for update,必须先锁定表,然后查询并更新
LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
SET total_value=sum_from_previous_statement
WHERE customer_id=some_id;
UNLOCK TABLES
必须一次锁定所有要访问的表,否则会抱错,而information_schema下的表不需要显示锁定也可访问
mysql>LOCK TABLES t1 READ;
mysql>SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
mysql>SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES
不能在一条sql中引用2次被锁定的表,即便使用也必须改用alias别名
mysql>LOCK TABLE t WRITE, t AS t1 READ;
mysql>INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql>INSERT INTO t SELECT * FROM t AS t1
表锁与事务
Lock tables会隐式提交当前事务,unlock tables只有在已经调用lock tables的前提下才会隐式commit;
开启事务(诸如start transaction)会释放当前获取的表锁;
Flush tables with read lock获取的是全局锁,而非表锁;
表锁与InnoDB
对于Innodb表,若要使用表锁,必须先设置autocommit=0且innodb_table_locks=1(默认),否则InnoDB无法侦测表锁而Mysql也无法感知行锁的存在;
且在事务提交后再unlock tables,如下例所示:
调用lock tables时,innodb引擎会获取内部表锁,然后mysql服务器获取表锁;commit时innodb释放内部表锁,unlock tables时mysql服务器才释放表锁;
如果autocommit=1,innodb不会获取内部表锁,极易导致死锁发生;
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES
Lock tables与触发器
如果基表上建有触发器,且触发器引用到了其他表,则锁定基表时会连带锁定这些被引用的表
LOCK TABLES t1 WRITE, t2 READ;
CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
BEGIN
UPDATE t4 SET count = count+1
WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
INSERT INTO t2 VALUES(1, 2);
END;
--t1添加写锁
--t2添加写锁,即便是申请的读锁,因为在trigger有insert操作,故被转换为写锁
--t3读锁
--t4写锁
表锁与分区表
对于MyISAM引擎,DML和lock tables会锁定整个分区表,create view/alter table只会锁住使用的分区,5.6.6引入了partition lock prunning改善了此功能;
而InnoDB,在分区修剪前不会实施行级锁,故不存在锁定整个分区的问题;
表锁统计信息
mysql>SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+