mysql的锁定机制_MySQL锁定机制

一、乐观锁与悲观锁

1.1 乐观锁与使用版本号实现乐观锁

乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则对用户返回错误信息，让用户决定如何去处理。 实现方式为版本号和时间戳。

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。// 1.查询出商品信息

select status, version from t_goods where id=#{id};

// 2.根据商品信息生成订单

// 3.修改商品status为2

update t_goods set status=2,version=version+1 where id=#{id} and version=#{version};

可以看出第三条SQL语句的where条件是version=#{version}，也就是version字段没有发生变化的时候才执行更新操作，version字段没有变化就意味着在读取version与修改version期间没有事务修改数据，那么version也就不会发生变化，也就可以执行更新操作。

1.2 悲观锁

悲观锁指的是外界对数据修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。具体包括行锁、表锁等。

二、MyISAM的表级锁

MySQL的MyISAM存储引擎只支持表级锁，有两种模式：表共享读锁(table read lock)和表独占写锁(table write lock)。锁模式的兼容性如下None读锁写锁读锁兼容兼容冲突

写锁兼容冲突冲突

也就是说对于MyISAM表的读操作不会因为不同进程访问资源而发生阻塞，而对于MyISAM表的写操作会阻塞其他用户对同一表的读和写操作。

读锁和写锁的加锁方式以及解锁方式分别为：lock table table_name read;

lock table table_name write;

unlock tables;

在读锁加锁期间，不管是当前session还是其他session都是可以读取数据的，但是加锁的session不可以更新数据，因为加的是读锁，其余的session的更新操作会被阻塞等待直到读锁释放才会更新成功。

在写锁加锁期间，只有当前session可以执行查询或更新操作，因为写锁与读锁以及写锁不兼容，其余session的查询与更新操作会被阻塞等待直到拥有锁的session释放锁为止。

可以通过如下命令获取MyISAM表级锁的争用情况：show status like 'table%';

该命令显示的具体参数含义如下：Table_locks_immediate：产生表级锁定的次数；

Table_locks_waited：出现的比较锁定争用而发生等待的次数；

如果Table_locaks_waited的值比较高,那么说明存在比较严重的表级锁争用情况。MyISAM在读操作占主导的情况下是高效的，可一旦出现大量读写操作并发，与InnoDB相比，MyISAM的执行效率就会直线下降。

MyISAM存储引擎有个系统变量，concurrent_insert，专门用以控制其并发插入的行为，其值分别是0、1、2。解释如下：当值为0时，不允许并发插入；

当值为1时，如果表中没有被删除的行，MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录；

当值为2时，无论表中有没有被删除的行，都允许在表尾并发插入记录。

三、InnoDB行级锁

InnoDB支持行级锁，支持事务处理。在处理数据量上InnoDB支持海量数据，并且在具有良好的索引的基础上，InnoDB的查询速度要比MyISAM快。

3.1 注意：事务与并发的区别

事务通常包含一系列更新操作，这些更新操作是一个不可分割的逻辑工作单元。要么全部成功，要么全部失败。一个session或进程或线程的某个操作如果包含一系列不可分割的逻辑单元那么就是一个事务，而并发指的是有可能包含事务操作的多进程/线程同时对某一资源进行操作，所以，在多进程/线程并发中，保持某进程/线程事务的完整性是安全且高效并发的基础。也就是说，在多进程/线程并发操作某一资源时，某个进程/线程的对资源一系列更新操作的抽象(也就是事务)安全(即事务安全)必须得到保证，不然就会产生数据错误。包含以下几种：丢失更新：一个事务读取数据并提交修改，覆盖了从上次读取之后其他事务提交的修改(不是基于最新的数据进行修改)；

读脏数据：一个事务读到另外一个事务还没有提交的数据；

不可重复读：一个事务两次读取一条记录之间有其他事务修改了改记录，导致两次读取的结果不一样；

幻读：一个事务两次读取多条记录之间有其他事务进行了添加或删除的操作，导致两次读取记录的数量不一致。

因此为了避免数据错误，必须保证并发事务的安全性。可以通过数据库隔离级别来实现不同程度上的安全隔离。如下纵向为隔离级别、横向为读数据一致性及允许并发的副作用读数据一致性脏读不可重复读幻读未提交读最低级别，只能保证不读取物理上损坏的数据是是是

已提交读语句级否是是

可重复读事务级否否是

可序列化最高级别，事务级否否否

