COPY MySQL数据库锁机制

所谓锁，为保证数据的一致性，对共享资源的在被并发访问变得有序的一种规则。

不同的MySQL存储引擎，有不同的锁机制或锁实现；总的来所，使用了三种锁级别，行级锁（row-level）、页级锁（page-level）、表级锁（table-level），依次锁定的资源粒度逐渐减小，锁资源是随着锁定资源粒度的减小，锁定同样数据需要的内存数量越来越多，算法也越来越负责，但同时应用程序遇到锁等待的可能也越来越底，系统的整体并发行随之提高；

表级锁，各大存储引擎粒度最大的锁级别，实现简单，获取锁和释放锁的速度快，也避免了死锁的问题，但同时带来了锁资源竞争的问题，导致并发度较底；表级锁分为读锁和写锁，MySQL通过四个队列来维护这两种锁定，两个存放当前正被锁定中的读和写的信息，两个存放等待读和写的信息，这四个队列分别为：read->lock\read_wait->lock->write->lock/write_wait->lock；读锁定，当前请求获取锁定的资源没有被写锁定，也没有在写锁定等待队列中有优先级更高写锁等待，立即进入read->lock，如果不满足，进入read_wait->lock；写锁定，当前请求写的资源没有被写锁定，并且没有在写锁定等待队列里面，那么再检测是否在读等待队列里面，如果有，进入写等待队列，如果没有，进入当前写队列；使用表级锁的通常是一些非事务性的存储引擎，如MyISAM、Memory、CSV等

页级锁，MySQL中毕竟特殊的一种锁级别，粒度介于行级锁和表级锁之间，获取和释放锁资源的负担也介于行级锁和表级锁之间，并发性同样也介于行级锁和表级锁之间，和行级锁一样，页级锁也可能发生死锁；主要是BerkeleyDB存储引擎是锁定方式；

行级锁，是RMDB实现的锁定粒度最小的锁，发生资源竞争的概率最小，能够给提供尽可能大的并发处理从而提供应用的性能。但同时由于颗粒度小，导致获取和释放锁需要的消耗也最大。另外，行级锁最容易发生死锁；行级锁定不是由MySQL实现的，而是由存储引擎实现的，如InnoDB和MySQL的分布式存储引擎NDBCluster等；InnoDB的行级锁同样分为两种，共享锁和排他锁，同样InnoDB也引入了意向锁（表级锁）的概念，所以也就有了意向共享锁和意向排他锁，所以InnoDB实际上有四种锁，即共享锁（S）、排他锁（X）、意向共享锁（IS）、意向排他锁（IX）；

如果某些资源已经有了一个共享锁，那么在这些资源上面可以添加其他的共享锁，但不能添加排他锁；如果某些资源已经有了一个排他锁，那么在这些资源上不能添加其他的排他锁和共享锁，只能等待当前锁的释放，并获取锁资源后，才能对其加锁，但可以对其添加意向锁，即如果等待事务想要添加的是排他锁，那么可以在锁定行的所在表添加意向排他锁，如果等待事务想要添加的是共享锁，那么可以在锁定行所在表添加意向共享锁；InnoDB的锁实现与Oracle的锁实现有很大的不同，总的来说，Oracle锁定数据是根据某行记录所在的物理block上的事务槽上表级锁定信息，而InnoDB的锁定则是通过指向数据记录的第一条索引之前和最后一条索引之后的空域空间上标记锁信息来实现的，所以InnoDB的这种锁实现有被称为“Next Key Locking”（间隙锁），间隙锁一个比较大的弱点是，当锁定一定范围的键值后，即使一些不存在的键值也回被无辜的锁定，导致这种键值的记录不能被insert。当然，这种情况只会出现在InnoDB的默认的事务隔离级别repeatable-read才会出现，如果降低InnoDB的事务隔离级别为read commited则不会出现这样的情况。InnoDB给出的解释是间隙锁可以阻止幻读的出现，但其实间隙锁只能阻止部分幻读的情况，但不能阻止全部。通过索引来实现锁的方式还有一个更大的隐患是，当Query不能使用索引时，行级锁将会上升为表级锁，会将整张数据表锁住，造成并发性能的降低。

