浅谈MySQL的七种锁

  1. 云栖社区>
  2. 袋鼠云技术团队>
  3. 博客>
  4. 正文

浅谈MySQL的七种锁

sansi_dba 2018-09-30 14:36:05 浏览9996
展开阅读全文

一、共享锁(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

1

        SQL:delete from t1 where id = 10;

    加锁流程如下:

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

2.id无索引+RR

2

        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 参数。

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

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)    


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

4

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


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

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

网友评论

登录后评论
0/500
评论
sansi_dba
+ 关注
所属团队号: 袋鼠云技术团队