浅谈MySQL的七种锁-阿里云开发者社区

一、共享锁（S锁）/排他锁（X锁）

事务拿到某一行记录的共享S锁，才可以读取这一行，并阻止别的事物对其添加X锁
事务拿到某一行记录的排它X锁，才可以修改或者删除这一行
共享锁的目的是提高读读并发
排他锁的目的是为了保证数据的一致性

二、意向锁

1、意向共享锁
预示事务有意向对表中的某些行加共享S锁

2、意向排他锁
预示着事务有意向对表中的某些行加排他X锁

3、 IS、S、IX、X锁之间的兼容性比较：

兼容性	IS	IX	S	X
IS	兼容	兼容	兼容	互斥
IX	兼容	兼容	互斥	互斥
S	兼容	互斥	兼容	互斥
X	互斥	互斥	互斥	互斥

4、意向锁的意义在哪里？

1.IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突
2.意向锁是在添加行锁之前添加。
3.如果没有意向锁，当向一个表添加表级X锁时，就需要遍历整张表来判断是否存行锁，以免发生冲突
4.如果有了意向锁，只需要判断该意向锁与表级锁是否兼容即可。

三、插入意向锁（insert intention looks）

插入意向锁是间隙锁的一种，针对insert操作产生。
目的是提高插入并发。
多个事物，在同一个索引，同一个范围区间进行插入记录的时候，如果插入的位置不冲突，不会阻塞彼此。

示例：

t1(id primary key,id1 int)

mysql> select * from t1;
+----+------+           
| id | id1  |           
+----+------+           
| 10 |   10 |           
| 20 |   20 |           
| 30 |   30 |           
+----+------+           
3 rows in set (0.00 sec)

mysql> start transaction;                             mysql> start transaction;             
Query OK, 0 rows affected (0.00 sec)                  Query OK, 0 rows affected (0.00 sec)  
                                      
mysql> insert into t1 values(11,11);                  mysql> insert into t1 values(12,12);  
Query OK, 1 row affected (0.00 sec)                   Query OK, 1 row affected (0.00 sec)   
                                                                                            
mysql> select * from t1;                              mysql> select * from t1;                            
+----+------+                                         +----+------+                                       
| id | id1  |                                         | id | id1  |                                       
+----+------+                                         +----+------+                                       
| 10 |   10 |                                         | 10 |   10 |                                       
| 11 |   11 |                                         | 12 |   12 |                                       
| 20 |   20 |                                         | 20 |   20 |                                       
| 30 |   30 |                                         | 30 |   30 |                                       
+----+------+                                         +----+------+                                       
4 rows in set (0.00 sec)                              4 rows in set (0.00 sec)

由于事物一和事物二都是对表的同一索引范围进行insert,使用的插入意向锁，由于插入的记录并不冲突，所以并不会阻塞事物二。如果事物二插入的记录与事物一冲突，会被X锁阻塞。

四、记录锁

对单条索引记录进行加锁，锁住的是索引记录而非记录本身，即使表中没有任何索引，MySQL会自动创建一个隐式的row_id作为聚集索引来进行加锁。

t1(id int primary key,id1 int)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=20 for update;    //记录锁，锁住id=20
+----+------+
| id | id1  |
+----+------+
| 20 |   20 |
+----+------+
1 row in set (0.00 sec)
                                                      mysql> start transaction;                
                                                      Query OK, 0 rows affected (0.00 sec)     
                                         
                                                      mysql> update t1 set id1=200 where id=20;      //被hang住

五、间隙锁（gap锁）

封锁记录中的间隔，防止间隔中被其他事务插入。
间隙锁主要出现在RR隔离级别，避免出现幻读。

1、MVCC（多版本并发）

1.MVCC的作用

避免脏读、写不阻塞读、实现可重复读、多版本控制

2.在MVCC下，读操作可以分为两种：快照读、当前读

1）快照读

select * from tbl_name where ...

