mysql 开荒进级篇种类 12 锁难点(隔开分离等第下锁的出入)

by admin on 2019年12月3日

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

  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)演示

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

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

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

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

  下面重点看下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 行受到影响

 

会话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;

等待…

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

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

SQL

 

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

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

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

条件

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

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

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

澳门威尼斯人官方网站 1

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

会话1

会话2

SET autocommit=0;

SET autocommit=0;

SELECT * FROM city WHERE city_id=14  FOR UPDATE;

city_id      country_id        cityname CityCode

14     2       深圳         001

 

 

该记录没有被索引,所以可以获得锁

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

city_id      country_id        cityname CityCode

15     2       长沙         002

 

由于该记录被会话1锁定,所以需要等待

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

等待…

  

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

Read commited

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

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

澳门威尼斯人官方网站 2

Repeatable read

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

  

会话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

serializable

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

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

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

Select

澳门威尼斯人官方网站 3

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

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

 

 

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

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

=

   如果查询使用如下sql
  select *澳门威尼斯人官方网站, from city where city_id>100 for update;

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

澳门威尼斯人官方网站 4

None locks

  这就是一个范围条件的检索,
innodb不但会对符合条件的101的记录加锁,也会对city_id大于101(虽然记录并不存在)的”间隙”加锁。使用间隙锁的目的是为了防止幻读,以满足相关的隔离级别。关于幻读查看”sql
开发进阶篇系列 6 锁问题(事务与隔离级别介绍)”
很明显,在使用范围条件的检索记录时,
会阻塞符合条件范围内键值的并发插入,往往造成严重的锁等待。在实现业务中尽量使用相等条件来检索数据。还需注意如查使用相等条件检索的数据不存在时,也会加间隙锁。
  为了防止幻读,mysql隔离级别必须是REPEATABLE-READ和Serializable。REPEATABLE-READ也是默认的隔离级别。

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

会话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

长沙

Consisten read/

会话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 行受到影响

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

None locks

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

Consisten read/

None locks

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地图