Mysql 事务原理简单分析

Mysql Innodb中的事务隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	Innodb中不可能，后面解释
可串行化（Serializable ）	不可能	不可能	不可能

脏读

不可重复读

幻读

不可重复读和幻读的区别

这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。 刚才提到锁机制，那么我们最常见的就是悲观锁和乐观锁。

悲观锁和乐观锁

悲观锁

为了保证事务的隔离性，就需要一致性锁定读，就是每次操作数据时都去锁住数据，不管哪种操作（增、删、改、查）都加锁，以至于其他事务操作这些数据

乐观锁

一般都是给表新增version字段，然后先通过查询到该数据的version版本，之后数据修改时都将vsersion字段当作where条件去操作数据，并且将version字段修改成version+1,若修改行数大于1表示修改成功，反之则修改失败。

锁的原理

不使用索引

CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;
-- 手动开启一个事务，并在id = 1这条数据上加上排它锁
SELECT * from test1 WHERE id = 1 for UPDATE;

BEGIN;
-- 手动开启另外一个事务，此时给id=2的这条数据进行加排它锁，结果会如何？
SELECT * from test1 WHERE id = 2 for UPDATE

发现此时居然查询id=2的数据事务被卡住了。这是为什么呢？当表没有创建索引时或者查询语句没有命中索引时，锁住的是整个表的数据，因为没有命中索引故其会去扫描全表数据。 当一张表没有索引时，innoDB会创建一个隐藏主键索引，当通过隐藏的主键索引去检索时，将该表中所有的隐藏索引检索一遍 例子：如果手动开始事务，并在id=1的数据上手动加上排它锁.如果此时再去查询id=2的数据时，发现此语句卡住了。 故得出没有建立索引的表，一旦锁住数据及为锁住整张表。

主键索引

CREATE TABLE `test2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;
-- 主键索引
SELECT * from test2 WHERE id = 1 FOR UPDATE;

BEGIN;
-- 手动开启其他事务
SELECT * from test2 WHERE id = 5 for update;

此时说明，主键索引时只会锁住匹配到的索引项，而不会影响其他事务操作其他索引

唯一索引

CREATE TABLE `test3` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;
-- 唯一索引
SELECT * from test3 where name = '李四' FOR update;

BEGIN;
-- 唯一索引
SELECT * from test3 where id= 5 FOR update;

注意：此时SELECT * from test3 where id= 5 FOR update为什么执行卡住了？唯一索引锁定时，先通过唯一索引然后找到对应主键索引，也就是辅助索引--->主键索引的一个过程，所以查询id = 5的数据时也被锁住了。 通过上面几个例子发现mysql innodb是通过锁住索引来实现行锁的

mysql innodb 为什么不会出现幻读？

详见下面InnoDB的行锁，如下图 临界锁的操作

临界锁（Next-key Lock）锁定范围加记录

BEGIN;
-- 临界锁，锁住对应的范围，防止幻读。
-- 按道理此时应该锁住，6,7,8(已存在),9,10
SELECT * from test2 WHERE id > 5 and id <11 FOR UPDATE;

BEGIN;
-- 此时测试插入id=7 的值，按道理应该插入不进去，因为锁住的范围是(5,8]和(8,12]
insert into test2 VALUES(7,'试试');

BEGIN;
SELECT * from test2 WHERE id = 12 FOR UPDATE;

刚才不是id>5 and id <11的么？此时为什么id =12也被锁了呢？因为此时锁住的范围是(5,8]和(8,12]

Gap Lock(间隙锁)

BEGIN;
-- 间隙锁，因为id =7 这条数据不存在，故锁退化成了间隙锁，那么此时id=7 落在了(5,8)这个区间
SELECT * from test2 where id = 7 for UPDATE;

BEGIN;
-- 因为锁退化成了间隙锁，那么此时id=7 落在了(5,8)这个区间，故id =6 也一起被锁住了
INSERT into test2 VALUES(6,'卡卡');

Record Lock(记录锁)

注意： 测试在test2表中，也就是主键索引。

BEGIN;
-- 在事务1中在id =5 的主键项锁定
SELECT * from test2 where id = 5 for update;

在RR级别中，通过MVCC机制，虽然让数据变得可重复读(这就是上面为什么事务2也能读取数据)，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。

对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：

快照读：就是select select * from table ....; 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。 select * from table where ? lock in share mode; select * from table where ? for update; insert; update ; delete; 事务的隔离级别实际上都是定义了当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”，就需要另外的模块来解决了

写（"当前读"） 事务的隔离级别中虽然只定义了读数据的要求，实际上这也可以说是写数据的要求。上文的“读”，实际是讲的快照读；而这里说的“写”就是当前读了。 为了解决当前读中的幻读问题，MySQL事务使用了Next-Key锁。

mysql的索引，为什么不用二叉树，会有什么样的问题，红黑树呢，为什么也不用？

二叉树深度越高，I/O开销越大。查询速率较慢。 红黑树（特殊平衡二叉树）即其子节点的高度差不能大于1，会发生自旋 B树由于是多路平衡查找树，可以将每个磁块的数据放入4KB的数据，充分利用了磁盘的空间，再加上B树的高度降低，这样就能快速访问到数据 B+树，左闭合B+树，B+树非叶子节点不保存数据相关信息，只保存关键字和子节点的引用，B+树叶子节点是顺序排序的。

索引引擎，myisam是什么样的，innodb呢，这里面有用到聚集索引吗？

myisam是索引和数据分开,innodb的数据是索引和数据一起的，myisam没有聚集索引，这里也可以看出谁查询更快，谁写入。 innodb由聚集索引。 innodb的行锁通过临界锁（gap+recored)来实现机锁。