mysql的锁的概述
锁的概率：某个链接对某个资源(可以是一张表，也有可能是一行或者多行记录)进行加锁操作，根据锁的不同功能，决定在加锁期间,其他链接是否允许对该资源有相应的处理权限。
那么mysql究竟有哪几种类锁呢？
一、按操作划分，可分为DML锁、DDL锁
二、按锁的粒度划分，可分为表级锁、行级锁、页级锁（mysql BDB支持）
三、按锁级别划分，可分为共享锁、排他锁
四、按加锁方式划分，可分为自动锁、显示锁
五、按使用方式划分，可分为乐观锁、悲观锁
DML锁（data locks，数据锁），用于保护数据的完整性，其中包括行级锁(Row Locks (TX锁))、表级锁(table lock(TM锁))。 DDL锁（dictionary locks，数据字典锁），用于保护数据库对象的结构，如表、索引等的结构定义。其中包排他DDL锁（Exclusive DDL lock）、共享DDL锁（Share DDL lock）、可中断解析锁（Breakable parse locks）
共享锁
共享锁就是只针对update时候加锁，在未对update操作提交之前，其他事务只能够获取最新的记录但不能够update操作。
举个例子说吧，假如说事务A读到一条记录，并且修改了该条记录的一个字段的值a改成了b，正在这个时候事务B也读到了这条记录并且获取的A事务修改后的字段值b，事务B想把该字段的值改为c，在此时事务A的修改操作并未提交。那对不起了事务B，因为是事务A先对该记录加锁的，你此时还没有对该条记录上锁的权限哦，不要急哦，等事务A提交之后你就可以改了。
排他锁
一开始就对记录上锁了，在本事务未提交之前别的事务无权进行任何操作。
还是用上个例子吧，事务A查到一个记录，并修改了其中的一个字段a的值为b，这个时候事务B来获取这条记录，我靠，怎么获取不到呢？原来是排他锁在作怪，这是什么情况。原来排他锁在获取记录的时候就对这条记录上了锁，而且别的事务连获取这条记录的权限都没有，更别提要修改了，必须等到事务A提交了事务释放了锁之后别的事务才能够获取到这条记录。
这两种锁各有利弊，具体使用要看你项目的具体业务的。
- 假如你的项目对并发很高对效率要求很高，那么你该选用共享锁，因为在别的事务对某些记录上锁后在事务未提交之前其他事务是有权限去查看的，但是当出现意外导致事务回滚时候，其他事务会多进行一步操作，那就是重新获取这些对象了，不过这比排队等锁要好多了。
- 假如你的项目并发数不是很多，同时对整个业务的原子操作要求很高这个时候排他锁是很不错的选择。
mysql的锁的级别的区别
mysql究竟能对那些对象上锁呢？现在学习mysql的锁的级别了。
mysql锁的级别分为三类：页级锁，表级锁，行级锁。
那有三个级别的锁，mysql究竟用的是哪个级别的呢？这个还得看你用的是什么mysql的引擎了
mysql大概有下面几个数据库引擎和对应的锁级别
- MyISAM引擎：使用的是表级锁。理解为锁住整个表，可以同时读，写不行。
- MEMORY/heap：使用的是表级锁。理解为锁住整个表，可以同时读，写不行。
- BDB：使用的是页级锁，它也支持表级锁。一次锁定相邻的一组记录。
- InnoDB：使用的是行级锁，它也支持表级锁。单独的一行记录加锁 。
三种锁的特性可大致归纳如下：
- 1） 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。分为表共享读锁（共享锁）与表独占写锁（排他锁）。
- 2） 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 行级锁分为共享锁 和 排他锁。
- 3） 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
这里我们主要讨论InnoDB存储引擎，也谈论的是行级锁，一般的在秒杀系统中我们会对商品库存使用行级锁，因为秒杀的时候库存是一个很重要的数据，我们在创建数据库的表时可能会出现下面这样的设置：
ENGINE = InnoDB AUTO_INCREMENT=10 DEFAULT CHARACTER SET = utf8 comment='用户表'将引擎设置为InnoDB，InnnoDB与其他引擎的不同：一是支持事务（TRANCSACTION），二是采用了行级锁。
InnoDB中两种模式的行级锁：(也就是最开始讨论的那种锁)
- 1）共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
( Select * from table_name where ……lock in share mode)- 2）排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和 排他写锁。(select * from table_name where…..for update)
为了允许行锁和表锁共存，实现多粒度锁机制；同时还有两种内部使用的意向锁（都是表锁），分别为意向共享锁和意向排他锁。意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
InnoDB行锁模式兼容性列表
| 请求锁模式 是否兼容 当前锁模式 | X | IX | S | IS |
| X | 冲突 | 冲突 | 冲突 | 冲突 |
| IX | 冲突 | 兼容 | 冲突 | 兼容 |
| S | 冲突 | 冲突 | 兼容 | 兼容 |
| IS | 冲突 | 兼容 | 兼容 | 兼容 |
意向锁是InnoDB自动加的，不需要用户干预。
对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）；对于一般的Select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。
共享锁：SELECT … LOCK IN SHARE MODE;
排他锁：SELECT … FOR UPDATE;
注意：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
- 在不通过索引条件查询的时候,InnoDB 确实使用的是表锁,而不是行锁。
- 由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的。应用设计的时候要注意这一点。
- 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
- 即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。
行级锁的优缺点
行级锁定的优点：
- 当在许多线程中访问不同的行时只存在少量锁定冲突。
- 回滚时只有少量的更改。
- 可以长时间锁定单一的行。
行级锁定的缺点：
- 比页级或表级锁定占用更多的内存。
- 当在表的大部分数据上使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。
（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
行级锁的死锁
MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引.在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。
当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。
在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁之后，InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。
那InnoDB是以什么来为标准判定事务的大小的呢？MySQL官方手册中也提到了这个问题，实际上在InnoDB发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多，在死锁中就越不会被回滚掉。
但是有一点需要注意的就是，当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。
需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。
下面就通过实例来介绍几种避免死锁的常用方法：
1、在应用中，如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率（即应该直接申请足够级别的锁，排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁）；
3、在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
4、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
5、在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
6、当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁。这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。
悲观锁与乐观锁
之前说过数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。
乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念，像memcache、hibernate、tair等都有类似的概念。
针对于不同的业务场景，应该选用不同的并发控制方式。所以，不要把乐观并发控制和悲观并发控制狭义的理解为DBMS中的概念，更不要把他们和数据中提供的锁机制（行锁、表锁、排他锁、共享锁）混为一谈。其实，在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。
下面具体来学习一下悲观锁和乐观锁:
悲观锁
在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。
悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。
悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）
在数据库中，悲观锁的流程如下：
在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。
如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。
如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。
MySQL InnoDB中使用悲观锁
要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。set autocommit=0;
//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;上面的查询语句中，我们使用了select…for update的方式，这样就通过开启排他锁的方式实现了悲观锁。此时在t_goods表中，id为1的 那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。
上面我们提到，使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。
优点与不足
悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数
乐观锁
在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。乐观事务控制最早是由孔祥重（H.T.Kung）教授提出。
乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。
相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。
数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。
实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。
使用版本号实现乐观锁
使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。
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};优点与不足
乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。
sql语句分析背后的锁实现(基于InnoDB存储引擎)
MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。
在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以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操作；然后在读取下一条加锁，直至读取完毕。
传统RDBMS加锁的一个原则，就是2PL (二阶段锁)：Two-Phase Locking。相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下面，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现。
从上图可以看出，2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。
hibernate中通过行级锁实现的悲观锁。
一些例子：
假设有个表单products ，里面有id跟name二个栏位，id是主键。
1: 明确指定主键，并且有此条记录，执行row lock。若查无此记录，则使用共享间隙锁Next-Key锁。
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and name="cat" FOR UPDATE;2: 无主键，执行table lock。
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;3: 主键不明确，table lock。
SELECT * FROM products WHERE id<>'3' FOR UPDATE;注意： FOR UPDATE仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。此外，如果A与B都对表id进行查询但查询不到记录，则A与B在查询上不会进行row锁，但A与B都会获取排它锁，此时A再插入一条记录的话则会因为B已经有锁而处于等待中，此时B再插入一条同样的数据则会抛出Deadlock found when trying to get lock; try restarting transaction然后释放锁，此时A就获得了锁而插入成功。
Mysql事务与隔离级别
事务就是一段sql 语句的批处理，但是这个批处理是一个atom（原子） ，不可分割，要么都执行，要么回滚（rollback）都不执行。只有InnoDB引擎的数据库transaction_safe table 才能支持事务。事务的使用比较简单,这里介绍事务的四种隔离级别:
MySQL/InnoDB定义的4种隔离级别：
Read Uncommited
这种隔离级别可以让当前事务读取到其它事物还没有提交的数据。这种读取应该是在回滚段中完成的。这种隔离级别是最低的Read Committed (RC)
这种隔离级别可以让当前事务读取到其它事物已经提交的数据。这种隔离级别会导致引发不可重复读，和幻读。Repeatable Read (RR)
这种隔离级别可以保证在一个事物中多次读取特定记录的时候都是一样的。这种隔离级别会导致引发幻读。Serializable
这种隔离级别将事物放在一个队列中，每个事物开始之后，别的事物被挂起。同一个时间点只能有一个事物能操作数据库对象。这种隔离级别对于数据的完整性是最高的，但是同时大大降低了系统的可并发性。
Serializable隔离级别下，采用的是单线程来解决并发访问的问题，也就是说在某一段时间内，只能有一个用户对数据库进行操作，导致其它用户阻塞。导致数据库的访问性能很差。并发度急剧下降，在MySQL/InnoDB下不建议使用。
数据库隔离级别的应用:
在分布式的系统中，通常会有多个线程连接到数据库中同时对一个表进行操作(这里的同时并不表示同一个时间点，而是同时竞争cpu的资源，至于如何调度，就要看线程和操作系统如何进行调度了)，这种情况下如果会话的事物设置不当，就会导致数据混乱，常常会出现以下三种情况(假设现在系统中有两个会话A和B，同时对表T_Test操作)：
脏读
如果有A向B 做了这个操作：
update account set money=money+100 where name='B';在没有commit 之前B 查询：
select money from account where name='B';找到了没有提交的money ，之后A在此时有rollback ，B 再查询，100 不见了。为了避免则提高数据库的隔离级别：read committed 。就是只能读取提交后的数据。
不可重复读
上述示例中说明的就是我们不能读取一个事务的中间状态。 而重复读是指我们每次读取到的结果都要一直。 这个也是mysql 默认的级别。
幻读
在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。和不可重复读的区别是：不可重复读是读取到了别人对表中的某一条记录进行了修改，导致前后读取的数据不一致。 虚读是前后读取到表中的记录总数不一样，读取到了其它事务插入的数据。比如现在有 A 和 B 两个应用程序，他们并发访问了数据库中的某一张表，假设表中有 3 条记录，B 执行查询操作， 第一次查询表得到了 3 条记录。此时 A 对表进行了修改，增加了一条记录，当 B 再次查询表的时候，发现多了一条数据。这种情况就造成了 B 的虚读。但是虚读是不一定每次都发生的，这种情况是不确定的。为了避免虚读，我们可以将事物隔离级别设置为 serializable 如果设置成了这种级别，那么数据库就变成了单线程访问的数据库，导致性能降低很多。
一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。
脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的.
summary：
（1）Serializable：可避免脏读、不可重复读、虚读情况的发生。
（2）Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读，是 mysql 默认的事务隔离级别）
（3）Read committed：可避免脏读情况发生。（读取已提交的数据）
（4）Read uncommitted：最低级别，以上情况均无法保证。（读取到了未提交的数据）
这四种隔离级别用的技术就是锁，并且除了Read uncommitted，全部忽略了快照读而是当前读。。。
一条简单SQL的加锁实现分析
在介绍完一些背景知识之后，本文接下来将选择几个有代表性的例子，来详细分析MySQL的加锁处理。当然，还是从最简单的例子说起。分析他们加什么锁？
SQL1：select * from t1 where id = 10;
SQL2：delete from t1 where id = 10;针对这个问题，该怎么回答？我能想象到的一个答案是：
SQL1：不加锁。因为MySQL是使用多版本并发控制(MVCC)的，读不加锁。
SQL2：对id = 10的记录加写锁 (走主键索引)。
4种隔离级别比较
| 读数据一致性及允许的并发副作用 隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
| 未提交读（Read uncommitted） | 最低级别，只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
| 已提交度（Read committed） | 语句级 | 否 | 是 | 是 |
| 可重复读（Repeatable read） | 事务级 | 否 | 否 | 是 |
| 可序列化（Serializable） | 最高级别，事务级 | 否 | 否 | 否 |
最后要说明的是：各具体数据库并不一定完全实现了上述4个隔离级别，例如，Oracle只提供Read committed和Serializable两个标准隔离级别，另外还提供自己定义的Read only隔离级别；SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL 支持全部4个隔离级别，但在具体实现时，有一些特点，比如在一些隔离级别下是采用MVCC一致性读，但某些情况下又不是，这些内容在后面的章节中将会做进 一步介绍。
间隙锁（Next-Key锁）
当我们用范围条件而不是相等条件检索数据（where条件必须是索引），并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。对于间隙锁，会在下面的文章中着重测试，并总结。
例：
假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：
mysql> select * from emp where empid > 100 for update;是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的：
（1）防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；
（2）为了满足其恢复和复制的需要。
很显然，在使用范围条件检索并锁定记录时，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
除了间隙锁给InnoDB带来性能的负面影响之外，通过索引实现锁定的方式还存在其他几个较大的性能隐患：
（1）当Query无法利用索引的时候，InnoDB会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；
（2）当Query使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所只想的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；
（3）当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定。
因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。
恢复和复制的需要，对InnoDB锁机制的影响
MySQL通过BINLOG录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复 制 （可以参见本书“管理篇”的介绍）。MySQL的恢复机制（复制其实就是在Slave Mysql不断做基于BINLOG的恢复）有以下特点。
一是MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。这与Oracle数据库不同，Oracle是基于数据库文件块的。
二是MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。这点也与Oralce不同，Oracle是按照系统更新号 （System Change Number，SCN）来恢复数据的，每个事务开始时，Oracle都会分配一个全局唯一的SCN，SCN的顺序与事务开始的时间顺序是一致的。
从上面两点可知，MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了 ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无 论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的，有关InnoDB在不同隔离级别下加锁的差异在下一小节还会介绍。
另外，对于“insert into target_tab select * from source_tab where ...”和“create table new_tab ...select ... From source_tab where ...(CTAS)”这种SQL语句，用户并没有对source_tab做任何更新操作，但MySQL对这种SQL语句做了特别处理。先来看如表 20-14的例子。
表20-14 CTAS操作给原表加锁例子
| session_1 | session_2 |
| mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) | mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) |
| mysql> insert into target_tab select d1,name from source_tab where name = '1'; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 | |
| mysql> update source_tab set name = '1' where name = '8'; 等待 | |
| commit; | |
| 返回结果 commit; |
在上面的例子中，只是简单地读 source_tab表的数据，相当于执行一个普通的SELECT语句，用一致性读就可以了。ORACLE正是这么做的，它通过MVCC技术实现的多版本 数据来实现一致性读，不需要给source_tab加任何锁。我们知道InnoDB也实现了多版本数据，对普通的SELECT一致性读，也不需要加任何 锁；但这里InnoDB却给source_tab加了共享锁，并没有使用多版本数据一致性读技术！
MySQL 为什么要这么做呢？其原因还是为了保证恢复和复制的正确性。因为不加锁的话，如果在上述语句执行过程中，其他事务对source_tab做了更新操作，就 可能导致数据恢复的结果错误。为了演示这一点，我们再重复一下前面的例子，不同的是在session_1执行事务前，先将系统变量 innodb_locks_unsafe_for_binlog的值设置为“on”（其默认值为off），具体结果如表20-15所示。
表20-15 CTAS操作不给原表加锁带来的安全问题例子
| session_1 | session_2 |
| mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql>set innodb_locks_unsafe_for_binlog='on' Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) | mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) |
| mysql> insert into target_tab select d1,name from source_tab where name = '1'; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 | |
| session_1未提交，可以对session_1的select的记录进行更新操作。 mysql> update source_tab set name = '8' where name = '1'; Query OK, 5 rows affected (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from source_tab where name = '8'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 8 | 1 | | 5 | 8 | 1 | | 6 | 8 | 1 | | 7 | 8 | 1 | | 8 | 8 | 1 | +----+------+----+ 5 rows in set (0.00 sec) | |
| 更新操作先提交 mysql> commit; Query OK, 0 rows affected (0.05 sec) | |
| 插入操作后提交 mysql> commit; Query OK, 0 rows affected (0.07 sec) | |
| 此时查看数据，target_tab中可以插入source_tab更新前的结果，这符合应用逻辑： mysql> select * from source_tab where name = '8'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 8 | 1 | | 5 | 8 | 1 | | 6 | 8 | 1 | | 7 | 8 | 1 | | 8 | 8 | 1 | +----+------+----+ 5 rows in set (0.00 sec) mysql> select * from target_tab; +------+------+ | id | name | +------+------+ | 4 | 1.00 | | 5 | 1.00 | | 6 | 1.00 | | 7 | 1.00 | | 8 | 1.00 | +------+------+ 5 rows in set (0.00 sec) | mysql> select * from tt1 where name = '1'; Empty set (0.00 sec) mysql> select * from source_tab where name = '8'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 8 | 1 | | 5 | 8 | 1 | | 6 | 8 | 1 | | 7 | 8 | 1 | | 8 | 8 | 1 | +----+------+----+ 5 rows in set (0.00 sec) mysql> select * from target_tab; +------+------+ | id | name | +------+------+ | 4 | 1.00 | | 5 | 1.00 | | 6 | 1.00 | | 7 | 1.00 | | 8 | 1.00 | +------+------+ 5 rows in set (0.00 sec) |
从上可见，设置系统变量innodb_locks_unsafe_for_binlog的值为“on”后，InnoDB不再对source_tab加锁，结果也符合应用逻辑，但是如果分析BINLOG的内容：
......
SET TIMESTAMP=1169175130;
BEGIN;
# at 274
#070119 10:51:57 server id 1 end_log_pos 105 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1169175117;
update source_tab set name = '8' where name = '1';
# at 379
#070119 10:52:10 server id 1 end_log_pos 406 Xid = 5
COMMIT;
# at 406
#070119 10:52:14 server id 1 end_log_pos 474 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1169175134;
BEGIN;
# at 474
#070119 10:51:29 server id 1 end_log_pos 119 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1169175089;
insert into target_tab select d1,name from source_tab where name = '1';
# at 593
#070119 10:52:14 server id 1 end_log_pos 620 Xid = 7
COMMIT;
......可以发现，在BINLOG中，更新操作的位置在INSERT...SELECT之前，如果使用这个BINLOG进行数据库恢复，恢复的结果与实际的应用逻辑不符；如果进行复制，就会导致主从数据库不一致！
通 过上面的例子，我们就不难理解为什么MySQL在处理“Insert into target_tab select * from source_tab where ...”和“create table new_tab ...select ... From source_tab where ...”时要给source_tab加锁，而不是使用对并发影响最小的多版本数据来实现一致性读。还要特别说明的是，如果上述语句的SELECT是范围条 件，InnoDB还会给源表加间隙锁（Next-Lock）。
因 此，INSERT...SELECT...和 CREATE TABLE...SELECT...语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中 应尽量避免使用。实际上，MySQL将这种SQL叫作不确定（non-deterministic）的SQL，不推荐使用。
如果应用中一定要用这种SQL来实现业务逻辑，又不希望对源表的并发更新产生影响，可以采取以下两种措施：
一是采取上面示例中的做法，将innodb_locks_unsafe_for_binlog的值设置为“on”，强制MySQL使用多版本数据一致性读。但付出的代价是可能无法用binlog正确地恢复或复制数据，因此，不推荐使用这种方式。
二是通过使用“select * from source_tab ... Into outfile”和“load data infile ...”语句组合来间接实现，采用这种方式MySQL不会给source_tab加锁。
什么时候使用表锁
对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁：
第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。
在InnoDB下，使用表锁要注意以下两点。
（1）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、InnoDB_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则，InnoDB将无法自动检测并处理这种死锁。
（2）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：
例如，如果需要写表t1并从表t读，可以按如下做：
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;InnoDB行锁优化建议
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：
a)尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；
b)合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；
c)尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
d)尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
e)在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。
（2）由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：
a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；
b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。
（3）可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：
mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
+-------------------------------+-------+InnoDB 的行级锁定状态变量不仅记录了锁定等待次数，还记录了锁定总时长，每次平均时长，以及最大时长，此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下：
InnoDB_row_lock_current_waits：当前正在等待锁定的数量；
InnoDB_row_lock_time：从系统启动到现在锁定总时间长度；
InnoDB_row_lock_time_avg：每次等待所花平均时间；
InnoDB_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
InnoDB_row_lock_waits：系统启动后到现在总共等待的次数；
对于这5个状态变量，比较重要的主要是InnoDB_row_lock_time_avg（等待平均时长），InnoDB_row_lock_waits（等待总次数）以及InnoDB_row_lock_time（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
锁冲突的表、数据行等，并分析锁争用的原因。具体方法如下：
mysql> create table InnoDB_monitor(a INT) engine=InnoDB;然后就可以用下面的语句来进行查看：
mysql> show engine InnoDB status;监视器可以通过发出下列语句来停止查看：
mysql> drop table InnoDB_monitor;设置监视器后，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。可能会有读者朋友问为什么要先创建一个叫InnoDB_monitor的表呢？因为创建该表实际上就是告诉InnoDB我们开始要监控他的细节状态了，然后InnoDB就会将比较详细的事务以及锁定信息记录进入MySQL的errorlog中，以便我们后面做进一步分析使用。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用“--console”选项来启动服务器以关闭写日志文件。
为了方便，以上都是通过阅读别人的文档拷贝过来后稍加修改的。
这里我专门对于间隙锁做了一些测试，用于补充我在阅读上边文章后对于间隙锁的一些疑问。
CREATE TABLE `t_test ` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
UNIQUE KEY `id` (`id` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
这里先创建一张t_test表，有id、name两列，id为唯一索引。并且准备测试使用的数据。
insert into t_test values(2,'小明');
insert into t_test values(11,'小丽');
insert into t_test values(12,'小蓝');
数据准备妥当，开始测试。我们先在RR（Repeatable Read）级别下测试。
先开启一个事物，并且使用索引为查询条件，但是该条索引值在表中不存在，从而当前事务在表中添加间隙锁。
事物1：select * from t_test where id=5 for update；
这个时候我们来查看另一个事物2中，可以使用那些操作，访问那些数据，从而判断事物1的加锁类型已经加锁范围。
| 事物2：insert into t_test values(3,'小黄'); | ×阻塞等待 |
| 事物2：insert into t_test values(1,'小黄'); | √ |
| 事物2：delete from t_test where id=2； | √ |
| 事物2：delete from t_test where id=11； | √ |
| 事物2：delete from t_test where id<=5； | √ |
| 事物2：update t_test set name='小明1' where id=2; | √ |
| 事物2：update t_test set name='小丽1' where id=11; | √ |
| 事物2：update t_test set name='小丽11' where id>=5; | √ |
| 事物2：select * from t_test where id=5 for update； | √ |
| 事物2：select * from t_test where id=2 for update； | √ |
| 事物2：select * from t_test where id=11 for update； | √ |
我们再来看看如果表中的id不是唯一索引而是普通索引的话，会有什么不同吗？
CREATE TABLE `t_test ` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
INDEX `id` (`id` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
使用同样的测试数据，并且使用与上边同样的隔离级别RR，同样的事务1；
| 事物2：insert into t_test values(3,'小黄'); | ×阻塞等待 |
| 事物2：insert into t_test values(2,'小黄'); | ×阻塞等待 |
| 事物2：insert into t_test values(11,'小蓝'); | √ |
| 事物2：insert into t_test values(1,'小黄'); | √ |
| 事物2：delete from t_test where id=2； | √ |
| 事物2：delete from t_test where id=11； | √ |
| 事物2：delete from t_test where id>=5； | √ |
| 事物2：delete from t_test where id<=5； | √ |
| 事物2：update t_test set name='小明1' where id=2; | √ |
| 事物2：update t_test set name='小丽1' where id=11; | √ |
| 事物2：update t_test set name='小丽11' where id>=5; | √ |
| 事物2：update t_test set name='小明11' where id<=5; | √ |
| 事物2：select * from t_test where id=5 for update； | √ |
| 事物2：select * from t_test where id=2 for update； | √ |
| 事物2：select * from t_test where id=11 for update； | √ |
我们根据表中的数据并且观察两个事物的现象后发现，使用唯一索引与普通说要，在RR级别下，区别不是很大。
| id | name |
| 2 | 小明 |
| 11 | 小丽 |
| 12 | 小蓝 |
以索引为查询条件（select操作时），并且该条索引的‘值’不存在于当前表中，使用共享间隙锁锁住了索引值为2至11之间灰色的区域（注意这里只有查询后返回结果为空时才会使用共享间隙锁。其他的只要返回是数据不为空时，并且使用的间隙锁都是排他间隙锁）。
因为insert操作中会先进行查找操作，唯一索引与普通索引在查找时有细微差别。
由于唯一索引具有不可重复的特性，所以查询的时候只需要查找到第一个符合条件的元素就会返回。
而普通索引准许重复，索引需要查找到符合条件的最后一个，然后在最后一个之后插入数据，所以插入id=2时阻塞等待，插入id=11时则成功。
测试完了RR级别，我们再测试一下RC级别（Read Commited）使用同样的数据，相同的操作是否有什么不同？
| id | name |
| 1 | 小红 |
| 2 | 小明 |
| 11 | 小丽 |
| 12 | 小蓝 |
set session transaction isolation level read committed;
事物1：select * from t_test where id=5 for update；
| 事物2：insert into t_test values(3,'小黄'); | √ |
| 事物2：insert into t_test values(5,'小黄'); | √ |
根据表中已有的数据，在观察两个事物的现象，可以推测，当select。。。for update;时，索引id=5的值不存在时，id的值为2,11之间没有任何锁（行锁或间隙锁都没有） 。
那么RC级别下使用索引进行范围查找时，加锁情况呢？
事物1：select * from t_test where id<5 lock in share mode；
| 事物2：insert into t_test values(2,'小明1'); | √ |
| 事物2：insert into t_test values(3,'小黑'); | √ |
| 事物2：update t_test set name='小明' where id=2; | ×阻塞等待 |
| 事物2：update t_test set name='小红1' where id=1; | ×阻塞等待 |
结论：索引id<5的时候，会为查询的结果添加共享行锁。
事物1：select * from t_test where id<5 for update；
| 事物2：select * from t_test where id<5 lock in share mode； | ×阻塞等待 |
| 事物2：insert into t_test values(3,'小黄'); | √ |
| 事物2：update t_test set name='小红1' where id=2; | ×阻塞等待 |
| 事物2：update t_test set name='小黑1' where id=1; | ×阻塞等待 |
结论：索引id<5的时候，会为查询的结果添加排他行锁。
上面我们分别对RR级别下，以唯一索引与普通索引查询数据库中不存在的值做了测试。
和RC级别下，以唯一索引为查询条件，查询数据库中不存在的值，或者范围查询时做了测试。
并且统计了一张表，需要时可以查看。
InnoDB存储引擎中不同SQL在不同隔离级别下锁比较
| 隔离级别 | Read Uncommited | Read Commited | Repeatable Read | Serializable | |
| 一致性读和锁 | |||||
| SQL | |||||
| SQL | 条件 | ||||
| select | 相等 | None locks | Consisten read/None lock | Consisten read/None lock | Share locks |
| 范围 | None locks | Consisten read/None lock | Consisten read/None lock | Share Next-Key | |
| update | 相等 | exclusive locks | exclusive locks | exclusive locks | Exclusive locks |
| 范围 | exclusive next-key | exclusive next-key | exclusive next-key | exclusive next-key | |
| Insert | N/A | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
| replace | 无键冲突 | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
| 键冲突 | exclusive next-key | exclusive next-key | exclusive next-key | exclusive next-key | |
| delete | 相等 | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
| 范围 | exclusive next-key | exclusive next-key | exclusive next-key | exclusive next-key | |
| Select ... from ... Lock in share mode | 相等 | Share locks | Share locks | Share locks | Share locks |
| 范围 | Share locks | Share locks | Share Next-Key | Share Next-Key | |
| 相等但是值不存在 | None locks | Share Next-Key | |||
| Select * from ... For update | 相等 | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
| 范围 | exclusive locks | Share locks | exclusive next-key | exclusive next-key | |
| 相等但是值不存在 | None locks | Share Next-Key | |||
| Insert into ... Select … （指源表锁） | innodb_locks_unsafe_for_binlog=off | Share Next-Key | Share Next-Key | Share Next-Key | Share Next-Key |
| innodb_locks_unsafe_for_binlog=on | None locks | Consisten read/None lock | Consisten read/None lock | Share Next-Key | |
| create table ... Select … （指源表锁） | innodb_locks_unsafe_for_binlog=off | Share Next-Key | Share Next-Key | Share Next-Key | Share Next-Key |
| innodb_locks_unsafe_for_binlog=on | None locks | Consisten read/None lock | Consisten read/None lock | Share Next-Key | |
从表中可以看出：对于许多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能 性也就越 高，从而对并发性事务处理性能的影响也就越大。因此，我们在应用中，应该尽量使用较低的隔离级别，以减少锁争用的机率。实际上，通过优化事务逻辑，大部分 应用使用Read Commited隔离级别就足够了。对于一些确实需要更高隔离级别的事务，可以通过在程序中执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE动态改变隔离级别的方式满足需求。
下面再提供一些mysql查看事物相关数据的一些操作，用于测试。
show processlist; 查看当前用户连接mysql的所有进程信息（信息中包括进程id，连接的数据库，当前操作，以及每个事物的状态等信息）。
kill pid;对于那种加锁后又断开的事物连接可以直接kill掉。（测试时遇到l这种问题，连接mysql服务器的跳板机由于长时间没有操作，而断开连接，导致之前开启事物中加的锁没有释放）。
1.查看当前会话隔离级别
select @@tx_isolation;
2.查看系统当前隔离级别
select @@global.tx_isolation;
扫一扫
9510
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
