mysql中innodb引擎的锁问题 - Go语言中文社区

mysql中innodb引擎的锁问题

   一，概述

    相对其他数据库而言，mysql的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如：myisam和memory存储引擎采用的是表级锁，bdb采用的是页面锁，但也支持表级锁，innodb存储引擎即支持行级锁也支持表级锁，但默认情况下是行级锁。

  三种锁的特性大致归纳如下：

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

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

     （3）页面锁： 开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

   所以，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用。而行级锁则更适合于有大量按照索引条件并发更新少量不同数据，同时又有并发查询的应用。


关于myisam锁问题可以查看：http://blog.csdn.net/fuzhongyu2/article/details/52794759



二，innodb锁问题


     innodb与myisam的最大不同点：一是，支持事务；二是，采用了行级锁


1，背景知识

   （1）事务(transaction)及其acid属性

         a， 原子性：事务是一组sql语句组成的逻辑处理单元，事务具有以y改，要么全都执行，要么都不执行

         b，一致性：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如b树索引或双向链表）也必须都是正确的

       c， 隔离性：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的‘独立’环境执行，这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然

    d，持久性：事务完成之后，他对于数据的修改是永久性的，即使出现系统故障也能够保持。

 

    （2）并发事务处理带来的问题

    相对于串行处理来说，并发事务处理能力大大增加数据库资源的利用率，提高数据库系统事务吞吐量，从而支持更多的用户。但也带来了一些问题：

       a，更新丢失：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题

      b， 脏读：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来同时读取一条记录，如果不加控制，第二个事务读取了这些'脏'数据，并做进一步处理，就会产生未提交数据的依赖关系。

     c，不可重复读：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变或某些记录已经被删除了。

     d，幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据。


     （3）事务隔离级别

  上面讲到的并发事务处理带来的问题中，‘更新丢失’通常是应该完全避免的，但是防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决问题。“脏读”，“不可重复读”，“幻读”，其实都是数据库读一致性问题，必须有数据库提供一定的事务隔离级别来解决：

      a， 一种是，在读取之前对其加锁，阻止其他事务对数据进行修改

     b，另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供同一数据的多个版本。

 

4种隔离级别比较
各类级别副作用 读数据一致性 脏读 不可重复读 幻读
未提交读 最低级别，只能保证不读取物理上损坏的数据 是 是 是
已提交读 语句级 否 是 是
可重复读 事务级 否 否 是
可序列化 最高级别，事务级 否 否 否



2，获取innodb行锁争用情况


可以通过检查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     |
+-------------------------------+-------+
5 rows in set (0.01 sec)

 如果发现innodb_row_waits和innodb_row_lock_time_avg的值较高，可以通过查询infomation_schema数据库中相关表来查看锁情况 

mysql> use information_schema;

mysql> select * from innodb_locks G;



3，innodb的行锁模式及加锁方法

   innodb实现了以下两种类型的行锁

     （1）共享锁（s）：允许一个事务读一行，阻止其他事务获得相同数据集的排它锁。又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但也能修改A，其他事务T'只读A不能修改A ，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

     （2）排它锁（x）：允许获得排它锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

另外，为了允许行锁和表锁的共存，实现多粒度锁机制，innodb还有两种内部使用的意向锁（imtention locks），这两种意向锁都是表锁

       (1) 意向共享锁（is）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的is锁

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


innodb行锁模式兼容性列表
当前锁模式是否兼容请求锁模式 x ix s is
x 冲突 冲突 冲突 冲突
ix 冲突 兼容 冲突 兼容
s 冲突 冲突 兼容 兼容
is 冲突 兼容 兼容 兼容

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


注：意向锁是innodb自动加的，不需要用户干预。对于update,delete,insert语句，innodb会自动给涉及数据集加排他锁；对于普通select语句，innodb不会加任何锁。

  

   可以通过一下语句显示加的共享锁或者排他锁：

     （1）共享锁：select * from table_name where .... lock in share mode;

      （2）排它锁：select * from table_name where ... for update;


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


   好，下面我们来看一个例子：

    表格式：       