死锁，行级锁可能产生死锁，InnoDB也不例外。InnoDB有一套检测死锁的机制，但前提是死锁的场景涉及的存储引擎都是InnoDB的时候。如果InnoDB检测到死锁的存在，那么就将影响数据行数最小的个事务回滚。

那么有什么办法来避免InnoDB的间隙锁带来的麻烦吗？有三种办法：1、降低并发，避免出现资源竞争，但这样会在一定程度上降低应用的性能；2、修改InnoDB的默认的事务隔离级别，由repeatable-read修改为read commited，当然修改事务隔离级别带来的另外一个隐患就是可能会出现不可重复读；3、在查询数据是，一定要使用索引，避免全表扫描，在insert数据时，使用增长的索引字段（即每次插入的索引字段的值一定保证是增长的）。

首先建立一张测试使用的表：
 
CREATE TABLE`test_innodb_lock` (
 
  `a` int(11) DEFAULT NULL,
 
  `b` varchar(16) DEFAULT NULL,
 
  KEY `test_innodb_lock_a_IDX` (`a`)
 
) ENGINE=InnoDB
 
        然后再往这张表里插入一些数据，以备使用，最终表数据如下：
 
+------+------+
 
| a  | b    |
 
+------+------+
 
|  1 | a    |
 
|  1 | x    |
 
|  1 | y    |
 
|  2 | b    |
 
|  2 | w    |
 
|  2 | z    |
 
|  3 | c    |
 
|  4 | d    |
 
|  5 | e    |
 
|  8 | ff  |
 
|  8 | f    |
 
|  10 | g    |
 
+------+------+
 
 
 
首先我们来看看行级锁的情况：
 
实验一：
 
打开两个MySQL客户端，
 
在客户端1执行:
 
mysql> set autocommit = 0;
 
Query OK, 0 rows affected (0.00 sec)
 
修改客户端1的事务提交方式为手动提交；
 
 
 
在客户端2执行：
 
mysql> set autocommit = 0;
 
Query OK, 0 rows affected (0.00 sec)
 
同样修改客户端2的事务提交方式为手动提交；
 
 
 
在客户端1执行：
 
mysql> update test_innodb_lock set b ='xxx' where  a = 1 and b = 'y';
 
Query OK, 1 row affected (0.00 sec)
 
Rows matched: 1  Changed: 1 Warnings: 0
 
同时使用索引字段a和非索引字段b更新一条数据；
 
 
 
在客户端2执行：
 
mysql> update test_innodb_lock set b ='xxx' where a=1 and b = 'x';
 
同时使用索引字段a（并且索引值同客户端1的值相同）和非索引字段更新另外一条数据；
 
结果发现客户端2的update语句被阻塞，需要客户端1提交或回滚才能继续执行。说明，虽然两个事务最终更新的数据不是同一条数据，但然后可能被锁定，这是因为两条SQL语句都使用了相同的索引值（a=1），行级锁上升为页级锁。
 
实验二：
 
在客户端1执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验一的操作；
 
 
 
在客户端2执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验一的操作；
 
 
 
在客户端1执行：
 
mysql> update test_innodb_lock set b ='xxx' where  a = 1 and b = 'a';
 
Query OK, 1 row affected (0.00 sec)
 
Rows matched: 1  Changed: 1 Warnings: 0
 
同时使用索引字段a和非索引字段b更新一条数据；
 
 
 
在客户端2执行：
 
mysql> update test_innodb_lock set b ='xxx' where a=2 and b = 'b';
 
Query OK, 1 row affected (0.00 sec)
 
Rows matched: 1  Changed: 1 Warnings: 0
 
同时使用索引字段a（索引值不同于客户端1SQL语句的索引值）和非索引字段b更新一条数据；
 
