mysql 锁，隔离级别

1.锁是对多线程，多进程访问同一资源进行协调的机制

2.分为行锁，表锁，页锁。myisam和memory支持表锁，BDB引擎支持表锁，页锁。innodb支持表锁，行锁（默认）

• 表锁：锁住整张表，加锁快，颗粒度大，开销小，不会出现死锁，发生锁冲突的概率最高，并发度最低。
• 行锁：锁住一行记录，加锁满，颗粒度细，开销大，会出现死锁，发生锁冲突的概率最低，并发度也最高
• 页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

3.MyISAM表锁

• 有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。 
• 表独占写锁：同一线程可以读写操作，其他线程读写等待
• 表共享读锁：同一线程可以读，写报错，其他线程可以读，写等待
• MyISAM的select自动加读锁，insert，update，delete自动加写锁
• 手动加锁可以用LOCK TABLE xxx read/write
• LOCK TABLE xxx read local 设置并发插入
• 只能访问显式加锁的这些表，不能访问未加锁的表
• 自动时，MyISAM总是一次获得SQL语句所需要的全部锁
• 锁定表带别名时需要锁定别名，lock table actor as a read,actor as b read;
• show status like 'table%'，分析系统上的表锁定争夺，Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况

4.MyISAM并发插入

• MyISAM表也支持查询和插入操作的并发进行
• concurrent_insert设置为0时，不允许并发插入
• （默认）concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录
• 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
• read local时当前线程不允许写，其他线程可以并发插入

5.读写操作同时争抢锁，写锁优先，可以设置写锁优先级降低，low-priority-updates

6 .事务特性

 

• 原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

7.并发事务问题

• 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。加必要的锁来解决
• 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。事务隔离机制来解决
• 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。事务隔离机制来解决
• 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。事务隔离机制来解决

8.事务的隔离级别

• 简单的select操作，属于快照读，不加锁
• 当前读：加锁

这个表不准（Repeatable Read解决了幻读，通过间隙锁）

9.show status like 'innodb_row_lock%'分析系统上的行锁的争夺情况

10.InnoDB实现了以下两种类型的行锁。

• 读锁，事务A加读锁，事务B也要加读锁才能读 select * from table where ? lock in share mode;
• 排他锁（Ｘ）：又称写锁，本事务可以读写，其他事务不能再加任何锁，但是可以普通select快照读（不加锁）
  • select * from table where ? for update;
  • insert into table values (…);
  • update table set ? where ?;
  • delete from table where ?;

11.意向锁（Intention Locks），这两种意向锁都是表锁。

• 意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
• 
• 锁不兼容，该事务就要等待锁释放。 

12.InnoDB行锁是通过给索引上的索引项加锁来实现的

• 只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 
• 访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的
• myisam同线程锁冲突报错，不同线程等待。innodb同事务不同事务都是等待
• 锁定的是索引的记录，不是索引的字段
• MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，
• 通过用explain检查两条SQL的执行计划，explain select * from tab_with_index where name = 1，MySQL能够进行数据类型转换，可能不会使用索引

13.间隙锁（Next-Key锁）

• 用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项加锁
• 对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”
• InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
• 如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！101条记录，给第201加锁，会锁住>101的间隙
• 用来防止幻读，在隔离级别为repeatable Read时开启间隙锁

14.事务的隔离级别

• readUncommit：读未提交，可以读到未提交的数据
  • 实现原理：读用快照读，不加锁，写用排他锁
  • 防止更新丢失（不能同时写），出现脏读，幻读，不可重复读
• readCommit：读提交了的数据
  • 实现原理：读加共享锁，读完立马释放共享锁，写加排他锁，事务结束时释放排他锁
  • 防止更新丢失，脏读，出现不可重复读，幻读
• RepeatableRead：可重复读，
  • 实现原理：读加共享锁，事务结束时释放，写加排他锁，事务结束时释放，范围读时加间隙锁
  • 防止更新丢失，脏读，幻读，不可重复读
• Serializable：序列化
  • 实现原理：读加表级共享锁，读加表级排他锁，
  • 防止更新丢失，脏读，幻读，不可重复读，性能最低