InnoDB实现了两种类型的行锁

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

• 共享锁（S）：又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
• 排他锁（X)：又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

　　另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

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

注意： 　　意向锁仅仅用于表锁和行锁的共存使用。如果我们的操作仅仅涉及行锁，那么意向锁不会对我们的操作产生任何影响。在任一操作给表A的一行记录加锁前，首先要给该表加意向锁，如果获得了意向锁，然后才会加行锁，并在加行锁时判断是否冲突。如果现在有一个操作要获得表A的表锁，由于意向锁的存在，表锁获取会失败（如果没有意向锁的存在，加表锁之前可能要遍历整个聚簇索引，判断是否有行锁存在，如果没有行锁才能加表锁）。 　　同理，如果某一操作已经获得了表A的表锁，那么另一操作获得行锁之前，首先会检查是否可以获得意向锁，并在获得意向锁失败后，等待表锁操作的完成。也就是说：1.意向锁是表级锁，但是却表示事务正在读或写某一行记录；2.意向锁之间不会冲突, 因为意向锁仅仅代表要对某行记录进行操作，在加行锁时，会判断是否冲突；3.意向锁是InnoDB自动加的，不需用户干预。

下面列出上述锁模式的兼容情况：

　　其中：+表示兼容；–表示不兼容。如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

事务可以通过以下语句显式的给记录集加共享锁或排他锁： 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 　　当访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
alter table tab_with_index drop index name;
insert into tab_with_index  values(1,'1');
insert into tab_with_index  values(1,'4');

　　此时若

select * from tab_with_index where id = 1 and name = '1' for update;

会对两行数据都加排它锁。 　　当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

间隙锁（Next-Key锁）

　　当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。 　　举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL： Select * from emp where empid > 100 for update; 是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。 　　InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要（这一点涉及事物的回滚）。

　　很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。 　　还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

事务回滚的实现

MySQL：是SQL语句级的，在执行事务中的SQL语句前，需要先在日志缓冲写日志，记录该事务的日志序列号和执行的SQL语句。当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制）。如果回滚，不是物理恢复，是逻辑恢复，因为它是通过执行相反的dml语句来实现的。而且不会回收因为insert和upate而新增加的page页的。即insert变成delete，update变成相反的update。

Oracle是基于数据库文件块的。 　　从上面两点可知，MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录（比如上面的>100都会锁定的例子，只要不插入>100的数，就不会幻读），也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。

多版本并发控制MVCC

　　MVCC (Multiversion Concurrency Control)，即多版本并发控制技术,它使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制,取而代之的是，把数据库的行锁与行的多个版本结合起来，只需要很小的开销,就可以实现一致性非锁定读，从而大大提高数据库系统的并发性能。 　　为了实现MVCC，InnoDB对每一行都加上了两个隐藏的列，其中一列存储行被创建的”时间”，另外一列存储行被删除的”时间”。当然InnoDB存储的并不是绝对的时间，而是系统版本号，即记录创建版本号和删除版本号。每当一个事务开始的时候，InnoDB都会给这个事务分配一个递增的版本号，事务开始时的系统版本号会作为事务的版本号。下面在repeatable read隔离级别下，说明MVCC的具体操作：

1. SELECT 对于select语句，只有同时满足了下面两个条件的行，才能被返回:

• 创建版本号小于或者等于当前事务版本号 ，就是说记录创建是在事务中（等于的情况）或者事务启动之前。
• 行的删除版本号要么没有被定义，要么大于当前事务的版本号：行的删除版本号如果没有被定义，说明该行没有被删除过；如果删除版本号大于当前事务的版本号，说明该行是被该事务后面启动的事务删除的，由于是repeatable read隔离级别，后开始的事务对数据的影响不应该被先开始的事务看见，所以该行可能被返回。

1. INSERT 在插入操作时，记录的创建版本号改为当前事务版本号。
2. DELETE 在删除操作时，记录的删除版本号改为当前事务版本号，相当于标记为删除，而不是实际删除。
3. UPDATE 在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号改为当前事务版本号，然后插入一行新的记录。

　　上述策略的结果就是，在读取数据的时候，InnoDB几乎不用获得任何锁，每个查询都通过版本检查，只获得自己需要的数据版本，从而大大提高了系统的并发度。 　　这种策略的缺点是，每行记录都需要额外的存储空间，更多的行检查工作和一些额外的维护工作。 　　另外，只有read-committed和 repeatable-read 两种事务隔离级别才能使用MVCC，read-uncommited由于是读到未提交的，所以不存在版本的问题。而serializable 则会对所有读取的行加锁。

在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。事务可以通过以下语句显式的给记录集加共享锁或排他锁：

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

Optimize Table

　　是mysql中一个可以回收更多的空间、减少“碎片”（defragment）的命令。当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。 　　多数时间并不需要运行OPTIMIZE TABLE，只需在批量删除数据行之后，或定期（每周一次或每月一次）进行一次数据表优化操作即可，只对那些特定的表运行。