mysql锁

概述

定义

锁是计算机协调多个进程或县城并发访问某一资源的机制。

在数据库中，除传统的计算资源(如CPU、RAM、I/O等)的争用之外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据并发访问性能的一个重要因素。从这个角度来说，锁对数据库显得尤为重要，也更复杂。

分类

从数据的操作的类型(读/写)分

读锁(共享锁)：
针对同一份数据，多个读操作是可以同时进行而互相不影响。

写锁(排它锁)：
当前写操作没有完成前，它会阻断其他写锁和读锁。

从数据的操作粒度分

表锁

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁力度大，发生锁冲突的概率最高，并发度最低。

# 手动加锁  
lock table 表名字 read(write), 表名字2; read(write)

# 查看表上加过的锁
show open tables;

# 释放表锁
unlock tables;
1
2
3
4
5
6
7
8

session1 对A表加读锁。

session1 可以读A表，不能读别的表，不能写当前表。

session2 可以读A表，可以读或写别的表。但是对A进行写时会一直阻塞，等待获取锁。

行锁

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB与MyISAM最大的不同点：

1. 支持事务(TRANSACTION)
2. 采用了行级锁

---

事务及其ACID属性

事务

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下四个属性，通常简称为事务的ACID属性。

Atomicity(原子性)： 事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

Consistent(一致性)： 在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完善性；事务结束时所有的内部数据结构(如B数索引或双向链表)也都必须是正确得。

Isolation(隔离性)： 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部都是不可见的，反之亦然。

Durable(持久性)： 事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务带来的问题

更新丢失(Lost Update)
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会出现丢失问题–最后的更新覆盖了由其他事务所做的更新。

脏读(Dirty Reads)
一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取这条记录，如果不加控制，第二个事务读取了这些“脏数据”，并据此做进一步的处理，就会产生未提交的数据依赖关系。

事务A读取到事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

不可重复读(Non-Repeatable Reads)
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变，或某些记录已经被删除。

事务A读取到了事务B已经提交的修改数据，不符合隔离性。

幻读(Phantom Reads)
一个事务按照相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据。

事务A读取到了事务B提交的新增的数据，不符合隔离性。

事务隔离级别

# 查看当前数据库的事务隔离级别
show variables like 'tx_isolation;'
1
2

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大。因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然于“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的。比如，许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

---

索引失效行锁变表锁

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)”, InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

危害：
因为Query执行过程中通过范围查找的话，他会索订整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是 当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入索订键值范围内的任何数据。

如何锁定一行

mysql> begin;

# for update 锁定某一行之后，其他操作会被阻塞，直到锁定行的会话提交commit
mysql> select * from xx_table where a=8 for update;

mysql> commit;
1
2
3
4
5
6

锁状态量分析

可通过检查INNODB_ROW+LOCK状态量来分析系统上的行锁的情况。

mysql> show status like 'innodb_row_lock%;'

# 状态量说明
innodb_row_lock_current_waits: 当前正在等待锁的数量

innodb_row_lock_time:从系统启动到现在锁定 总时间长度。

innodb_row_lock_time_avg: 每次等待所花平均时间，

innodb_row_lock_time_max:从系统启动到现在等待这段时间

innodb_row_lock_waits:系统启动后到现在总共等待的次数。

# 尤其是当等待次数很高，而且每次等待时间也不小的时候
# 我们就需要 **分析系统之中为什么会有如此多的等待，然后根据分析结果着手指定优化计划**。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
2. 合理设计索引，尽量缩小锁的范围
3. 尽可能较少检索条件，避免间隙锁。
4. 尽量控制事务大小，减少锁定资源量和时间长度。
5. 尽可能低级别事务隔离。