Mysql事务隔离级别和锁特性

数据库事务的四大特性（ACID）：

1. 原子性：事务包含的所有数据库操作要么全部执行，要么全部回滚。
2. 一致性：事务应确保数据库的在开始和结束的状态一致。即数据库中的数据应满足完整性约束，如两个客户相互转账，事务发生前两人的账户金额总额是2000，那么事务结束后两人的账户金额总额应当仍然是2000。
3. 隔离性：事务并发时一个事务的执行不应影响另一个事务的执行。
4. 持久性：事务一旦提交，它对数据库的修改应该永久保存在数据库中。

数据库事务的隔离级别及各级别下的并发访问问题

1. 更新丢失(Lost Update)或脏写：指一个事务的更新操作覆盖了另一个事务的操作。mysql所有事务隔离级别在数据库层面上均可避免更新丢失问题。最低数据库隔离级别 ，设置如下：Set session transaction isolation level read uncommited;
2. 脏读（Dirty Reads）:指一个事务可以读到另一个事务未提交的更新数据。该种问题可以在”已提交读”事务隔离级别上避免,，该事务隔离级别也是Oralce默认的事务隔离级别。具体操作如下Set session transaction isolation level read commited;
3. 不可重读（Non-Repeatable Reads）：事务A在多次读取同一数据的前后，事务B对该数据进行了修改操作，导致事务A在提交事务之前，多次读取同一条数据的结果不一样。通过提高事务隔离级别至repeatable read可避免,具体操作如下Set session transaction isolation level repeatable read; repeatable read也是mysql默认的事务隔离级别。设置后当事务B提交后，事务A读取的数据不会有任何影响，但是事务B提交的逻辑会在事务A中生效，已保证正常的逻辑。
4. 幻读（Phantom Reads）：一个事务内根据相同查询条件多次读取数据时，由于另一事务提交了相关的新增或删除操作，导致该事务前后读取的数据量不一致。避免幻读可通过提高事务隔离级别至serializable避免，sql如下 S et session transaction isolation level serializable ;此时事务所有的读操作均为被默认加上读锁，写操作默认加上写锁。其锁原理与可重复读下一模一样，同样有行锁、间隙锁、表锁等。

不可重复读的重点在于另一个事务对数据内容的修改，幻读的重点在于另一事务对数据数量的增加和删除。

 数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度 上“串行化”进行,这显然与“并发”是矛盾的。

同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

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

设置事务隔离级别： set tx_isolation='REPEATABLE-READ';

Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔 离级别，如果Spring设置了就用已经设置的隔离级别

当前读与快照读

当前读：即加了锁的增删改查，读取的永远是数据库的最新版本数据：

1.  select …. Lock in share mode 或 select …  for update ,即给select上读锁和排它锁。
2.  update、insert、delete语句自动上排它锁。（进行此类操作时数据库会先select相关记录并加锁返回，即增删改的内部就包括了一个当前读操作来获取数据的最新版本，然后再进行相关增删改操作，此时的快照会被更新为本次修改后的最新版本）

快照读：即不加锁的select 操作，只有数据库事务隔离级别不为serializable时才会有。在serializable事务隔离级别下，由于sql是串行化执行，快照读也被蜕化成加锁的当前读。快照读有可能读到的是数据的历史版本。创建快照的时机决定了读取到的数据版本

锁详解

锁的种类：

按级别分：

1. 读锁：读锁又称为共享锁，一个session在对表执行select 语句时会对默认表记录上读锁，此时其他session对锁住的记录上写锁(即进行增删改操作)就会被暂时阻塞，却可以对它再上读锁。
2. 写锁：写锁又称为排它锁，一个session在对表执行update语句时会对默认表记录上写锁，此时其他session无论对锁住的记录上写锁还是读锁(即所以增删改查操作)都会会被暂时阻塞。

     简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

按加锁方式分：