mysql> create table t19(id int auto_increment primary key,first_name varchar(63),last_name varchar(63),age int(3));
Query OK, 0 rows affected (0.37 sec)

mysql> insert into t19(first_name,last_name,age)values('f1','zy1',1),('f2','zy2',2),('f3','zy3',3);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t19;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f1         | zy1       |    1 |
|  2 | f2         | zy2       |    2 |
|  3 | f3         | zy3       |    3 |
+----+------------+-----------+------+
3 rows in set (0.00 sec)

  

innodb存储引擎的共享锁例子
session_1 session_2 
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select id,first_name,last_name,age from t19 where id=1;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f1         | zy1       |    1 |
+----+------------+-----------+------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select id,first_name,last_name,age from t19 where id=1;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f1         | zy1       |    1 |
+----+------------+-----------+------+
1 row in set (0.00 sec)
当前session对actor_id=178的记录加share mode的共享锁：
mysql> select id,first_name,last_name,age from t19 where id=1 lock in share mode;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f1         | zy1       |    1 |
+----+------------+-----------+------+
1 row in set (0.00 sec)
 
  其他session仍然可以查询记录，并也可以对该记录加share mode共享锁：
mysql> select id,first_name,last_name,age from t19 where id=1 lock in share mode;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f1         | zy1       |    1 |
+----+------------+-----------+------+
1 row in set (0.00 sec)
当前sesssion对锁定的记录进行更新操作，等待锁；
mysql> update t19 set age=2;
等待
##超时会报如下错误
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
注：这边不能更新，是因为session_2中加了共享锁，如果别的会话对这条事务没有加共享锁，是能更新的。		  
  其他session也对该记录进行更新操作，则会导致死锁退出
mysql> update t19 set age=222 where id=2;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
获得锁后，可以成功更新
Query OK, 0 rows affected (20.06 sec)
Rows matched: 1  Changed: 0  Warnings: 0
 

  当使用select ... from update加锁后再更新记录会出现 下面的情况

 

innodb存储引擎的排他锁例子
session_1 session_2 
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select id,first_name,last_name,age from t19 where id=1;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f11        | zy1       |    2 |
+----+------------+-----------+------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select id,first_name,last_name,age from t19 where id=1;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f11        | zy1       |    2 |
+----+------------+-----------+------+
1 row in set (0.00 sec)
当前session对id=1的记录加for update的排它锁
mysql> select id,first_name,last_name,age from t19 where id=1 for update;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f11        | zy1       |    2 |
+----+------------+-----------+------+
1 row in set (0.00 sec)
 
  其他session可以查询记录，但不能对该记录加共享锁和排它锁，会等待获得锁
mysql> select id,first_name,last_name,age from t19 where id=1;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f11        | zy1       |    2 |
+----+------------+-----------+------+
1 row in set (0.00 sec)

mysql> select id,first_name,last_name,age from t19 where id=1 for update;
等待
更新提交：
mysql> update t19 set age=1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.04 sec)
 
  其他session获得锁，得到记录：
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f11        | zy1       |    1 |
+----+------------+-----------+------+
1 row in set (26.07 sec)

     4，innodb行锁实现方式

      innodb行锁是通过索引上的索引项加锁来实现的，如果没有索引，innodb将通过隐藏的聚簇索引来对记录加锁。innodb行锁分为3中情形。

     1，record lock ：对索引项加锁

   2，gap lock：对索引项之间的间隙，第一条记录前的间隙或最后一条记录后的间隙加锁

   3，next-key lock ：前两种的组合，对记录及其前面的间隙加锁。

      innodn这种行锁实现特点意味着：如果不通过索引条件检索数据，那么innodb将对表中的所有记录加锁，实际效果跟表锁一样。

   （1）在实际应用中，特别注意innodb行锁这一特性，否则可能导致大量的锁冲突，从而影响并发性能：

  

