MySQL锁机制

锁是数据库系统区别于文件系统的一个关键特性。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不例外。

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。根据加锁范围，MySQL里面的锁大致可以分成三类，全局锁，表级锁，行锁。MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）;BDB存储引擎采用的是页面锁（page-level locking）,但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking）,也支持表级锁，但默认情况下是采用行级锁。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

• 行级锁：开销大，加锁慢；会出现死锁；   锁粒度小，    发生所冲突的概率最低，并发度最高；     

1）全局锁

     全局锁就是对整个数据库实例加锁，MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock(FTWRL)。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（增删改）、数据定义语句（DDL）和更新类事务的提交语句。

全局锁的典型使用场景是做全库逻辑备份。也就是把整库每个表都select出来存成文本。【Mysql官方自带的逻辑备份工具是mysqldump。当MySQL存储引擎支持事务并且隔离级别是可重复读，这时当mysqldump使用参数--single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于innodb MVCC的支持，这个过程中数据是可以正常更新的。single-transaction方法只适用于所有的表使用事务引擎的库，如果有的表使用了不支持事务的引擎，那么备份就只能通过FTWRL方法】对于MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，破坏了备份的一致性。这时，需要使用FTWRL命令了。

2）表级锁

    表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的思索问题。锁定颗粒度大所带来的负面影响就是出现锁定资源争用的概率也会最高，致使并发大打折扣。

    MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock,MDL）。

    （一）表锁：表锁的语法是 lock tables ... read/write 。与FTWRL类似，可以用unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。表锁分为共享锁（读锁）和 排他锁 （写锁），读锁与读锁兼容，读锁与写锁、写锁与写锁之间互斥。举个例子，如果在某个线程A中执行lock tables t1 read, t2 write;这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。

     如何加表锁：MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行更新操作（update、delete、insert等）前，会自动给涉及的表加表锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显示加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁的原因。

eg1:  lock tables orders read,order_detail read ;

       select sum(total) from orders;

       select sum(subtotal) from order_detail;

      unlock tables;   

当使用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错！

 eg2: lock table actor read, actor as a read, actor as b read;

        select a.first_name, a.last_name,b.first_name,b.last_name from actor a, actor b  where a.first_name = b.first_name and a.first_name='Lisa'  and a.last_name = 'Tom' and a.last_name <> b.last_name;

    并发插入（Concurrent Inserts）  

   LOCK TABLES ... READ LOCAL, 加了“local”选项，MyISAM表支持查询和插入操作的并发执行。MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为，其值分别可以为0、1或2。当设置为0时，不允许并发插入。当设置为1时，如果MyISAM表中没有空洞（即表中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。当设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

    MyISAM的锁调度

    MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。 不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这时因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM的调度行为。

   i  通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。

   ii 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。

   iii通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

虽然上面3中方法都是要么更新优先、要么查询优先的方法，但还是可以用其来解决查询相对重要的应用中，读锁等待严重的问题。另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

使用表级锁定的主要是MyISAM、MEMORY、CSV等一些非事务性存储引擎。

（二）元数据锁（MDL）：MDL不需要显示使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

•    读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。

•    读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完成才能开始执行。

     MDL会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。事务中的MDL锁，在语句执行时开始申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

（三）表锁和MDL是怎么协同工作的？

        MDL和表锁是两个不同的结构。举例说明：要在MyISAM表上更新一行，那么会加MDL读锁和表的写锁；然后同时另外一个线程要更新这个表上另外一行，也要加MDL读锁和表的写锁。第二个线程的MDL读锁是能成功加上的，但是被表的写锁堵住了。从语句现象上看，就是第二个线程要等第一个线程执行完成。当多种锁同时存在时，必须得“全部不互斥”才能并行，只要有一个互斥，就得等。

3）行级锁

    行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有引擎都支持行锁，比如MyISAM引擎就不支持行锁，不支持行锁就意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这会影响到业务并发度。Innodb支持行级锁，这也是MyISAM被InnoDB替代的重要原因之一。

行锁就是针对数据表中行记录的锁。在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。此外，行级锁定也最容易发生死锁。使用行级锁定的主要是InnoDB存储引擎。

   行锁与MDL锁：DML(数据库管理语言)会产生读MDL锁，也就是update会持有MDL读锁。读和读不互斥，但对于行锁来说，两个update同时更新一条数据是互斥的。当多种锁同时存在时，必须得“全部不互斥”才能并行，只要有一个互斥，就得等。

    （一）InnoDB存储引擎实现了两种标准的行级锁：

•      共享锁（S Lock）:允许事务读一行数据。但不能修改，增加，删除数据。