1.  自动锁: 即进行增删改查时自动加的锁。
2.  显式锁：  

-- 加读锁:
 lock  table  tableName  read ;

-- 给select语句加共享锁：
 select * from tableName lock in share mode;

-- 加写锁：
  lock  table  tableName  write;

-- 给select语句加排它锁： 
  select * from tableName for update;  

-- 解锁: 
  unlock  tables;
-- 查看表上加过的锁：
  show open tables;

按锁的粒度分，可分为

1.  表级锁：锁住整张表，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。
2.  行级锁：每次操作锁住一行数据。开销大，加锁慢；有可能出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
3.  间隙锁：锁住待操作数据在数据库的间隙区间。在repeatable read和serializable隔离级别下才有。
4.  临界锁(Next-key Locks) ：Next-Key Locks是行锁与间隙锁的组合。左开右闭。

间隙锁的使用前提：

1. 对索引的操作才会加grap锁，因为不走索引Innodb本身加的是表锁。
2. 对删、改、加锁读这种可能涉及范围查询的操作才会出现间隙锁，因为普通读(即快照读)Innodb默认不加锁，新增不会产生间隙锁。因此，如果一个事务对一批数据进行修改、删除操作，此时另一个事务开始新增，可能会发生间隙锁的竞争。
3. 若where条件全部命中，则只加行锁，因为加行锁（行锁:若走稀疏索引，则稀疏索引和密集索引都加锁；若走密集索引，则只给密集索引加锁）就足以保护操作的数据（此时也是命中的数据）不被其他事物改动。
4. 若where条件部分命中或全部未命中，则加grap锁，因为此时操作的数据数大于命中数据数，必须保证未被命中的数据也不能被其他事物所新增而出现幻读现象。

按使用方式分，可分为

1.  悲观锁：必须先解锁后访问，为数据处理的安全提供了保证，它的实现一般基于数据库的锁机制，如排它锁。
2.  乐观锁：认为数据一般情况下不会造成冲突，只有在数据提交的时候才会对数据的冲突与否进行检测，它的实现一般不会采用数据库的锁机制，而采用表字段记录数据版本号的方式实现。在提交更新数据前对对版本号进行一个当前读操作。

Innodb和MyISAM搜索引擎的锁区别：

MyISAM：默认支持表级锁，不支持行级锁。表级锁与索引无关。对数据进行读取的时候，它会自动加上表级的读锁，增删改的时候，默认加上表级的写锁。

Innodb：默认支持行级锁，select语句默认不上锁（因为它有MVCC机制），只有增删改默认上排它锁。Innodb执行增删改查时，如果sql不走索引时的字段锁是表级锁，走索引时的字段锁是行级锁。

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb 的整体性能和MYISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现 不仅不能比MYISAM高，甚至可能会更差。

行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

1 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:系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：

Innodb_row_lock_time_avg （等待平均时长）

Innodb_row_lock_waits （等待总次数）

Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待， 然后根据分析结果着手制定优化计划。

查看INFORMATION_SCHEMA系统库锁相关数据表

‐‐ 查看事务 
 select * from INFORMATION_SCHEMA.INNODB_TRX; 
‐‐ 查看锁 
 select * from INFORMATION_SCHEMA.INNODB_LOCKS; 
‐‐ 查看锁等待 
 select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到 
 kill trx_mysql_thread_id  
‐‐ 查看锁等待详细信息 
 show engine innodb status\G;

死锁

set tx_isolation=' repeatable-read ';

Session_1执行：select * from account where id=1 for update;

Session_2执行：select * from account where id=2 for update;

Session_1执行：select * from account where id=2 for update;

Session_2执行：select * from account where id=1 for update;

查看近期死锁日志信息：show engine innodb status\G;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

锁优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

2. 合理设计索引，尽量缩小锁的范围

3. 尽可能减少检索条件范围，避免间隙锁

4. 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

尽可能低级别事务隔离