innodb存储引擎的表在不使用索引时对全部记录加锁
session_1 session_2 
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select id,first_name,last_name,age from t19 where age=1 for update;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f11        | zy1       |    1 |
+----+------------+-----------+------+
1 row in set (0.00 sec)
 
  
mysql> select id,first_name,last_name,age from t19 where age=2 for update;
等待
##超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

     从上面的例子中，我们看到，只给age=1加了排它锁，但session_2请求age=2时却也需要等待获得锁。原因是，在没有索引的情况下，innodb会对所有记录都加锁。

  注：因为mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现冲突的，设计的时候要注意这一点。


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

       （3）即便在条件中使用了索引字段，但是否使用索引来检索数据是由mysql通过判断不同执行计划的代价来决定的，如果mysql认为全表扫描的效率更高，比如对很小的表，他就不会使用索引，这种情况下innodb也会对所有记录加锁。因此，在分析锁冲突的时候，别忘了检查sql的执行计划，以确认是否使用了索引。


         5，next-key锁

 

     概念： 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，innodb会给符合条件的已有记录的索引项加锁；对于键值在条件范围内但并不存在的记录叫做“间隙（GAP）”,innodb也会对这个“间隙”加锁，这种锁机制就是所谓的next-key锁。

      举个例子：假如t19表中只有3条记录，其id分别为1,2,3

mysql> select * from t19 where id>2 for update;

  是一个范围条件的检索，innodb不仅会对符合条件的id值为3的记录加锁，也会对id大于3（这些记录并不存在）的“间隙”加锁。

   作用：（1）为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了id大于3的任何记录，那么本事务再次执行上述语句，就会发生幻读。

                （2）为了满足其恢复和复制的需要，有关其恢复和复制对锁机制的影响，以及不同隔离级别下innodb使用next-key锁的情况。

    缺点：使用范围条件检索并锁定记录时，innodb这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此在实际开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

      注：如果使用相等条件请求一个不存在的记录，innodb也会加next-key锁。

     

  

next-key锁阻塞例子
session_1 session_2
创建事务：
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
对不存在的记录加for updatede的锁
mysql> select * from t19 where id=4 for update;
Empty set (0.00 sec)
 
  这时插入数据也会出现锁等待
mysql> insert into t19(id,first_name,last_name,age)values(10,'f10','zy10',10);
执行rollback:
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
 
  获得锁插入记录
Query OK, 1 row affected (38.19 sec)

    


     6，恢复和复制的需要，对innodb锁机制的影响


        mysql 通过biglog记录执行成功的insert ,update ,delete等更新数据的sql语句，并由此实现mysql数据库的恢复和主从复制。

        目前mysql支持的三种日志格式：（1）基于语句的日志格式sbl  （2）基于行的日志格式rbl    （3）混合格式。

       支持4种复制模式：（1）基于sql语句的复制sbr：也是mysql最早支持的复制模式

                                          （2）基于行数据的复制rbr：这是mysql5.1以后开始支持的复制模式，对于非安全的sql语句采用居于行的复制模式。

                                           （3）混合复制模式：对于安全的sql语句采用基于sql语句的复制模式，对于非安全的sql语句采用居于行的复制模式。

                                           （4）使用全局事务ID（GTIDs）的复制：主要是解决主从自动同步一致问题。

     对基于语句日志格式(SBL)的恢复和复制而言，由于mysql的BIGLOG是按照事务提交的先后顺序记录的，因为要正确恢复或复制数据，就必须满足：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。这已经超过了iso/ansi sql92 "可重复读“隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，innodb要用到next-key锁的原因。

    对于"insert into t20 select id,age from t19 where ..." (CTAS)这种sql语句，用户并没有对t19做任何更新操作，但mysql对这种sql做了特别处理。

  

CTAS操作给原表加锁的例子
session_1 session_2
创建表t20:
mysql> create table t20(id int(11),age int(3));
Query OK, 0 rows affected (0.28 sec)

开始事务
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
  
mysql> insert into t20 select id,age from t19 where id=1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
  
#这边操作的数据和session_1中插入的数据有关系（才会锁定）
mysql> update t19 set age=1 where id=1;
等待