•      排他锁（X Lock）：获准排他锁的事务既能读数据，又能修改数据。

    对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X）；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过语句显示给记录集加共享锁或排他锁。

     如果一个事务t1已经获得了行r的共享锁，那么另外的事务t2可以获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容。但若有其他的事务想获得行r的排他锁，则必须等待事务t1,t2释放行r的共享锁--这种情况称为锁不兼容。

    （二）InnoDB存储引擎支持多粒度锁定

      这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持了一种额外的锁方式，称为意向锁。意向锁为表级别的锁，innoDB支持两种意向锁：

•    意向共享锁（intention shared lock, lS）:事务有意向对标中的某些行加共享锁（S锁）

•    意向排他锁（intention exclusive lock, IX）:事务有意向对表中的某些行加排他锁（X锁）

     意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB会先获取该数据行所在数据表的对应意向锁。由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫意外的任何请求。

      表级意向锁与表锁的兼容性：

 

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

（三）意向锁的作用

    意向锁可以让表锁和行锁共存。

（四）一致性锁定读

       用户有时候需要显示的对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对与select的只读操作。InnoDB存储引擎对于select语句支持两种一致性的锁定读操作：

•        select  ... for update;     对读取的行记录加一个X锁，其他事务不能对已锁定的行加任何锁。

•        select  ... lock in share mode;   对读取的行记录加一个S锁，其他事务可以向被锁定的加S锁，但是如果加X锁，则会被阻塞。

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

     此外，select ... for update, select ... lock in share mode必须在一个事务中，当事务提交了，锁也就释放了。因此在使用上述两句select锁定语句时，务必加上BEGIN, START TRANSACTION 或者 SET AUTOCOMMIT = 0。

（五）一致性非锁定读

        在默认的隔离级别下（可重复读隔离级别），一致读是指InnoDB在多版本并发控制中（MVCC）在事务的首次读时产生一个镜像，在首次读时间点之前，其他事务提交的修改可以读取到，而首次读时间点之后，其他事务提交的修改或者是未提交的修改，都读取不到。唯一例外的情况，是在首次读时间点之前的本事务未提交的修改数据可以读取到。

   在读提交隔离级别下，一致读的每个读取操作都会有自己的镜像。一致读操作不会施加任何的锁，所以就不会阻止其他事务的修改动作。

比如最经典的 mysqldump --single-transaction 备份的时候就是把当前的事务隔离级别改变为可重复读并开启一个一致性事务的快照 , 就是一致性非锁定读。

   一致读在某些DDL语句下不生效：

•  碰到drop table语句时，由于InnoDB不能使用被drop的表，所以无法实现一致读。

• 碰到alter table语句时，也无法实现一致读。

• 当碰到insert into ... select, update ... select 和create table ...select语句时，在默认的事务隔离级别下，语句的执行更类似于在读取提交数据的隔离级别下。      

（六）行锁的几种算法

    （1） Record Lock（行锁）：单个行记录上的锁 

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

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

           mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;

            Query OK, 0 rows affected (0.15 sec)

            mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

            Query OK, 4 rows affected (0.00 sec)

            Records: 4  Duplicates: 0  Warnings: 0

表20-9   InnoDB存储引擎的表在不使用索引时使用表锁例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id = 1 ; +------+------+ | id   | name | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id = 2 ; +------+------+ | id   | name | +------+------+ | 2    | 2    | +------+------+ 1 row in set (0.00 sec)
mysql> select * from tab_no_index where id = 1 for update; +------+------+ | id   | name | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.00 sec)
	mysql> select * from tab_no_index where id = 2 for update; 等待

在如表20 -9所示的例子中，看起来session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行，如表20-10所示。

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

        mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;

        Query OK, 0 rows affected (0.15 sec)

        mysql> alter table tab_with_index add index id(id);

        Query OK, 4 rows affected (0.24 sec)

        Records: 4  Duplicates: 0  Warnings: 0

    表20-10   InnoDB存储引擎的表在使用索引时使用行锁例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_with_index where id = 1 ; +------+------+ | id   | name | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_with_index where id = 2 ; +------+------+ | id   | name | +------+------+ | 2    | 2    | +------+------+ 1 row in set (0.00 sec)
mysql> select * from tab_with_index where id = 1 for update; +------+------+ | id   | name | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.00 sec)
	mysql> select * from tab_with_index where id = 2 for update; +------+------+ | id   | name | +------+------+ | 2    | 2    | +------+------+ 1 row in set (0.00 sec)

       ii）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。在如表20-11所示的例子中，表tab_with_index的id字段有索引，name字段没有索引：

mysql> alter table tab_with_index drop index name;

