Mysql数据库事务、隔离、锁机制介绍

1.数据库的锁机制
讲一个例子来理解数据库的锁机制吧，一个数据表中记录企业的雇员情况，a在修改雇员的时候，b在查询雇员信息，这样查询出来的雇员信息必定与现实出现冲突。为了解决这样的一个冲突，数据表中出现了锁机制，即当有人在修改数据表时，将该表加上锁，不允许其他人进行操作

当然这样的机制可以最大限度的防止更新查询冲突的出现，但这是建立在牺牲效率的基础上实现的，因此选择怎样的机制要视实际情况而定
从对数据操作的类型（读\写）分

• 读锁（共享锁）：针对同一块数据，即多个读操作可以同时进行而不会互相影响。
• 写锁（排他锁）：当当前写操作没有完成前，它会阻断其他写锁和读锁。

从锁定的数据范围分

• 表锁：即以表为单位去添加锁
• 行锁：以行为单位添加锁

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念

2.锁粒度

• 表锁：管理锁的开销最小，同时允许的并发量也最小的锁机制。MyIsam存储引擎使用的锁机制。当要写入数据时，把整个表都锁上，此时其他读、写动作一律等待。在MySql中，除了MyIsam存储引擎使用这种锁策略外，MySql本身也使用表锁来执行某些特定动作，比如alter table.
• 行锁：可以支持最大并发的锁策略。InnoDB和Falcon两张存储引擎都采用这种策略。

MySql是一种开放的架构，你可以实现自己的存储引擎，并实现自己的锁粒度策略，不像Oracle，你没有机会改变锁策略，Oracle采用的是行锁，而且oracle只有表空间。
3.事务
对mysql事务的理解：
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
• 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
• 事务用来管理insert,update,delete语句

---

4.事务的特性
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

• 事务的原子性：一组事务，要么成功；要么撤回。
• 稳定性 ： 有非法数据（外键约束之类），事务撤回。（比如删除部门表时，对应的员工没有删除成功，则原先的删除操作会被撤回）
• 隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
• 可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

5.mysql的事务处理有两种方式

• start-rollback-commit事务链

  • 通过start 开始事务
  • rollback 事务回滚
  • commit 事务确认

• 改变mysql的自动提交模式

  • setAutoCommit（false）；
  • commit（）；

MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持的！

比如执行一个转账操作，a向b转账100块，在转账过程中有任何账目错误，该事务都会被撤销

    conn.setAutoCommit(false);//设置事务不自动提交     ps=conn.prepareStatement("insert into account values(?,?,?)");     ps.setInt(1, 0);     ps.setString(2, "e");     ps.setString(3, "2000");     ps.executeUpdate();     sp=conn.setSavepoint(); //设置回滚点     ps=conn.prepareStatement("update account set account =account-100 where name=?");//a向b转账100块     ps.setString(1,"a");     ps.executeUpdate();     ps=conn.prepareStatement("update account set account =account+100 where name=?");//b收到a转账100块     ps.setString(1,"b");     ps.executeUpdate();     conn.rollback();//提交之前回滚的话，则之前的操作都不生效     conn.commit();     } catch (Exception e) {         if(sp!=null){              try{                   conn.rollback(sp);//返回到回滚设置点，通过回滚可以把之前的操作全部撤销掉                   conn.commit(); //这里只提交回滚点之前的sql操作              }catch(SQLException e1){                   e1.printStackTrace();         }     }else{         try{              conn.rollback();//如果没有回滚点，回到开启事务之前         }catch(SQLException e1){              e1.printStackTrace();         }     }

设置回滚操作后，必须设置提交操作，否则只是返回之前的状态，原先的操作还是没有能够提交回去

6.隔离级别（Isolation Level）
SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

• Read Uncommitted（读取未提交内容）
  在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
  脏读:一个事务读取到另一个事务未提交的数据
• Read Committed（读取提交内容）
  这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
  不可重复读:在一个事务内读取表中的某一行数据，多次读取结果不同 — 行级别的问题
• Repeatable Read（可重读）
  这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
  虚读(幻读):是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致 — 表级别的问题
• Serializable（可串行化）
  这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争，但效率也是最低的。

四大隔离级别:

Read uncommitted – 不防止任何隔离性问题,具有脏读/不可重复度/虚读(幻读)问题
Read committed – 可以防止脏读问题,但是不能防止不可重复度/虚读(幻读)问题
Repeatable read – 可以防止脏读/不可重复读问题,但是不能防止虚读(幻读)问题
Serializable – 数据库被设计为单线程数据库,可以防止上述所有问题

从安全性上考虑: Serializable>Repeatable read>read committed>read uncommitted
从效率上考虑: read uncommitted>read committed>Repeatable read>Serializable

7.更新丢失问题
两个线程基于同一个查询结果进行修改,后修改的人会将先修改人的修改覆盖掉.

• 悲观锁:
  悲观锁悲观的认为每一次操作都会造成更新丢失问题,在每次查询时就加上排他锁

• 乐观锁:
  乐观锁会乐观的认为每次查询都不会造成更新丢失.利用一个版本字段进行控制

查询非常多,修改非常少,使用乐观锁
修改非常多,查询非常少,使用悲观锁
但在实际操作中，以上的安全控制问题基本有框架可以解决
8.数据库隔离操作

查询当前数据库的隔离级别:

select @@tx_isolation;

设置隔离级别:

set [global/session] transaction isolation level xxxx;

其中如果[global/session]不写默认是session，指的是修改当前客户端和数据库交互时的隔离级别,
而如果使用golbal,则修改的是数据库的默认隔离级别

真正使用数据的时候,根据自己使用数据库的需求,综合分析对安全性和对效率的要求,选择一个隔离级别使数据库运行在这个隔离级别上.
mysql 默认下就是Repeatable read隔离级别
oracle 默认下就是read committed个隔离级别