mysql> commit;
Query OK, 0 rows affected (0.07 sec)
 
  
Query OK, 0 rows affected (6.94 sec)
Rows matched: 1  Changed: 0  Warnings: 0

        上面的例子中，只是简单的读t19中的表，但innodb却给t19加了锁，这是为了保证恢复和复制的正确性。


7，innodb在不同隔离级别下的一致性读及锁的差异



8，什么时候用表锁


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

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

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

    当然，应用中这两种事务不能太多，否则，就应该考虑使用myisam表了，在innodb下使用表锁主要注意一下两点

     （1）使用lock tables 虽然可以给innodb 加表级锁，但必须说明的是，表锁不是由innodb存储引擎层管理的，而是由其上一层——mysql server负责的，仅当autocommit=0,innodb_table_locks=1时，innodb层才能自动识别涉及表级锁的死锁；否则，innodb将无法自动检测并处理这种死锁。

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

  

set autocommit=0;
lock tables t20 read;
(do something)...
commit;
unlock tables;

 9，关于死锁

    在innodb中，除了单个sql组成的事务外，锁是逐步获得的，这就决定了innodb中发生死锁是可能的

  

死锁例子
session_1 session_2 
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t18 where id=1 for update;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | fzy1 |    1 |
+----+------+------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t19 where id=1 for update;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f11        | zy1       |    1 |
+----+------------+-----------+------+
1 row in set (0.00 sec)

mysql> select * from t19 where id=1 for update;
等待
因session_2已取得排他锁，等待		  
  
mysql> select * from t18 where id=1 for update;
死锁


  上面这种循环锁就是典型的死锁。发生死锁后，innodb会自动检测，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或涉及表锁的情况下，innodb并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。这个参数并不只是来解决死锁问题，在并发访问较高的情况下，大量事务因为无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重的性能问题，甚至拖垮数据库，这时，我们通过设置锁等待超时阈值，可避免这种情况发生。

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

   好，下面我们来看一个例子：

 

访问不同表表顺序造成的死锁问题
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 first_name,last_name from t19 for update;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| f11        | zy1       |
| f2         | zy2       |
| f3         | zy3       |
| f4         | zy4       |
+------------+-----------+
4 rows in set (0.00 sec)
 
  
mysql> insert into t18(id,name,age)values(14,'fzy14',14);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t18(id,name,age)values(14,'fzy14',14);
等待
 
  
mysql> select * from t19 where id=1 for update;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f11        | zy1       |    1 |
+----+------------+-----------+------+
1 row in set (41.32 sec)

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
死锁		  


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

同一张表死锁例子
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 t19 where id=1 for update;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  1 | f11        | zy1       |    1 |
+----+------------+-----------+------+
1 row in set (0.00 sec)
 
  
mysql> select * from t19 where id=3 for update;
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  3 | f3         | zy3       |    2 |
+----+------------+-----------+------+
1 row in set (0.00 sec)

mysql> select * from t19 where id=3 for update;
等待
 
  
mysql> select * from t19 where id=1 for update ;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
死锁		 
+----+------------+-----------+------+
| id | first_name | last_name | age  |
+----+------------+-----------+------+
|  3 | f3         | zy3       |    2 |
+----+------------+-----------+------+
1 row in set (14.59 sec)
 

  

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

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

    

隔离级别引起的死锁例子
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 first_name,last_name from t19 where id=20 for update;
Empty set (0.00 sec)
 
  
mysql> select first_name,last_name from t19 where id=20 for update;
Empty set (0.00 sec)

mysql> insert into t19(id,first_name,last_name,age)values(20,'f20','zy20',20);
等待
 
  
mysql> insert into t19(id,first_name,last_name,age)values(20,'f20','zy20',20);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
因为其他记录已经对记录进行了更新，这时候插入会提示死锁，并退出
获得锁
Query OK, 1 row affected (16.96 sec)
 


      （5）当隔离级别为read_committed时，如果两个线程都先执行select ... for update ，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第一个线程提交后，第二个线程会因主键重出错，虽然这个线程出错，却会获得一个排他锁，这时如果第三个线程又来申请排它锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行rollback释放获得的排它锁。

   

