MySQL事务隔离级别和各种锁

• MySQL的InnoDB/MYISAM 存储引擎的区别

  1. InnoDB支持事务、行级锁、外键
  2. MYISAM不支持以上三种，但支持全文索引FULL TEXT(InnoDB在5.6版本后才支持)

  • MyISAM适合：

    1. 做很多count 的计算；
    2. 插入不频繁，查询非常频繁；
    3. 没有事务。

  • InnoDB适合： (1)可靠性要求比较高，或者要求事务； (2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

  • InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

  • Mysql在V5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB

  	-- 查看当前存储引擎
  	show variables like '%engine%';
  	-- 查看所有存储引擎
  	show engine;
  	-- 修改本次回话默认存储引擎
  	set default_storage_engine=innodb;
  	-- 修改本次全局回话默认存储引擎
  	set global default_storage_engine=innodb;
  	-- 修改/etc/my.cnf，[mysqld]下面任意位置添加配置，重启生效
  	default-storage-engine = InnoDB
  

• MySQL的锁机制

  • 共享锁：给某些资源加上共享锁，允许自己读这些资源，也允许其他事务读该资源，不允许其他事务获得排它锁。 select * from table where ... lock in share mode

  • 排它锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。悲观锁通过排它锁实现 select * from table where ... for update

  • 意向共享锁（IS）：事务给数据行加上共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。(表锁)

  • 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。(表锁)

  • 脏读，最容易理解。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据。

  • 不重复读。解决了脏读后，会遇到，同一个事务执行过程中，另外一个事务提交了新数据，因此本事务先后两次读到的数据结果会不一致。

  • 幻读。解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

• MySQL的事务隔离级别

  • ReadUncommitted：未提交可读，会出现脏读，很少使用
  • ReadCommitted：提交可读，事务提交后才可读，会出现不可重复读和幻读，大部分数据库的隔离级别
  • ReadRepeat：解决了不可重复读，仍然存在幻读，MySQL的默认事务级别
  • Serializable（可串行化）
    这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

• 事务的传播行为：

  • PROPAGATION_REQUIRED：如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置。

  • PROPAGATION_SUPPORTS：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。‘

  • PROPAGATION_MANDATORY：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。

  • PROPAGATION_REQUIRES_NEW：创建新事务，无论当前存不存在事务，都创建新事务。

  • PROPAGATION_NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。

  • PROPAGATION_NEVER：以非事务方式执行，如果当前存在事务，则抛出异常。

  • PROPAGATION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

转载于:https://my.oschina.net/u/3917923/blog/2875268