【MySQL1】- 调优基础（锁、事务、隔离级别）

1.Mysql的锁机制

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

1. MySQL的锁机制比较简单，不同的存储引擎支持不用的锁机制
2. MyISAM和MEMORY采用的是表级锁（table-level-loking）；InnoDB既支持行级锁（row-level-locking），也支持表级锁，默认情况下采用行锁
3. 表锁：开销小，加锁快，粒度大，不会出现死锁，锁冲突概率高，并发度低
4. 行锁：开销大，枷锁满，粒度小，可能出现死锁，锁冲突概率低，并发度高

2.MyISAM表锁

1.两种锁模式

1. 表共享-读锁（Table-Read-Lock）
2. 表独占-写锁（Table-Write-Lock）

2.规则（同一张表）

1. 读操作，不会阻塞读操作；
2. 读操作，阻塞写操作；
3. 写操作，阻塞读操作 & 写操作；
4. 逻辑：MyISAM在执行查询语句之前，会自动给所涉及的表加读锁，在执行更新操作前，会自动给涉及的表加写锁，不需要用户来显式加锁

3.InnoDB锁 - 重点

1. InnoDB的事务及ACID属性

1. 原子性
2. 一致性
3. 隔离性
4. 持久性

2. 并发事务带来的问题

1. 脏读

   1. SessionA修改了某条数据，但事务未提交（有回滚的可能）
   2. SessionB读取了这条数据，然后用它处理逻辑

2. 不可重复读

   1. SessionA在事务A中，第一次读取某行数据为a，未提交事务
   2. SessionB在事务B中，修改了该行数据，提交了事务
   3. SessionA在自己的事务A中，第二次读取该行数据，发现数据与第一次读取的内容不同

3. 幻读

   1. SessionA修改表中的全部原始行
   2. SessionB给表中插入新行
   3. A和B，都操作完，结果是发现有未被修改的行存在

4. 查询隔离级别

   show variables like 'tx_isolation';
   

5. 设置隔离级别

   set tx_isolation='REAPEATABLE-READ'
   

   	脏读	不可重复读	幻读
   READ-UNCOMMITED	√	√	√
   READ-COMMITED		√	√
   REPEATABLE-READ			√
   SERIALIZABLE

3.MVCC机制，多版本并发控制

1. REEPEATABLE-READ级别；
   1. a事务在第一次查询时，会取用数据库最新的数据内容(别的线程事务提交后的数据)；
   2. a事务在第二次查询时，会使用自己a事务第一次查询获得的快照数据内容；
   3. a事务在进行update时，不论第几次，都会使用数据库最新的数据内容进行运算！
   4. a事务在update之后，再次select操作，会获得最新的数据内容嘛？
   5. 所以：使用sql语句变量进行更新操作，可以杜绝bug，尽量不要使用java代码进行修改后sql进行set；

4.InnoDB的行锁模式和加锁方法

1. 共享锁-读锁（S）
   1. 允许一个事务去读一行，阻止其他事务获取相同数据集的排他锁
   2. 事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改
2. 排他锁-写锁（X）
   1. 允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁
   2. 事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁
3. 规则：
   1. InnoDB默认的修改数据语句：update，delete，insert都会自动给锁涉及的数据加上排他锁（X）
   2. 查询语句：select语句默认不会加任何锁
   3. select … for update – 给语句加排他锁
   4. select … lock in share mode – 给语句加共享锁

5.行锁的实现方式

1. 行锁的本质是给索引上的索引项加锁，所以如果使用的是同一个索引，也会出现锁争抢问题
2. 只有通过索引条件检索数据时，InnoDB才会使用行锁，否则，InnoDB将使用表锁

6.总结

1.MyISAM

1. 共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
2. 在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
3. MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
4. 由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

2.InnoDB

1. InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁
2. 在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

3.MySQL设计

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

1. 尽量使用较低的隔离级别；
   精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
2. 选择合理的事务大小，小事务发生锁冲突的几率也更小；
3. 给记录集显式加锁时，最好一次性请求足够级别的锁。
   比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
4. 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
5. 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响；
   不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；

对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。