MySQL之锁机制の五_mysql lock in share mode 其他人能select

并发事务解决方案

脏读、不可重复读和幻读都是数据库读一致性问题，需要由数据库提供一定的事务隔离机制来解决。

（1）锁机制

锁机制用于管理对共享资源的并发访问。解决写-写冲突问题。在读取数据前，对其加锁，防止其它事务对该数据进行修改。

• 悲观锁：往往依靠数据库提供的锁机制。

• 乐观锁：大多是基于数据版本记录机制来实现。

（2）MVCC多版本并发控制

解决读-写冲突问题。不用加锁，通过一定机制生成一个数据请求时间点时的一致性数据快照， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。这样在读操作的时候不需要阻塞写操作，写操作时不需要阻塞读操作。

MySQL/InnoDB锁机制

MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般  
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

加锁方式

• 两段锁：加锁/解锁是两个不相交的阶段，加锁阶段：只加锁，不解锁。解锁阶段：只解锁，不加锁。

锁的兼容性

InnoDB实现了两种标准的行级锁：

• 共享锁：共享锁（Share Locks，简记为S锁）又称为读锁。其它事务可以并发地读取数据，可以再加共享锁，但任何事务都不能获取数据上的排它锁，直至已经释放所有共享锁。
• 排它锁：排它锁（Exclusive lock，简记为X锁）又称为写锁。若事务对数据对象加上了排它锁，则只允许该事务对数据对象进行读取和修改，其它事务不能再对数据对象加任何类型的锁，直到该事务释放对象上的排它锁。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。
  • 对于共享锁大家可能很好理解，就是多个事务只能读数据不能改数据。 
    对于排他锁大家的理解可能就有些差别，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

InnoDB支持多粒度锁定，这种锁定允许在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上的加锁操作，InnoDB支持一种额外的锁方式，我们称之为意向锁。
若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象 进行上锁，那么首先需要对粗粒度的对象上锁。例如图，如果需要对页上的记录r进 行上X锁，那么分别需要对数据库A、表、页上意向锁IX，最后对记录r上X锁。若  其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。举例来说，在对记 录r加X锁之前，已经有事务对表1进行了S表锁，那么表1上已存在S锁，之后事务 需要对记录r在表1上加上IX，由于不兼容，所以该事务需要等待表锁操作的完成。
       InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主 要是为了在一个事务中揭示下一行将被请求的锁类型。 由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以 外的任何请求。

• 意向锁：为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
  • 意向共享锁（IS）表示事务准备给数据行加入共享锁，事务在给一个数据行加共享锁之前必须先取得该表的IS锁。
  • 意向排它锁（IX）表示事务准备给数据行加入排它锁，事务在给一个数据行加排它锁之前必须先取得该表的IX锁。

• 注意事项
  1.意向锁是表锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是InnoDB自动加的，不需要用户干预。
  2.IX和IS是表锁，不会与表锁发生冲突，只会与行锁发生冲突。

兼容情况：
InnoDB的锁兼容情况如下所示。

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。 
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。 
事务可以通过以下语句显式给记录集加共享锁或排他锁：

一致性锁定读

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。对读取的行记录加一个共享锁（S锁）。其他事务可以向被锁定的记录加S锁，但如果加X锁，则被阻塞。
• 排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。对读取的行记录加一个排它锁（X锁）。其他事务想在这些行上加任何锁都会被阻塞

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

对于一致性非锁定读，即使读取的行被使用select...for update，也是可以进行读取的。另外，select...for update、select...lock in share mode必须在一个事务中，当事务提交了锁也就释放了。禁止自动提交，在使用上述两句selset锁定语句时，务必加上begin、start transaction或者set autocommit=0。

一致性的非锁定读操作 MVCC 

 一致性的非锁定行读是指InnoDB通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行删除和更新操作，这时读取操作不会因此而等待行上锁的释放，相反，InnoDB会去读取行的一个快照数据。

 　　

　　之所以称为非锁定读，是因为不需要等待访问的行上X锁的释放。快照数据是指该行之前版本的数据，该实现是通过Undo段来实现。而Undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有必要对历史的数据进行修改。

 　　非锁定读的机制大大提高了数据读取的并发性，在InnoDB默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事物隔离级别下，读取的方式不同，并不是每个事务级别下读取的都是一致性读。同样，即使都是使用一致性读，但是对于快照数据的定义也不同。

　　通过上图可以知道，快照数据其实就是当前行数据之前的历史版本，可能有多个版本。一个行可能有不止一个快照数据，我们称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（MVCC）。

　　在Read Commited和Repeatable Read(InnoDB默认)下，InnoDB使用非锁定的一致性读。然而，对于快照数据的定义却不相同。Read commited下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。在Repeatable事务隔离级别下和Repeatable read级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

对于Read commited级别，这违反了事务ACID的隔离性。（隔离性：一个事务的影响在该事务提交前对其他事务都不可见）

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

 

创建tab_with_index表，id字段有普通索引：

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
mysql> alter table tab_with_index add index id(id);
然后使用相同的查询语句session_2就不用等待了。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
在下面的例子中，表tab_with_index的id字段有索引，name字段没有索引：

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。 
在下面的例子中，表tab_with_index的id字段有主键索引，name字段有普通索引：

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突 时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。 

