mysql 开发进阶篇系列 9 锁问题 (Innodb 行锁实现方式)

by admin on 2019年10月24日
  1. innodb
    行锁是基于索引实现的,如果不通过索引访问数据,innodb会使用表锁。

1. innodb在不同隔离级别下的一致性读及锁的差异
  不同的隔离级别下,innodb处理sql
时采用的一致性读策略和需要的锁是不同的,同时,数据恢复和复制机制的特点,也对一些sql的一致性读策略和锁策略有很大影响。对于许多sql,
隔离级别越高,innodb给记录集的锁就越严格(龙其是使用范围条件的时候),产生的锁冲突的可能性也就越高,对并发性事务处理性能的影响也就越大。因此,在应用中,应该尽量使用较低的隔离级别,减少锁争用。通常使用Read
Commited隔离级别就足够了,
对于一些确实需要更高隔离级别的事务,可能在程序中执行 SET SESSION
TRANSACTION ISOLATION LEVEL REPEATABLE READ 或SET SESSION TRANSACTION
ISOLATION LEVEL SERIALIZABLE 动态来改变隔离级别。 

1 .innodb 共享锁(lock in share mode)演示

一.概述

1 .获取innodb行锁争用情况

 
   http://www.cnblogs.com/MrHSR/p/9376086.html

  下面重点看下REPEATABLE READ与Read commited
锁申请的不同区别,在增删改查上申请的锁都是一致的,但在事务中锁释放的时间是不一样的这点需要注意。

会话1

会话2

SET autocommit=0;

SELECT cityname FROM  city WHERE city_id=14;

city_id      country_id        cityname CityCode

14     2       22     001

SET autocommit=0;

SELECT cityname FROM  city WHERE city_id=14;

city_id      country_id        cityname CityCode

14     2       22     001

— 对 city_id=14加共享锁

SELECT * FROM  city WHERE city_id=14 LOCK IN SHARE MODE;

city_id      country_id        cityname CityCode

14     2       22     001

 

 

— 也对 city_id=14加共享锁

SELECT * FROM  city WHERE city_id=14 LOCK IN SHARE MODE;

city_id      country_id        cityname CityCode

14     2       22     001

— 当前会话对锁定的记录进行更新操作,等待锁。

UPDATE city  SET cityname=’深圳’ WHERE city_id =14;

 等待中….

 

 

— 会话2也对锁定的记录进行更新操作,则会导致死锁退出

UPDATE city  SET cityname=’深圳’ WHERE city_id =14;

错误代码: 1213

Deadlock found when trying to get lock; try restarting transaction

获得锁后,更新成功

查询:update city set cityname=’深圳’ where city_id =14

共 1 行受到影响

 

  Innodb 行锁是通过给索引上的索引项加锁来实现的。这一点与(oracle,sql
server)不同后者是通过在数据块中对相应的数据行加锁。这意味着只有通过索引条件检索数据,innodb才使用行级锁,否则
innodb将使用表锁。

  在实际应用中,特别要注意innodb行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。下面来实际演示说明:

  1.1 通过检查innodb_row_lock状态变量来分析系统上的行锁的争夺情况

 2. Innodb 间隙锁(Next-key)机制,以及innodb使用间隙锁的原因

SQL

 

  1.  innodb 的表条件CityCode不使用索引时,使用的是表锁例子

SHOW STATUS LIKE 'innodb_row_lock%'

  http://www.cnblogs.com/MrHSR/p/9390350.html

条件

2.  innodb 排它锁(for update)演示

-- 查询表中数据共二条
SELECT * FROM  city;

图片 1

 3.不同隔离级别下,innodb的锁机制和一致性读策略不同。

Read uncommited

会话1

会话2

SET autocommit=0;

SELECT * FROM  city WHERE city_id=14;

city_id      country_id        cityname CityCode

14     2       22     001

SET autocommit=0;

SELECT * FROM  city WHERE city_id=14;

city_id      country_id        cityname CityCode

14     2       22     001

— 对 city_id=14加for update 排它锁

SELECT cityname FROM  city WHERE city_id=14 FOR UPDATE ;

cityname

11

 

 

— 可以查询

SELECT cityname FROM  city WHERE city_id=14

cityname

11

— 但不能对 city_id=14加for update 排它锁

SELECT cityname FROM  city WHERE city_id=14 FOR UPDATE ;

等待中…

— 更新后,释放锁

UPDATE city  SET cityname=’深圳’ WHERE city_id =14;

COMMIT;

 

 

获取锁 for update共享锁,值还是11

cityname

11

图片 2

    通过innodb_row_lock_waits 和 innodb_row_lock_avg
的值来判断行锁争用情况,值高意味着争用比较严重。

  

Read commited

-- 条件字段CityCode不走索引
EXPLAIN SELECT * FROM city WHERE CityCode='001'

  1.2 通过检查 innodb
monitors来观察发生锁冲突的表,数据行等,并分析锁争用的原因

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

Repeatable read

图片 3

-- 查看分析
SHOW ENGINE INNODB STATUS;

  

serializable

会话1

会话2

SET autocommit=0;

SELECT * FROM  city WHERE CityCode=’001′;