更新顺利进行，执行并没有被阻塞；
 
说明，同是根据索引和非索引字段进行更新数据，当两个事务的SQL语句中的索引条件值不一样时，更新仍然能够顺利进行。

实验三：
 
在客户端1执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验一的操作；
 
 
 
在客户端2执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验一的操作；
 
 
 
在客户端1执行：
 
mysql> update test_innodb_lock set b ='xxx' where b = 'd';
 
Query OK, 1 row affected (0.00 sec)
 
Rows matched: 1  Changed: 1 Warnings: 0
 
通过非索引字段更新唯一的一条数据记录，
 
 
 
在客户端2执行：
 
mysql> update test_innodb_lock set b='xxx' where b ='e';
 
通过非索引字段更新另外一条唯一的一条数据记录，update语句被阻塞；
 
说明，一个事务根据非索引字段更新数据时，InnoDB会将整个表给锁住，行级锁此时上升为表级锁。
 
 
 
实验四：
 
在客户端1执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验三的操作；
 
 
 
在客户端2执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验三的操作；
 
 
 
在客户端1执行：
 
mysql> update test_innodb_lock set b ='xxx' where a=4;
 
Query OK, 1 row affected (0.00 sec)
 
Rows matched: 1  Changed: 1 Warnings: 0
 
只使用索引更新数据记录
 
 
 
在客户端2执行：
 
mysql> update test_innodb_lock set b ='xxx' where a=4;
 
Query OK, 1 row affected (0.00 sec)
 
Rows matched: 1  Changed: 1 Warnings: 0
 
只使用索引更新数据记录，同时索引值与客户端1的索引值相同（a=4），此时，客户端2的update语句被阻塞。
 
说明，这个现象的行级锁，于我们理解的行级锁一致，即真正只是锁定了一条记录。
 
 
 
实验五：
 
在客户端1执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验四的操作；
 
 
 
在客户端2执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验四的操作；
 
 
 
在客户端1执行：
 
mysql> update test_innodb_lock set b ='xxx' where a=4;
 
Query OK, 1 row affected (0.00 sec)
 
Rows matched: 1  Changed: 1 Warnings: 0
 
只使用索引更新数据记录
 
 
 
在客户端2执行：
 
mysql> update test_innodb_lock set b ='xxx' where b=’g’;
 
只使用非索引字段更新数据记录，客户端2的update语句被阻塞，这是因为客户端2的update语句由于没有使用索引，需要在数据表上加意向排他锁，但在a=4这条记录上，已经存在排他锁了，索引客户端2的update语句只能被阻塞。

以上实验说明：
 
1、      InnoDB的行级锁在有些情况下是会自动上升为页级锁和表级锁的，此时数据库的写性能会急剧下降，并可能出现大量的死锁（关于死锁的情况，很容易模仿出来，这里不在举例）；
 
2、      真正的行级锁，只发生在所有的事务都是通过索引来进行检索数据的。
 
 
 
下面我们继续实验与间隙锁相关的情况：
 
实验六：
 
在客户端1执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验五的操作；
 
 
 
在客户端2执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验五的操作；
 
 
 
在客户端1执行：
 
mysql> update test_innodb_lock set b ='xxx' where a=8;
 
Query OK, 2 rows affected (0.00 sec)
 
Rows matched: 2  Changed: 2 Warnings: 0
 
通过索引更新数据记录，索引值为8；
 
 
 
在客户端2执行：
 
mysql> insert into test_innodb_lock(a,b)values(8,'xxx');
 
向数据表中插入一条数据，插入数据的索引列的值与客户端1的SQL语句的索引值相同，都为8，此时，insert语句被阻塞。
 
 
 
在客户端1执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> update test_innodb_lock set b ='xxx' where a=8;
 
Query OK, 2 rows affected (0.00 sec)
 
Rows matched: 2  Changed: 2 Warnings: 0
 
通过索引更新数据记录，索引值为8；
 
 
 
在客户端2执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into test_innodb_lock(a,b)values(5,'xxx');
 