锁算法

InnoDB主要实现了三种行锁算法。

• Record Lock：记录锁，锁定一个行记录

• Gap Lock：间隙锁，锁定一个区间，但不包含记录本身。

• Next-Key Lock：记录锁+间隙锁，锁定行记录和区间

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，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的规范里面RR事务级别是可能出现幻读的，InnoDB通过间隙锁避免了这种情况。这个实现和规范有所差别。另外，Next-Key Lock是Repeated Read级别才会有的，在Read Committed级别并不存在。

锁问题                             

通过锁可以实现事务隔离性的要求，使得事务可以并发地工作。因为事务隔离性的要求，锁只会带来3种问题：丢失更新、脏读、不可重复读。如果可以防止这三种情况的发生，那将不会产生并发异常。

（1）丢失更新

 　　出现下面的情况时，就会发生丢失更新：

 

1. 事务T1查询一行数据，放入本地内存，并显示给一个终端用户User1。
2. 事务T2也查询该行数据，并将取得的数据显示给终端用户User2。
3. User1修改这行记录，更新数据库并提交。
4. User2修改这行记录，更新数据库并提交。

显然，这个过程中用户User1的修改更新操作“丢失”了。（例如如果银行转账丢失了更新操作，后果将很严重。）

要避免丢失更新发生，其实需要让这种情况下的事务变成串行操作，而不是并发的操作。即在上述4种的第1种情况下，对用户读取的记录加上一个排他锁，同样，发生第2种情况下的操作时，用户也需要加上一个排他锁。这种情况下，第2步就必须等待第1、3步完成，最后完成第4步。

（2）脏读

脏数据指在缓冲池中被修改的数据，并且还没有被提交。

（脏数据和脏页有所不同，脏页：指在缓冲池中已被修改的页，但还没有刷新到磁盘，即数据库实例内存中的页和磁盘的页中的数据是不一致的。）

如果读到了脏数据，即一个事务可以读到另一个事务中未提交的数据，则显然违反了数据库的隔离性。

而对于脏页的读取是非常正常的。脏页是因为数据库实例内存和磁盘的异步同步造成的，这并不影响数据的一致性。）

脏读就是指在不同的事务下，可以读到另外事务未提交的数据。简单来说，就是可以读到脏数据。

脏读很少发生，因为脏读发生的条件是需要需要隔离级别为read uncommited，而一般数据库隔离级别至少设置为read commited。

（3）不可重复读

不可重复读是指在一个事务内多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么在第一个事务的两次读数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读。

一般来说不可重复度的问题是可以接受的，因为其读到的是已经提交的数据，本身并不会带来很大的问题。因此，很多数据库厂商将数据库事务的默认隔离级别设置成READ COMMITTED，在这种隔离级别下允许不可重复读的现象。

InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。在Next-Key Lock算法下，对于索引的扫描，不仅仅是锁住扫描到的索引，而且还能锁住这些索引覆盖的范围。因此对于这个范围内的插入都是不允许的，这样就避免了另外的事务在这个范围内插入数据导致的不可重复读的问题。因此，InnoDB存储引擎的默认事务隔离级别是READ REPEATABLE，采用Next-Key Lock算法，就避免了不可重复读的现象。

（4）幻读

是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

不同的隔离级别对并发问题的解决情况如图：

　　不可重复读与脏读的区别：脏读是读到未提交的数据；而不可重复读读到的是确实已提交的数据，但是其违反了数据库事务一致性的要求。

 　　不可重复读与幻读的区别：不可重复读的重点是修改： 同样的条件，你读取过的数据，再次读取出来发现值不一样了。幻读的重点在于新增或者删除：同样的条件，第 1 次和第 2 次读出来的记录数不一样。

阻塞

因为不同锁之间的兼容性关系，所以在有些时刻，一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源。在InnoDB存储引擎的源代码中，用Mutex数据结构来实现锁，在访问资源前需要用mutex_enter函数进行申请，在资源访问或修改完毕后立即执行mutex_exit函数。当一个资源已被一个事务占有时，另一个事务执行mutex_enter函数会发生等待，这就是阻塞。阻塞并不是意见坏事，阻塞是为了保证事务可以并发并且正常运行。

死锁

如果程序是串行的，那么不可能发生死锁。死锁只发生于并发的情况，数据库就是一个并发进行着的程序，因此可能会发生死锁。

（死锁的一个经典案例：A等待B，B在等待A。）

InnoDB存储引擎有一个后台的锁监控线程，该线程负责查看可能的死锁问题，并自动告知用户。 InnoDB存储引擎并不会回滚大部分的错误异常，但是死锁除外。发现死锁后，InnoDB存储引擎会马上回滚一个事务。

锁升级

锁升级是指将当前锁的粒度降低。例如，数据库可以把一个表的1000个行锁升级成一个页锁，或者将页锁升级为表锁。如果数据库的设计中认为锁是一种稀有资源，而且想避免锁的开销，那数据库中会频繁出现锁升级现象。