可见，事务的隔离级别越严格，并发的副作用就小，但付出的代价越大。

事务与并发总结：事务安全是在并发中出现的，没有并发就不会产生事务安全。MyISAM不支持事务即不支持一系列不可分割的更新操作组成的逻辑工作单元，所以就不会产生事务安全问题。但是InnoDB支持事务操作，那么就会存在上述事务安全问题。为了解决上述事务安全问题，必须首先关闭MySQL自动提交(因为自动提交会使得每一条更新语句被执行从而数据被持久化)，然后执行一系列更新语句组成的逻辑工作单元后再显式提交事务(commit)，出问题还可以回滚(rollback)。也可以不关闭自动提交，直接显式开启事务(start transaction)即可也是同样的效果。这是一个session/进程/线程对自己事务的处理方式从而保证自己的一系列操作是完整的，但是高并发环境下，进程/线程之间各自事务的安全性保证则要通过事务的隔离级别来实现。同样的，并发操作必须加锁。即线程/进程的原子事务的子操作必须加锁保证对某一条记录的修改是独占的，进程/线程之间的事务安全通过隔离级别来保证。

3.2 InnoDB行级锁模式、事务下共享锁与排它锁的使用流程锁模式共享锁(S)排它锁(X)意向共享锁(IS)意向排它锁(IX)共享锁(S)兼容冲突兼容冲突

排它锁(X)冲突冲突冲突冲突

意向共享锁(IS)兼容冲突兼容兼容

意向排它锁(IX)冲突冲突兼容兼容

各个锁的含义如下：共享锁(S)：允许一个事务读一行数据时，阻止其他的事务读取相同数据的排它锁；

排它锁(X)：允许获得排它锁的事务更新数据，阻止其他事务取得相同数据的共享锁和排它锁；

意向共享锁(IS)：事务打算给数据行加行共享锁。事务在给一个数据行加共享锁前必须先取得该表的IS锁；

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

如果一个事务请求的锁模式与当前的锁模式兼容，InnoDB就将请求的锁授予该事务，如果两者不兼容，那么该事务就要等待锁释放。

意向锁是InnoDB存储引擎自动加的，对于普通的SELECT语句，InnoDB不会加任何锁，对于INSERT、UPDATE、DELETE语句，InnoDB会自动给涉及的数据加排它锁，InnoDB可以通过如下语句添加共享锁(S)与排它锁(X)：SQL语句 LOCK IN SHARE MODE;

SQL语句 FOR UPDATE；

使用InnoDB共享锁与排它锁必须满足以下几个条件：设置autocommit的值是OFF或；

表的数据引擎是支持事务的，比如InnoDB数据引擎；

如果不管autocommit，手段在事务里执行操作，这个时候要使用begin或者start transaction开启事务；

不要在锁定事务规定的时间外使用共享锁和排它锁。

之所以有上述四个条件是因为InnoDB是支持事务的，而InnoDB又是自动提交的，这和事务一次性提交不符，所以先要关闭事务自动提交，或者直接使用start transaction开启事务，执行完事务中的SQL语句后再提交(commit)。

以上条件也是InnoDB存储引擎在支持事务的情况下的获取共享锁或排它锁实现数据操纵的流程。

需要注意的是，InnoDB行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁，否则使用表级锁。

3.3 InnoDB在不同隔离级别下加锁的差异

在不同隔离级别下，InnoDB处理SQL语句时所采用的一致性和需要的锁是不同的。

对于SQL语句而言，隔离级别越高，InnoDB存储引擎给记录添加的锁就越严格，产生锁冲突的可能性就越高(因为高隔离级别是保证了某一个事务的整体完整性，因此其余事务就会产生锁等待)，对并发的性能影响就越大。因此，应该尽量使用较低的隔离级别，以降低并发中的锁争用的几率。

对于一些需要使用较高隔离级别的情况，可以通过如下操作更换隔离级别：set session transaction isolation level repeatable read;

set session transaction isolation level serializable;

3.4 间隙锁(Net-Key锁)

在更新InnoDB存储引擎表中的某个区间数据时，将会锁定这个区间的所有记录，即使这个区间中的某条记录不存在，该条记录也会被锁住，这个时候，如果另外一个session往这个表中添加一条记录时，此时必须等到上一个事务释放锁资源。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，如果没有加间隙锁，如果其他事务添加某个区间的某条记录，那么就会发生幻读；另一方面，是为了满足其恢复和赋值的需求。