向数据表中插入一条数据，插入数据的索引列的值小于客户端1的SQL语句的索引值，但大于或等于已有数据记录中最大小于检索索引（a=8）的索引值5，此时，insert语句被阻塞。
 
 
 
在客户端1执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> update test_innodb_lock set b ='xxx' where a=8;
 
Query OK, 2 rows affected (0.00 sec)
 
Rows matched: 2  Changed: 2 Warnings: 0
 
通过索引更新数据记录，索引值为8；
 
 
 
在客户端2执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into test_innodb_lock(a,b)values(9,'xxx');
 
向数据表中插入一条数据，插入数据的索引列的值大于客户端1的SQL语句的索引值，但小于已有数据记录中最小大于检索索引（a=8）的索引值10，此时，insert语句被阻塞。
 
 
 
在客户端1执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> update test_innodb_lock set b ='xxx' where a=8;
 
Query OK, 2 rows affected (0.00 sec)
 
Rows matched: 2  Changed: 2 Warnings: 0
 
通过索引更新数据记录，索引值为8；
 
 
 
在客户端2执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into test_innodb_lock(a,b)values(10,'xxx');
 
 
 
向数据表中插入一条数据，插入数据的索引列的值大于客户端1的SQL语句的索引值，且大于或等于已有数据记录中最小大于检索索引（a=8）的索引值10，此时，insert语句顺利执行。
 
 
 
以上系列的动作说明，当一个事务在通过索引更新数据时，它会将该索引的前后紧紧相邻的索引记录锁住，包括那些根本就不存在的索引值，锁定的区间为左闭右开区间，即[x,y），其中x为小于事务中SQL语句索引值的最大值，y为大于事务中SQL语句索引值的最小值，在本例中，事务中SQL语句索引值为8，索引其锁定的区间为[5,10），所以另外一个事务在做insert操作时，索引值大于或等于5且小于10的索引记录都将被阻塞。需要注意的是，当更新事务的索引值为已有记录中最大值时，这时所有大于该索引值的记录，其他事务的insert操作都将被阻塞。这就是InnoDB间隙锁的具体表现。所以说，InnoDB的间隙锁避免了部分幻读，但不是全部，因为它锁定的是一个区间，而不是整张表。

实验七：
 
在客户端1执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验六的操作
 
 
 
在客户端2执行：
 
mysql> rollback;
 
Query OK, 0 rows affected (0.00 sec)
 
回滚实验六的操作
 
 
 
在客户端1执行：
 
mysql> update test_innodb_lock set b ='xxx' where b=’a’;
 
Query OK, 2 rows affected (0.00 sec)
 
Rows matched: 2  Changed: 2 Warnings: 0
 
通过非索引字段更新一条记录；
 
 
 
在客户端2执行：
 
mysql> insert into test_innodb_lock(a,b)values(10,'xxx');
 
插入一条完全不相关的数据，该insert语句被阻塞；
 
说明，当事务1通过非索引字段更新一条数据是，整张表就会被锁住，即使是insert操作，也将被阻塞。
 
 
 
以上实验说明：
 
1、      InnoDB的间隙锁是可以避免数据出现幻读，但只是避免部分出现幻读，当一个事务是通过索引来更新数据是，另外一个事务在前一个事务索引值前后的左闭右开区间是不能并行插入数据的，必须等待上一个事务提交或回滚；
 
2、      当前一个事务不是通过索引字段来进行更新操作时，那么InnoDB的这种间隙锁就能够完全避免幻读的出现，因为它会将整个表锁住，在当前事务提交或回滚之前，阻塞所以insert操作。
 
 
 
说明：
 
1、        以上实验的所以update操作，更换为delete操作，效果完全一样；
 
2、        如果修改InnoDB的默认事务隔离级别，由可重复读修改为读已提交，那么以上现象均不会出现，所以这样的锁机制只在可重复读这一事务隔离级别出现，或者说这是InnoDB可重复读事务隔离级别的一种实现方式。