mysql表锁与事务等问题

对于MyISAM/Memory/Merge等引擎，mysql只提供表级锁，分为读锁和写锁，可通过lock tables手工获取，而DML会自动获取写锁；

下面着重介绍lock tables的用法

 注意：innodb下开启表锁会自动提交之前的事务 ，故后续业务造成数据出错无法回滚。

所需权限

用户必须拥有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   |

+-----------------------+---------+