隔离级别引起的死锁例子
session_1 session_2 session_3
改变事务隔离级别为read committed,设置事务
mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
改变事务隔离级别为read committed,设置事务
mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
改变事务隔离级别为read committed,设置事务
mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select first_name,last_name from t19 where id=22 for update;
Empty set (0.00 sec)

mysql> select first_name,last_name from t19 where id=22 for update;
Empty set (0.00 sec)
 
可以插入记录（在这个隔离级别下）
mysql> insert into t19(id,first_name,last_name,age)values(22,'f22','zy22',22);
Query OK, 1 row affected (0.00 sec)
   
  插入等待获得锁
mysql> insert into t19(id,first_name,last_name,age)values(22,'f22','zy22',22);
等待
 
提交
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
   
  发现主键重了，抛出异常，但没有释放锁
ERROR 1062 (23000): Duplicate entry '22' for key 'PRIMARY'
 
    申请获得共享锁，因为已经锁定，所以需要等待
mysql> select first_name,last_name from t19 where id=22 for update;
  这个时候，直接更新，会出现死锁
mysql> update t19 set age=23 where id=22;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
    死锁异常
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

  

     尽管通过上面道德设计和sql优化等措施，可以大大减少死锁，但死锁很难完全避免。因此，在设计过程中总是捕获并处理死锁异常是一个很好的编程习惯。

  


三，小结与比较

  

    下面对锁问题总结一下：主要介绍了mysql中myisam表级锁和innodb行级锁的实现特点，并讨论了两种存储引擎经常遇到的锁问题和解决方法。

     对于myisam的表锁，主要有一下几点：

       （1）共享锁(s)之间是兼容的，但共享锁(s)与排它锁(x)之间，以及排它锁写锁(x)之间是互斥的，也就是说读和写是串行的。

        （2）在一定条件下，myisqm允许查询和插入并发执行，可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。

       （3）myisam默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以设置low_priority_updates参数，或在insert ,update ,delete 语句中指定 low_prority选项来调节读写锁的争用。

       （4）由于表锁的锁定粒度打，读写之间又是串行的，因此，如果更新操作较多，myisqm表可能会出现严重的锁等待，可以考虑采用innodb表来减少冲突。


     对于innodb表，主要介绍了几点内容：

   （1）innodb next-key锁机制，以及innodb使用next-key锁的原因。

    （2）在不同的隔离级别下，innodb的锁机制和一致性读策略不同。

   （3）mysql的恢复和复制对innodb锁机制和一致性读策略也有较大的影响。

   （4）锁冲突甚至很难避免。


   在了解innodb锁特性后，用户可以通过涉及和sql调整等措施减少锁冲突和死锁，包括一下几项：

  （1）尽量使用较低的事务隔离级别

   （2）精心设计索引，并尽量使用索引访问数据，是加锁更加精确，从而减少锁冲突机会

    （3）选择合理的事务大小，小事务发生锁冲突的几率也更小，

   （4）给记录集显式加锁时，最好一次性请求足够级别的锁，比如要修改数据，最好直接申请排它锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。

   （5）不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行，这样可以大大减少死锁的机会。

    （6）尽量用相等条件访问数据，这样可以避免nex -key锁对并发插入的影响。

    （7）不要申请超过实际需要的锁级别除非必须，查询时不要显示加锁。

   （8）对一些特定的事务，可以使用表锁来提高处理速度或减少死锁发生的概率。


版权声明：本文来源CSDN，感谢博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/fuzhongyu2/article/details/52743680
站方申明：本站部分内容来自社区用户分享，若涉及侵权，请联系站方删除。
  • 发表于 2020-03-01 21:43:59
  • 阅读 ( 1515 )
  • 分类：数据库

你可能感兴趣的文章

精选的优质文章

0 条评论

请先 登录 后评论

官方社群

GO教程

推荐文章

猜你喜欢

随便看看

来源51CTO

如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！