city_id      country_id        cityname CityCode

14     2       深圳         001

SET autocommit=0;

SELECT * FROM  city WHERE CityCode=’002′;

city_id      country_id        cityname CityCode

15     2       长沙         002

— 加锁

SELECT cityname FROM  city WHERE CityCode=’001′ FOR UPDATE ;

cityname

深圳

 

 

— 加锁

SELECT cityname FROM  city WHERE CityCode=’002′ FOR UPDATE ;

等待…

错误代码: 1205

Lock wait timeout exceeded; try restarting transaction

  status内容中会详细说明当前锁等待的信息,包括表名,锁类型,锁定记录的情况等,以便进行进一步的分析和问题的确定。以后在详细分析

 5.调整锁冲突和死锁策略

Select

    通过上面的案例 会话1只给一行加了排它锁,
但会话2在请求其它行的排他锁时,却出现了锁等待。原因就是在没有索引的情况下,innodb只能使用表锁。

图片 4

         5.1 尽量使用较低的隔离级别

 

  2. innodb 的表条件CityCode使用索引时,使用的是行锁例子

2. innodb 行锁模式及加锁方法

         5.2
精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会。

=

-- 添加索引
ALTER TABLE city ADD INDEX ix_citycode(CityCode)
-- CityCode走索引
EXPLAIN SELECT * FROM city WHERE CityCode='001'

两种锁模式(类型)共享锁, 排它锁。
  共享锁(S):允许其它事务读取该行数据,阻止其它事务获取排他锁去更新。
  排它锁(x): 阻止其它事务读取或更新。
为了允许行锁和表锁共存,实现多粒度锁机制,innodb内部使用意向锁,这二种意向锁都是表锁。
  意向共享锁(IS): 当前事务打算给数据行加S锁,必须先取得该表的IS锁。
  意向排它锁(IX): 当前事务打算给数据行加x锁,必须先取得该表的IX锁。

         5.3 选择合理的事务大小,小事务发生锁冲突的几率也更小。

None locks

图片 5

下图是innodb 行锁模式兼容性列表

         5.4
给记录集显示加锁时,最好一次性请求足哆级别的锁。比如要修改数据的话,最好直接申请排它锁,而不是先申请共享锁,修改时再请求排它锁,这样容易死锁。

Consisten read/

会话1

会话2

SET autocommit=0;

SELECT * FROM  city WHERE CityCode=’001′;

city_id      country_id        cityname CityCode

14     2       深圳         001

SET autocommit=0;

SELECT * FROM  city WHERE CityCode=’002′;

city_id      country_id        cityname CityCode

15     2       长沙         002

— 加锁

SELECT cityname FROM  city WHERE CityCode=’001′ FOR UPDATE ;

cityname

深圳

 

 

— 加锁

SELECT cityname FROM  city WHERE CityCode=’002′ FOR UPDATE ;

cityname

长沙

图片 6

         5.5 不同程序访问一组表时,尽量约定以相同的顺序访问各表。

None locks

  通过上图可以总结出来: S与IS锁在事务与事务之间是兼容的。
ix锁与(ix锁或is锁)在事务与事务之间是兼容的。
is锁与(IX,S,IS锁)是兼容的。X锁与其它锁是互斥的。
  当事务将要获取的锁与其它事务的锁兼容时,就会授予该事务锁,否则该事务就要等待其它事务锁的释放。
  对于update,delete,insert语句,innodb会自动给涉及的数据集加X锁(对于delete,update会先加S锁再升级为X锁)。
  对于普通的select语句innodb会加S锁。

         5.6
尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。

Consisten read/

3. 语句级加锁

None locks

  lock in share mode(语句集上加IS锁)
    在select 语句最后加上lock in share
mode相当于手动加上共享锁。确保当前没有人对该记录进行update或delete操作。
  for update(语句集上加IX锁)
    select for update是对锁定行记录后,需要进行更新操作的应用。
        (未完)

Share locks

范围

None locks

Consisten read/

None locks

Consisten read/

None locks

Share next-key

Update

=

X(排它锁)

X

X

X

范围

X next-key

X next-key

X next-key

X next-key

Insert

 

X

X

X

X

REPLACE

无键冲突

X

X

X

X

键冲突

X next-key

X next-key

X next-key

X next-key

Delete

 

=

X

X

X

X

范围

X next-key

X next-key

X next-key

X next-key

Select ..from

Lock in share mode

=

Share locks

Share locks

Share locks

Share locks

范围

Share locks

Share locks

Share next-key

Share next-key

Select ..from

For update

=

X

X

X

X

范围

X

Share locks

X next-key

X next-key

Insert into..

Select ..

Innodb_locks_unsafe

_for_binlog=off

Share next-key

Share next-key

Share next-key

Share next-key

Innodb_locks_unsafe

_for_binlog=on

None locks

Consisten read/

None locks

Consisten read/

None locks

Share next-key

Create table..

Select ..

Innodb_locks_unsafe

_for_binlog=off

Share next-key

Share next-key

Share next-key

Share next-key

Innodb_locks_unsafe

_for_binlog=on

None locks

Consisten read/

None locks

Consisten read/

None locks

Share next-key

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图