mysql学习-----锁概念

锁实现分类

• 乐观锁：顾名思义以乐观的想法,在操作数据库时（更新、插入、删除等操作），认为这次操作不会导致冲突，即在操作数据时不进行其它处理（不加锁），在更新后再判断是否冲突

• 悲观锁：与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟Java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

注意：
- 乐观锁不是数据库自带的，需要我们自己去实现。
- 悲观锁是由数据库自己实现了的

乐观所实现：通过一个版本字段

具体实现

在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

示例

举例：下单操作包括3步骤
1. 查询出商品信息
select (status,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};

悲观锁实现 ：共享锁和排它锁是悲观锁的不同的实现

共享锁【S锁】

• 概念：共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。

• 使用：在执行语句后面加上lock in share mode就代表对某些资源加上共享锁了

• 示例

  开启一个事物，并不提交 （即添加一个共享锁）
  begin;
  select * from user_info where user_phone=’13911111111’ lock in share mode;

  再在另一个事物中

  执行：update user_info set user_age=20 where user_phone=’13911111111’;
  会进入卡顿状态，几秒之后，提示错误信息
  [SQL]update user_info set user_age=20 where user_phone=’13911111111’;
  [Err] 1205 - Lock wait timeout exceeded; try restarting transaction
  结果证明，对于该记录加锁成功了，在上一条记录还没有commit之前，这条id=1的记录被锁住了，只有在上一个事务释放掉锁后才能进行操作，或用共享锁才能对此数据进行操作。

  执行：update user_info set user_age=20 where user_phone=’13911111111’ lock in share mode;
  报错（原因在后面的原理分析中解释：对insert、update、delete等会自动添加排它锁）
  [SQL]update user_info set user_age=20 where user_phone=’13911111111’ lock in share mode;
  [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘lock in share mode’ at line 1

  执行：select * from user_info where user_phone=’13911111111’ lock in share mode;
  结果成功

排它锁【X锁】

• 概念：排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁
• 使用：在执行语句后面加上for update就代表对某些资源加上排它锁了

• 示例

  开启一个事物，并不提交 （开启一个排它锁）
  begin;
  select * from user_info where user_phone=’13911111111’ for update;

  再在另一个事物中

  执行：select user_name from user_info where user_phone=’13911111111’ for update;
  会进入卡顿状态，几秒之后，提示错误信息
  [SQL]select user_name from user_info where user_phone=’13911111111’ for update;
  [Err] 1205 - Lock wait timeout exceeded; try restarting transaction
  结果证明，对于该记录加锁成功了，在上一条记录还没有commit之前，这条id=1的记录被锁住了，只有在上一个事务释放掉锁后才能进行操作。

锁级别分类

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

类别

• 表级锁
• 页面锁
• 行级锁

使用

• MyISAM和MEMORY存储引擎采用的是表级锁（table-level-locking）
• BDB存储引擎采用的是页面锁（page-level-locking），同时也支持表级锁
• InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下是采用行级锁

优缺点

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

查看被锁sql

– 查看那些表锁到了
show OPEN TABLES where In_use > 0;
– 查看进程号
show processlist;
–删除进程
kill 1085850；

原理（InnoDB）

多版本的并发控制协议MVCC

概念

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

分类

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

以MySQL InnoDB为例：

• 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

  select * from table where ?;

• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

  select * from table where ? lock in share mode;
  select * from table where ? for update;
  insert into table values (…);
  update table set ? where ?;
  delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

为什么将 插入/更新/删除 操作，都归为当前读？可以看看下面这个 更新 操作，在数据库中的执行流程：

从图中，可以看到，一个Update操作的具体流程。当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

注：根据上图的交互，针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。

参考：
http://blog.csdn.net/puhaiyang/article/details/72284702
http://www.cnblogs.com/zhaoyl/p/4121010.html