Query OK, 4 rows affected (0.22 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into tab_with_index  values(1,'4');

Query OK, 1 row affected (0.00 sec)

mysql> select * from tab_with_index where id = 1;

+------+------+

| id   | name |

+------+------+

| 1    | 1    |

| 1    | 4    |

+------+------+

2 rows in set (0.00 sec)

表20-11 InnoDB存储引擎使用相同索引键的阻塞例子       

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 and name = '1' for update; +------+------+ | id   | name | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.00 sec)
	虽然session_2访问的是和session_1不同的记录，但是因为使用了相同的索引，所以需要等待锁： mysql> select * from tab_with_index where id = 1 and name = '4' for update; 等待

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

在如表20-12所示的例子中，表tab_with_index的id字段有主键索引，name字段有普通索引：

mysql> alter table tab_with_index add index name(name);

Query OK, 5 rows affected (0.23 sec)

Records: 5  Duplicates: 0  Warnings: 0

表20-12  InnoDB存储引擎的表使用不同索引的阻塞例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 for update; +------+------+ | id   | name | +------+------+ | 1    | 1    | | 1    | 4    | +------+------+ 2 rows in set (0.00 sec)
	Session_2使用name的索引访问记录，因为记录没有被索引，所以可以获得锁： mysql> select * from tab_with_index where name = '2' for update; +------+------+ | id   | name | +------+------+ | 2    | 2    | +------+------+ 1 row in set (0.00 sec)
	由于访问的记录已经被session_1锁定，所以等待获得锁。： mysql> select * from tab_with_index where name = '4' for update;

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

在下面的例子中，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。

例子中tab_with_index表的name字段有索引，但是name字段是varchar类型的，如果where条件中不是和varchar类型进行比较，则会对name进行类型转换，而执行的全表扫描。

mysql> alter table tab_no_index add index name(name);

