ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql表锁与lock tables

mysql表锁与lock tables

原创 Linux操作系统 作者:myownstars 时间:2013-03-13 10:51:42 0 删除 编辑

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

下面着重介绍lock tables的用法

 

所需权限

用户必须拥有lock tablesselect权限;

 

选项

Read [local]—可由多个会话同时获取,local用于MyISAM,允许并发插入

[low_priority] write—阻止其他会话访问该表,通常写锁比读锁优先级要高,如果某表已经被添加读锁,此时其他会话申请写锁,则所有后续读锁请求会阻塞直至写锁释放,这样会影响读锁并发;

low_priority则允许其他读锁优先执行,使用该选项必须谨慎,很容易导致写锁饥饿等待;

对于autocommit=0innodb表,low_priority选项无效,视为普通写锁,会导致后续读锁请求等待

 

算法

对候选基表采用内部算法排序,用户无法干预此行为

如果表同时请求读锁和写锁,优先处理写锁,除非使用了low_priority,但此选项对开启事务的innodb无效

一次只锁定一个表,但释放时同时执行

 

释放表锁

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服务器获取表锁;commitinnodb释放内部表锁,unlock tablesmysql服务器才释放表锁;

如果autocommit=1innodb不会获取内部表锁,极易导致死锁发生;

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 添加写锁,即便是申请的读锁,因为在triggerinsert操作,故被转换为写锁

--t3读锁

--t4写锁 

 

表锁与分区表

对于MyISAM引擎,DMLlock 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   |

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

 

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-755980/,如需转载,请注明出处,否则将追究法律责任。

上一篇: mysql的缓存机制
请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3217938