对于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 |
+-----------------------+---------+！~~
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2014-11-12 10:32:19 |
赞(
操作