2）当前读
select * from tbl_name where ... for update;
update
delete
insert

3）为什么delete/update也是一种当前读？(如一个update操作)

a.在进行update的时候，MySQL会根据where条件得到过滤出来的第一条记录，并进行加锁（currenet read）
b.对该条记录进行update
c.再次读取下一条记录，直到没有满足条件的记录为止
d.delete原理与之类似

4）为什么insert也是一种当前读？
insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

2、隔离级别

1.Read Uncommitted

可以读取到未提交的事物

2.Rrad Committed(RC)

针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。

3.Repeatable Read (RR)

针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

4.Serializable

所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。
读写冲突，并发行很差

3、几种触发间隙锁的情况

1.id非唯一索引+RR

SQL：delete from t1 where id = 10;

加锁流程如下：

    
    a.通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，
    b.然后加主键聚簇索引上的记录X锁，然后返回；
    c.然后读取下一条，重复进行。
    d.直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

2.id无索引+RR

SQL：delete from t1 where id = 10;

加锁流程如下：

    
    a.由于id字段无索引，进行全表扫描的当前读，
    b.聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙都被加上了GAP锁。

3.针对id无索引+RR MySQL性能上做的一些优化

semi-consistent read
semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。
针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁。

4.semi-consistent read如何触发？

1）隔离级别是read committed；
2）隔离级别是Repeatable Read，同时设置了 innodb_locks_unsafe_for_binlog 参数。

示例一：非唯一索引 + 等值当前读

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete  from t2 where myid = 100;
Query OK, 2 rows affected (0.00 sec)   
                                                                                                                        mysql> start transaction;                                                                                                                          
                                                            Query OK, 0 rows affected (0.00 sec)                     
                                                         
                                                            mysql> insert into t2 values(3,98);                      
                                                            ^C^C -- query aborted                                    
                                                            ERROR 1317 (70100): Query execution was interrupted      
                                                            mysql> insert into t2 values(134,98);                    
                                                            ^C^C -- query aborted                                    
                                                            ERROR 1317 (70100): Query execution was interrupted      
                                                            mysql> insert into t2 values(7,104);                     
                                                            ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'
                                                            mysql> insert into t2 values(8,104);                     
                                                            ^C^C -- query aborted                                    
                                                            ERROR 1317 (70100): Query execution was interrupted      
                                                            mysql> insert into t2 values(118,104);                   
                                                            ^C^C -- query aborted                                    
                                                            ERROR 1317 (70100): Query execution was interrupted      
                                                            mysql> insert into t2 values(118,105);                   
                                                            Query OK, 1 row affected (0.00 sec)

示例二：非唯一索引 + 范围当前读

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select  * from t2 where myid > 100  for update;
+-----+------+
| id  | myid |
+-----+------+
|  98 |  105 |
| 123 |  109 |
+-----+------+
2 rows in set (0.00 sec)


                                                                                                                 mysql> start transaction;                          
                                                            Query OK, 0 rows affected (0.00 sec)               
                                                   
                                                            mysql> insert into t2 values(8,100);               
                                                            ^C^C -- query aborted                              
                                                            ERROR 1317 (70100): Query execution was interrupted
                                                            mysql> insert into t2 values(4,100);               
                                                            Query OK, 1 row affected (0.00 sec)                
                                                   
                                                            mysql> insert into t2 values(3,101);               
                                                            ^C^C -- query aborted                              
                                                            ERROR 1317 (70100): Query execution was interrupted
                                                            mysql> insert into t2 values(99,101);              
                                                            ^C^C -- query aborted                              
                                                            ERROR 1317 (70100): Query execution was interrupted
                                                            mysql> insert into t2 values(99,108);              
                                                            ^C^C -- query aborted                              
                                                            ERROR 1317 (70100): Query execution was interrupted
                                                            mysql> insert into t2 values(134,128);             
                                                            ^C^C -- query aborted                              
                                                            ERROR 1317 (70100): Query execution was interrupted

