您的位置:金沙游乐场85155 > 大数据库 > mysql 开发进阶篇系列 10 锁问题 (相同索引键值或

mysql 开发进阶篇系列 10 锁问题 (相同索引键值或

发布时间:2019-10-08 12:31编辑:大数据库浏览(82)

    会话1

    会话2

    SET autocommit=0;

    SET autocommit=0;

    SELECT * FROM city WHERE city_id=14 AND Cityname='深圳' FOR UPDATE;

    city_id      country_id        cityname CityCode

    14     2       深圳         001

     

     

    会话2与会话1访问的是不同的记录,但是因为使用了相同的索引值,所以需要等待锁

    SELECT * FROM city WHERE city_id=14 AND Cityname='长沙' FOR UPDATE;

    等待...

    事务(Transaction)及其ACID属性

    • 原子性(Actomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
    • 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以操持完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
    • 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
    • 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

    会话1

    会话2

    SELECT @@tx_isolation

    @@tx_isolation

    REPEATABLE-READ

    SELECT @@tx_isolation

    @@tx_isolation

    REPEATABLE-READ

    SET autocommit=0;

    SET autocommit=0;

    -- 当前会话对不存在的记录加 for update;

    SELECT * FROM city WHERE city_id=102 FOR UPDATE;

     

     

    如果这里插入的值>=102就会出现阻塞

    INSERT INTO city VALUES(200,2,'江门','005')

    错误代码: 1205

    Lock wait timeout exceeded; try restarting transaction

     

    ROLLBACK;

     

     

    INSERT INTO city VALUES(200,2,'江门','005')

    共 1 行受到影响

    行锁的三种形式

    1. Record lock:锁定一条记录。
    2. Gap lock
    3. Next-key lock

       如果查询使用如下sql
      select * from city where city_id>100 for update;

    例子

    事务A
    select * from table where id='1';
    .
    select * from table where id='1';
    .
    select * from table where id='1';

    上述例子中事务B update以后事务A第一次select的时候RC级别和RR级别获取的结果都是id=1的那一条数据;第二次select的时候,由于事务B已经提交,RC级别select的结果就是id=3,而RR级别读取的是事务开始时的数据,id=1。

    图片 1

    排他锁

    select * from table_name where .....for update

    Note left of 事务1: select * from table_1 where id=1 for update;
    事务1-->事务2: 
    Note right of 事务2: select * from table_1 where id=1 for update;
    Note right of 事务2: 等待...
    事务2-->事务1: 
    Note left of 事务1: update table_1 set age=10 where id=1;
    Note left of 事务1: 更新完后释放锁
    事务1-->事务2: 
    Note right of 事务2: 获得锁后,得到其他事务提交的记录
    

      由于mysql 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但如果是使用相同的索引键,是会出现锁冲突的。设计时要注意
      例如:city表city_id字段有索引,Cityname字段没有索引:

    一致性非锁定读

    一致性非锁定读是指InnoDB存储引擎通过多版本并发控制技术来读取当前数据库的数据。如果当前读取的行正在执行delete或者update操作,这时读取操作不会等行锁的释放,而是去读取行的快照数据。

    图片 2

    非锁定一致性读.png

    快照数据是指改行之前版本的数据,该实现是通过undo段来实现的,而undo段用来在事务中保存回滚数据,因此使用快照没有增加额外的开销。
    这是InnoDB存储引擎的默认读取方式。

    1.使用相同索引键值的冲突

    一致性锁定读

    默认配置下事务的隔离级别为REPEATABLE READ,select操作为非一致性锁定读,但某些情况下需要对数据库读取操作进行加锁保证数据的一致性。select 有两种一致的锁定读:

    • select ... for update
    • select ... lock in share mode

      当表有多个索引时候,不同的事务可以使用不同的索引锁定不同的行,无论什么索引,innodb都会使用行锁来对数据加锁。
      例如city表city_id字段有主键索引,CityCode字段有普通索引:

    自增长与锁

    InnoDB存储引擎内部对每个含有自增长列的表有一个自增长计数器,当进行insert操作时,首先获取计数器的最大值,加1后进行insert操作。这个操作会加一个特殊的表锁,AUTO-INC LOCK。这个锁并不是在事务提交后才释放,而是在insert语句执行完后释放。

    4. 间隙锁(next-key锁) 并发下要重点考虑

    mysql行锁的特性

    1. innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.
      实例:
      id是主键
      | id| name|
      | -| - |
      | 1 | 1 |
      | 2 | 2 |
      | 3 | 3 |
      事务1update第一条id=1的数据,事务不提交;事务2接着update第二条id=2的数据的时候等待,原因是id没有加上索引,导致事务1锁的是表锁而不是行锁。

    2. 如果是使用相同的索引键,会出现锁冲突。
      示例:tab_with_index表中id字段有索引,name字段没有索引。
      事务1:

    select * from tab_with_index where id = 1 and name = '1' for update;
    

    事务2:

    select * from tab_with_index where id = 1 and name = '4' for update;
    

    虽然事务2访问的是和事务1不同的记录,但是因为使用了相同的索引,所以需要等待锁。

    1. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
      示例:表tab_with_index的id字段有主键索引,name字段有普通索引。
      事务1:
    select * from tab_with_index where id = 1 for update;
    

    事务2:

    select * from tab_with_index where name = '2' for update;
    

    事务2使用name的索引访问记录,因为记录没有被索引,所以也可以获得锁。

     2.使用不同索引键值但是同一行的冲突 

    mysql 锁机制

    标签(空格分隔): mysql


             当我们用范围条件而不是相等条件检索数据,并请求共享或排它锁时,innodb会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录就叫做"间隙锁"  比如city表数据分布如下:

    例子

    TODO...

    3. 创建了索引,但使用的是表锁
      在前面章节说过,创建了索引但不走索引的情况,这种情况下innodb将使用表锁,而不是行锁,因些分析锁冲突时,还需检查sql的执行计划,以确认是否真正使用了索引。

    外键与锁

    在对外键值进行update和insert操作时首先需要查询父表的记录,即select父表,这个select操作不是使用一致性非锁定读,因为会发生数据不一致的问题,为此需要使用一致性锁定读,这时使用的select ... lock in share mode方式。当父表对应记录加X锁后,子表的操作将会阻塞。

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:mysql 开发进阶篇系列 10 锁问题 (相同索引键值或

    关键词: