MySQL的表锁和行锁

/ Interview / 2 条评论 / 454人围观

基本命令

查看那张表被锁住,其中In_use字段大于0则表被锁。

show open tables;

给某张表上锁

lock table 【表名】【read/write】;

释放所有的锁

unlock tables;

查看引擎

show engines;

查看自动提交状态

show variables like 'autocommit';

表锁

以下是在MyISAM引擎下,MyISAM偏读。

准备

读锁

读锁也叫共享锁,以下都是测试出来的结果,有兴趣可以自己试试。

Session1Session2
可以查询 t_logs 表可以查询 t_logs 表
不可以更新 t_logs 表不可以更新 t_logs 表
会阻塞,必须等待锁释放
不可以查询别的表可以查询别的表
不可以更新别的表可以更新别的表

总结:当前会话为某张表加了读锁,当前会话仅仅能读取加锁的表,任何其它操作都不能做。而其他会话对此表只有读的权限,但除此之外的表,则正常操作。

写锁

写锁也叫排它锁

Session1Session2
可以查询 t_logs 表不可以查询 t_logs 表
会阻塞
可以更新 t_logs 表不可以更新 t_logs 表
会阻塞,必须等待锁释放
不可以查询别的表可以查询别的表
不可以更新别的表可以更新别的表

总结:当前会话为某张表加了写锁,那么当前会话就独享了这张表,拥有这张表的读写权限,且不能够对除此表之外的任何表做任何操作。其它会话也不可以对此表进行任何操作。

行锁

MySQL在5.1以后默认的存储引擎就是InnoDB,行锁演示是在InnoDB引擎下,该引擎支持事务,并且为了并发性能引进了行级锁。

准备

为了演示效果,先关闭自动提交功能。

set autocommit = 0;

结论

在Session1执行 commit 命令之前,假设修改 id = 1 的这一行,得出如下结论:

Session1Session2
能够修改id为1的这一行除了id为1的这一行都可以修改
也能够修改别的记录
但修改哪条记录,那条记录就被锁住
除了被锁住的记录不能修改以外(可以读),其它都可以修改

总结:行级锁顾名思义就是锁定数据库中的一行,对于数据库来说开销比锁一张表大,但是发生锁冲突的概率比表锁小很多,并发性能高很多,当前会话锁住这一行后,其它会话要想修改这一行,必须等待上一个会话进行事务的提交,否则就会阻塞在这里。但对于其它未被锁住的行,是不受任何影响的。

间隙锁

什么时候会发生间隙锁?看下面例子:

mysql> select * from t_permission;
+----+----------------+-------------+
| id | url            | description |
+----+----------------+-------------+
|  1 | /user/create   | create      |
|  3 | /user/update   | update      |
|  4 | /user/retrieve | retrieve    |
|  5 | /user/delete   | delete      |
+----+----------------+-------------+

t_permission 表 id 为主键,但缺少了 id = 2,当我们使用范围更新数据的时候,如下语句:

update t_permission set description = '666' where id > 0 and id < 6;

将 id = 2 包括了进去,那么mysql默认的会将这个范围之内所有的连续id都上锁,与此同时,在另一个会话中进行插入 id = 2 的数据操作:

insert into t_permission values(1,'222','222');

那么这个插入操作会阻塞,这就是间隙锁。如果线上环境发生了这样的锁等待,很不好发觉,可以用show profile命令来排查。

  1. 娃儿我让娃儿

    回复
  2. 2瞧2委屈

    回复