示例三：主键索引 + 范围当前读

mysql> select  * from t2 where id > 100  for update;
+-----+------+
| id  | myid |
+-----+------+
| 123 |  109 |
| 999 |   56 |
+-----+------+
2 rows in set (0.00 sec)

                                                                                mysql> start transaction;                                                                            
                                                            Query OK, 0 rows affected (0.00 sec)                    
                                                        
                                                            mysql> insert into t2 values(99,192);                   
                                                            ^C^C -- query aborted                                   
                                                            ERROR 1317 (70100): Query execution was interrupted     
                                                            mysql> insert into t2 values(125,192);                  
                                                            ^C^C -- query aborted                                   
                                                            ERROR 1317 (70100): Query execution was interrupted     
                                                            mysql> insert into t2 values(1259,192);                 
                                                            ^C^C -- query aborted                                   
                                                            ERROR 1317 (70100): Query execution was interrupted     
                                                            mysql> select * from t2 where id=123 lock in share mode;
                                                            ^C^C -- query aborted                                   
                                                            ERROR 1317 (70100): Query execution was interrupted     
                                                            mysql> select * from t2 where id=124 lock in share mode;
                                                            Empty set (0.00 sec)

六、临键锁(Next-Key Locks)

临键锁是记录锁和间隙锁的组合，既锁住了记录也锁住了范围。

临键锁的主要目的，也是为了避免幻读。

如果把事务的隔离级别降级为RC，临键锁就也会失效。

通常情况下，InnoDB在搜索或扫描索引的行锁机制中使用“临键锁(next-key locking)”算法来锁定某索引记录及其前部的间隙(gap)，以阻塞其它用户紧跟在该索引记录之前插入其它索引记录。

innodb_locks_unsafe_for_binlog默认为OFF，意为禁止使用非安全锁，也即启用间隙锁功能。将其设定为ON表示禁止锁定索引记录前的间隙，也即禁用间隙锁，InnoDB仅使用索引记录锁(index-record lock)进行索引搜索或扫描，不过，这并不禁止InnoDB在执行外键约束检查或重复键检查时使用间隙锁。

innodb_locks_unsafe_for_binlog的效果：

（1）对UPDATE或DELETE语句来说，InnoDB仅锁定需要更新或删除的行，对不能够被WHERE条件匹配的行施加的锁会在条件检查后予以释放。这可以有效地降低死锁出现的概率；
（2）执行UPDATE语句时，如果某行已经被其它语句锁定，InnoDB会启动一个“半一致性(semi-consistent)”读操作从MySQL最近一次提交版本中获得此行，并以之判定其是否能够并当前UPDATE的WHERE条件所匹配。如果能够匹配，MySQL会再次对其进行锁定，而如果仍有其它锁存在，则需要先等待它们退出。
（3）innodb_locks_unsafe_for_binlog可能会造成幻读

示例一：innodb_locks_unsafe_for_binlog=off的情况下：

mysql> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `id1` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(11) NOT NULL,
  `id1` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t4;
+----+------+------+
| id | id1  | id2  |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
|  4 |    4 |    4 |
|  5 |    5 |    5 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> select * from t5;
Empty set (0.00 sec)

mysql> insert into t5 select * from t4 where id2=3;
Query OK, 1 row affected (0.34 sec)
Records: 1  Duplicates: 0  Warnings: 0

                                                                                                                            mysql> start transaction;                                                                                              
                                                              Query OK, 0 rows affected (0.00 sec)    
                                        
                                                              mysql> update t4 set id1=33 where id2=3;    //被hang住

mysql> commit;
Query OK, 0 rows affected (0.01 sec)
                                                                                                                            Query OK, 1 row affected (9.15 sec)          //事物一提交后，update操作执行成功                                                         
                                                              Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t5;
