Mysql高级性能优化之锁（十三）

锁

概念

在计算机中，协调多个进程或线程并发访问某一资源的一种机制； 在数据库中，除了传统的计算资源（I/O，CPU，RAM等），数据也是一种供许多用户共享访问的资源； 如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。

在购买商品的时候，商品库存只有一个，两人同时购买，这个时候就涉及锁的一个概念，会用到事务，先从库存表中取出物品的数据，然后插入订单，付款后，插入付款表，更新商品信息，在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

---

锁分类

按操作分：

1、读锁（共享锁）：

允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有（只能读取不能修改）。

2、写锁（排它锁）：

一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。

详情：
https://www.cnblogs.com/boblogsbo/p/5602122.html
https://www.cnblogs.com/panxuejun/p/8874321.html

---

按粒度分：

1、表锁：

偏向Myisam存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低（整张表只能一个人使用）；

（1）示例：
①建立一张Myisam引擎的表

②查看表有没有被锁过：show open tables;(若1，说明表被加锁；0，为表没被加锁)
③对表加锁：lock table locktest(表名) read（读锁），locktest2 (表名) write（写锁）；
④对表进行解锁：unlock tables；

（2）读写锁对操作和性能产生哪些影响
①对locktest（表名）添加读锁，lock table locktest read；

操作内容	当前连接	另一个连接
是否可以查看自己	可以	可以
是否可以更新	不可以	当更新时，处于阻塞状态，等待解锁后，才能进行更新
能不能读别的表	当前表没有解锁的情况下，不可以	可以

②对locktest（表名）添加写锁，lock table locktest write；

操作内容	当前连接	另一个连接
是否可以查看自己	可以	当更新时，处于阻塞状态，等待解锁后，才能查看
是否可以更新	可以	当更新时，处于阻塞状态，等待解锁后，才能进行更新
能不能读别的表	当前表没有解锁的情况下，不可以	可以

表锁分析
show status like ‘table（表名）%’ ：查看表的状态可以查看表的创建时间
Myisam的读写调度是写优先，这也是myisam不适合做为主表的引擎
因为写锁后，其他线程不能做任何操作，大量更新会使用查询很难得到锁，从而造成永久阻塞

商城可分为两个库，卖家库和买家库，卖家库更偏向于写，买家库更偏向于读

若表经常做一些更新操作，就不要使用表锁；若读数据比较多的时候，可以使用表锁。

---

2、行锁：

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

（1）事务

ACID属性	说明
原子性	事务包含的所有操作要么全部成功，要么全部失败回滚
一致性	事务执行之前和执行之后都必须处于一致性状态，让数据保持一定上的合理
隔离性	当多个用户并发访问数据库时，如操作同一表，数据库为每一个用户开启的事务，不能被其他事务所干扰，多个并发事务之间要相互隔离
持久性	指一个事务一旦被提交，就不能再回滚了，已经把数据保存在数据库中了

（2）并发事务处理带来的问题

问题	说明
更新丢失	两个或多个事物选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生更新丢失的问题，最后的更新覆盖了其他事务做的更新
脏读	事务A读到了事务B已修改，但未提交的数据（Read committed：读提交，能解决脏读的问题）
不可重复读	一个事务范围内两个相同的查询却返回了不同数据，事务A读到了事务B已经提交的修改数据（Repeatable read）
重复读	事务开启，不允许其他事务的UPDATE修改操作
幻读	一个事务(同一个read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行（serializable：效率低下，比较耗数据库性能，一般不使用）
对应关系

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable

脏读幻读等详情：https://www.cnblogs.com/balfish/p/8298296.html

查看隔离级别	语句
查看当前会话隔离级别	select @@tx_isolation;
查看系统当前隔离级别	select @@global.tx_isolation;

设置隔离级别
全局	set global transaction isolation level repeatable read;
当前会话	set session transaction isolatin level repeatable read;

注意：索引失效，行锁变表锁

间隙锁：https://blog.csdn.net/zcl_love_wx/article/details/82382582

如何锁定一行数据：查询语句+for update；

查看行锁的状态：show status like ‘innodb_row_lock%’;

---

3、悲观锁

对于数据被外界修改持保守态度，认为数据随时都会被修改，所以整个数据处理中需要将数据加锁， 关系数据库的行锁，表锁不论是读写锁，都是悲观锁(一般都是依靠关系型数据库提供的锁机制)；

---

4、乐观锁

每次自己操作数据的时候认为没有人会去修改，所以不去加锁； 但是在更新的时候会去判断在此期间数据有没有被修改，需要用户自己去实现； 不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性；
为什么要使用乐观锁？ 对于读操作远多于写操作的时候，大多数都是读取，这时候一个更新操作会阻塞所有读取，降低了吞吐量。 最后还要释放锁，锁是会损耗性能的，我们只要想办法解决少量的的更新操作的同步问题。 如果读写比例差距不是很大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁，他增加了复杂度，也带来了额外的风险。
乐观锁的实现方式

更新操作时，查询数据把版本号或时间戳查询出来，更新语句将版本号+1或时间戳=当前时间戳，并且where条件中放入之前查询得到的版本号或时间戳，如下：