Query OK, 4 rows affected (8.06 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select * from tab_with_index where name = 1 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: tab_with_index

         type: ALL

possible_keys: name

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 4

        Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from tab_with_index where name = '1' \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: tab_with_index

         type: ref

possible_keys: name

          key: name

      key_len: 23

          ref: const

         rows: 1

        Extra: Using where

1 row in set (0.00 sec)

 

（2）Gap Lock :  间隙锁，锁定一个范围，但不包含记录本身。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁。间隙锁是施加在索引记录之间的间隙上的锁，锁定一个范围的记录、但不包括记录本身，比如SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE语句，尽管有可能对c1字段来说当前表里没有=15的值，但还是会阻止=15的数据的插入操做，是因为间隙锁已经把索引查询范围内的间隙数据也都锁住了，间隙锁的使用只在部分事务隔离级别（可重复读级别）才是生效的。

间隙锁只会阻止其他食物的插入操作，就是只有insert操作会等待GAP锁，update操作不会等待GAP锁。

    使用gap lock的前置条件：

• 事务隔离级别为REPEATABLE-READ, innodb_locks_unsafe_for_binlog参数为0，且sql走的索引为非唯一索引（无论是等值检索还是范围检索）

• 事务隔离级别为REPEATABLE-READ, innodb_locks_unsafe_for_binlog参数为0，且sql是一个范围的当前读操作，这时即使不是非唯一索引也会加gap lock。

Gap Lock的作用是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生。

可以通过两种方式来关闭Gap Lock:

• 将事务的隔离级别设置为READ COMMITTED。

• 将参数innodb_locks_unsafe_for_binlog设置为1。

（3）Next-key Lock: Gap Lock + Record Lock, 锁定一个范围，并且锁定记录本身。

      在默认情况下，MySQL的事务隔离级别是可重复读，并且innodb locks unsafe for binlog参数为0，这时默认采用next-key locks。

     所谓Next-key Locks,就是记录锁和间隙锁的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。当扫描表的索引时，InnoDB以这种形式实现行级的锁：遇到匹配的索引记录，在上面加上对应的S锁或X锁。因此，行级锁实际上是索引记录锁。如果一个事务拥有索引上记录r的一个S锁或X锁，另外的事务无法立即在r记录索引顺序之前的间隙插入一条新的纪录。

     默认情况下，InnoDB启用RR事务隔离级别，此时，InnoDB在查找和扫描索引时会使用Next-key锁，其设计的目的是为了解决【幻读】的出现。

    当查询的索引含有唯一（主键索引和唯一索引）属性时，InnoDB存储引擎会对Next-key Lock进行优化，将其降低为Record Lock,即仅锁住索引本身，而不是范围。

（七） 恢复和复制的需要，对InnoDB锁机制的影响

 

MySQL通过BINLOG记录执行成功的Insert、update、delete等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制。MySQL的恢复机制（复制其实就是Slave Mysql不断做基于BINLOG的恢复）有以下特点。

I    一是MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。这与Oracle数据库不同，Oracle是基于数据库文件块的。

II   二是MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。这点也与Oracle不同，Oracle是按照系统更新好（System Change Number, SCN）来恢复数据的，每个事务开始时，Oracle都会分配一个全局唯一的SCN，SCN的顺序与事务开始的时间顺序是一致的。

从上面两点可知，MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能查无满足其多顶条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92 “可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，Inn哦DB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited 或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的。

执行一个普通的SELECT语句，用一致性读就可以了。Innodb实现了多版本数据，对普通的SELECT一致性读，不需要加任何锁。MySQL对于处理“Insert into target_tab select source_tab where ...”和“create table new_tab ...select ... from source_tab where”时要给source_tab加锁，而不是使用对并发影响最小的多版本数据来实现一致性读。还要特别说明的是，如果上述语句的SELECT是范围条件，Inn哦代表还会给源表加间隙锁（Next-Lock）。

因此，Insert... select ... 和 create table ...select... 语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中应尽量避免使用。实际上，MySQL将这种SQL叫做不确定的SQL，不推荐使用。

（八）InnoDB 在不同隔离级别下的一致性读及锁的差异

表20-16                                          InnoDB存储引擎中不同SQL在不同隔离级别下锁比较

隔离级别         一致性读和锁 SQL		Read Uncommited	Read Commited	Repeatable Read	Serializable
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
Select * from ... For update	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive locks	Share locks	exclusive next-key	exclusive 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

（九）InnoDB什么时候使用表锁

     对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

   i）第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。

  ii）第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

  当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。

  在InnoDB下，使用表锁要注意以下两点：

  （1）使用LOCK TABLES 虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，二是由其上一层--MySQL SERVER负责的，仅当autocommit=0、innodb_table_locks=1(默认设置)时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，Inodb才能自动识别涉及表级锁的死锁；否则，Innodb将无法自动检测并处理这种死锁。

（2）在用LOCK TABLES 对Innodb表加锁时要注意，要将autocommit设为0，否则MySQL不会给表加锁；事务结束前，不要用unlock tables释放表锁，因为unlock tables会隐含地提交事务；commit或rollback并不能释放用lock tables加的表级锁，必须用unlock tables释放表锁。

（十）关于死锁

        MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。

   死锁：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态。发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innode_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

    查看死锁的方法有两种：

• 通过 show engine innodb status 命令可以查看最后一个死锁的情况。

• 通过 innodb_print_all_deadlocks 参数配置可以将所有死锁的信息都打印到 MySQL 的错误日志中。

    避免死锁的常用方法：

       （1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。

       （2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

       （3）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

       （4）在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用select ... for update加排它锁，在没有符合条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新纪录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITED,就可避免问题。

      （5）当隔离级别为READ COMMITTED时，如果两个线程都先执行select ... for update, 判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。

（十一）锁问题

       通过锁机制可以实现事务的隔离性要求，使得事务可以并发地工作。锁提高了并发，但是也有潜在的问题，不过好在因为事务隔离性的要求，锁只会带来三种问题，如果可以防止这三种情况的发生，那将不会产生并发异常。

  1）脏读

      先了解脏数据、脏页、脏读。

      脏页：指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页数据是不一致的，当然在刷新到磁盘之前，日志都已经被写入到了重做日志文件中。对于脏页的读取，是非常正常的。脏页是因为数据库实例内存和磁盘的异步造成的，这并不影响数据的一致性（或者说两者最终会达到一致性，即当脏页都刷到磁盘）。并且因为脏页的刷新是异步的，不影响数据库的可用性，因此可以带来性能的提高。

     脏数据：是指事物对缓冲池中行记录的修改，并且还没有被提交。

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

  2）不可重复读

      不可重复读是在一个事务内读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问同一数据集合，并做了一些DML操作。因此在第一个事务中的两次读取数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况，这种情况称之为不可重复读。

     不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到确实已经提交的数据。

3）丢失更新

    当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新的问题，最后的更新覆盖了由其他事务所做的更新。要避免丢失更新发生，需要让事务在这种情况下的操作变成串行化，而不是并行的操作。

     （十二）锁阻塞

       因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，其实为了确保事务可以并发正常的运行。在Innodb存储引擎中，参数innodb_lock_wait_timeout用来控制等待的时间（默认是50秒），innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是off,不回滚）。参数innodb_lock_wait_timeout可以在MySQL数据库运行时进行调整：在默认情况下innodb存储引擎不会回滚超时引发的错误异常。其实Innodb存储引擎在大部分情况下都不会对异常进行回滚。