+----+------+------+
| id | id1  | id2  |
+----+------+------+
|  3 |    3 |    3 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select * from t4 where id2=3;
+----+------+------+
| id | id1  | id2  |
+----+------+------+
|  3 |    3 |    3 |
+----+------+------+
1 row in set (0.00 sec)

示例二：innodb_locks_unsafe_for_binlog=on的情况下

mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | ON    |
+--------------------------------+-------+
1 row in set (0.35 sec)

mysql> select * from t4;
+----+------+------+
| id | id1  | id2  |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
|  4 |    4 |    4 |
|  5 |    5 |    5 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> select * from t5;
Empty set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t5 select * from t4 where id2=3;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t5;
+----+------+------+
| id | id1  | id2  |
+----+------+------+
|  3 |    3 |    3 |
+----+------+------+
1 row in set (0.00 sec)
                                                                                                                        mysql> start transaction;                                                                              
                                                            Query OK, 0 rows affected (0.00 sec)     
                                         
                                                            mysql> update t4 set id2=333 where id2=3;                       //事物一未提交的情况下，直接更新成功，不会有阻塞
                                                            Query OK, 1 row affected (0.00 sec)      
                                                            Rows matched: 1  Changed: 1  Warnings: 0 
                                         
                                                            mysql> select * from t4 where id=3;      
                                                            +----+------+------+                     
                                                            | id | id1  | id2  |                     
                                                            +----+------+------+                     
                                                            |  3 |    3 |  333 |                     
                                                            +----+------+------+                     
                                                            1 row in set (0.00 sec)                  
                                         
                                                            mysql> select * from t5;                 
                                                            Empty set (0.00 sec)  
                                                            
                                                            mysql> commit;                                     //事物二先提交     
                                                            Query OK, 0 rows affected (0.00 sec)    

mysql> commit;                               //事物一后提交                                                                                             
Query OK, 0 rows affected (0.01 sec)                                                                                   
                                    
mysql> select * from t5;            
+----+------+------+                
| id | id1  | id2  |                
+----+------+------+                
|  3 |    3 |    3 |                
+----+------+------+                
1 row in set (0.00 sec)             
                                    
mysql> select * from t4 where id=3; 
+----+------+------+                
| id | id1  | id2  |                
+----+------+------+                
|  3 |    3 |  333 |                
+----+------+------+                
1 row in set (0.00 sec)

查看binlog日志：

BEGIN
/*!*/;
# at 565
#180926 16:27:06 server id 1013306  end_log_pos 609     Table_map: `test1`.`t4` mapped to number 125
# at 609
#180926 16:27:06 server id 1013306  end_log_pos 667     Update_rows: table id 125 flags: STMT_END_F

BINLOG '
muurWxM6dg8ALAAAAGECAAAAAH0AAAAAAAEABXRlc3QxAAJ0NAADAwMDAAY=
muurWx86dg8AOgAAAJsCAAAAAH0AAAAAAAEAAgAD///4AwAAAAMAAAADAAAA+AMAAAADAAAATQEA
AA==
'/*!*/;
### UPDATE `test1`.`t4`                                     //事物二先提交，所以binlog日志中先记录对t4的更新操作
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
###   @3=3 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
###   @3=333 /* INT meta=0 nullable=1 is_null=0 */
# at 667
#180926 16:28:38 server id 1013306  end_log_pos 694     Xid = 82
COMMIT/*!*/;
# at 694
#180926 16:28:52 server id 1013306  end_log_pos 755     GTID    last_committed=1    sequence_number=3    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'f2754eef-6a6e-11e8-8b99-000c2971d3ea:1451'/*!*/;
# at 755
#180926 16:25:57 server id 1013306  end_log_pos 824     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1537993557/*!*/;
BEGIN
/*!*/;
# at 824
#180926 16:25:57 server id 1013306  end_log_pos 868     Table_map: `test1`.`t5` mapped to number 126
# at 868
#180926 16:25:57 server id 1013306  end_log_pos 912     Write_rows: table id 126 flags: STMT_END_F

