MySql--锁

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 本文字数： 1.2k 阅读时长 ≈ 4 分钟

摘要

数据库锁

  • 从性能上分为乐观锁(用版本对比来实现)和悲观锁

  • 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)

读锁（共享锁，S锁[Shared]）：针对同一份数据，多个读操作可以同时进行而不会互相影响。读锁可以认为没有加锁，可读但不可写，当写锁锁住数据时，读锁也会不可获取。
写锁（排它锁，X锁[eXclusive]）：当前写操作没有完成前，它会阻断其他写锁和读锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 给表加读锁
# 当前session和其他session都可以读该表,当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待
mysql> lock table actor read;

# 给表加写锁
# 当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞
mysql> lock table actor write;

# 查看哪些表上加了锁
mysql> show open tables;
# 查看指定表
# In_use=1表示被加了写锁
mysql> show open tables like 'actor';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | actor |      1 |           0 |
+----------+-------+--------+-------------+

# 解锁
mysql> unlock tables;

对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

  • 从对数据操作的粒度分，分为表锁和行锁

1
2
# 行锁for update，这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交
select * from actor where id = 10 for update;

注意行锁的查询条件必须走索引，否则会升级为表锁
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

  • 行锁分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度（等待总时长）
Innodb_row_lock_time_avg: 每次等待所花平均时间（等待平均时长）
Innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数（等待总次数）

  • MyISAM不支持事务且只支持表锁，在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。

  • InnoDB支持事务和行锁，在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

  • 读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

  • 间隙锁(Gap Lock): 锁的就是两个值之间的空隙，用于解决幻读。间隙锁是在可重复读隔离级别下才会生效。

如account表的主键id是不连续的（1，2，3，10，20），那么间隙就有id为 (3,10)，(10,20)，(20,正无穷) 这三个区间
在Session_1下面执行 update account set age = 10 where id > 8 and id <18;
则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在 (3,20]区间都无法修改数据，注意最后那个20也是包含在内的。
注意这里锁住的最后一条记录不是id=18，而是18所在的间隙区间都会锁住。
尽可能减少检索条件范围，避免间隙锁

  • 临键锁(Next-key Locks): 是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

  • 查看锁等待详细信息

1
mysql> show engine innodb status\G;