BINLOG '
VeurWxM6dg8ALAAAAGQDAAAAAH4AAAAAAAEABXRlc3QxAAJ0NQADAwMDAAY=
VeurWx46dg8ALAAAAJADAAAAAH4AAAAAAAEAAgAD//gDAAAAAwAAAAMAAAA=
'/*!*/;
### INSERT INTO `test1`.`t5`                        //事物一后提交所以对于insert ... select 操作在binlog中后记录，但是set的记录仍然是事物二为修改之前的值
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
###   @3=3 /* INT meta=0 nullable=1 is_null=0 */
# at 912
#180926 16:28:52 server id 1013306  end_log_pos 939     Xid = 78
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

因此，当innodb_locks_unsafe_for_binlog=on的情况下，会让你容易造成数据的不一致。

七、自增长锁

自增长锁是一种表级锁，专门针对auto_increment类型的列。

自增长列锁各模式分析：

innodb_autoinc_lock_mode：自增长长锁模式


0：
    不管是insert into values (simple insert)还是insert into select (bulk insert)，都是：持有锁、读取/修改、执行SQL、释放，不需要等到事务提交就释放锁，但是需要SQL执行完成，并且不能保证连续。
    持有latch ---> 读取和修改auto锁 ---> 执行insert ---> 释放
    注意：不需要等待insert所在的事务是否提交
    缺点：可能出现数字不连续
    持有时间相对过长：SQL执行完毕，不需要事务提交

1：
    默认值，对于回滚是不能保证自增长列连续的。
    对于simple insert （insert into values）：持有锁、读取、释放、执行SQL,最快，不需要执行完SQL就释放，不需要等待insert执行完毕就可以释放锁。
    对于bulk insert （insert into select）：持有锁、读取、执行SQL、释放，需要执行完SQL才释放。（对于批量insert来说等同于0）
    优点：
        对于simple insert 来说，性能比0好些，对于批量来说，性能等同于0
    缺点：
        数字不连续
        对于批量来说持有锁的时间相对过长

2：
    经常改为2，主要是为了唯一，不是为了连续，在批量insert时或者批量insert并发的时候用
    优点：速度最快
    缺点：只能保证唯一，不能保证递增和连续。持有、读取和修改、释放、执行SQL
    建议修改成2，对于批量的insert可以提升性能

示例：
1、自增长锁

t2(id automent_ment,id1 int)

mysql> show variables like 'innodb_autoinc_lock_mode';                        
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.04 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2(id1) values(11);                            
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+------+
| id | id1  |
+----+------+
|  1 |   11 |
+----+------+
1 row in set (0.00 sec)                   
                   
                                                     mysql> start transaction;             
                                                     Query OK, 0 rows affected (0.00 sec)  
                                      
                                                     mysql> insert into t2(id1) values(22);
                                                     Query OK, 1 row affected (0.03 sec) 
                                                                                             
mysql> insert into t2(id1) values(33);                         
Query OK, 1 row affected (0.00 sec)                            
                                                               
mysql> select * from t2;                                       
+----+------+                                                  
| id | id1  |                                                  
+----+------+                                                  
|  1 |   11 |
|  3 |   33 |
+----+------+
2 rows in set (0.00 sec)                               
                                                    mysql> select * from t2;
                                                     +----+------+           
                                                     | id | id1  |           
                                                     +----+------+           
                                                     |  2 |   22 |           
                                                     +----+------+           
                                                     1 row in set (0.00 sec)

由于innodb_autoinc_lock_mode=1，所以事物一并不会阻塞事物二的simple insert，保证了id字段的唯一性

参考引用：
何登成的技术博客/MySQL 加锁处理分析：http://hedengcheng.com/?p=771
微信公众号/架构师之路：https://mp.weixin.qq.com/mp/profile_ext?action=home&__biz=MjM5ODYxMDA5OQ==&scene=124wechat_redirect

浅谈MySQL的七种锁