奥门威尼斯网址mysql 开拓升级篇类别 15 锁难点 (总计卡塔尔(قطر‎

by admin on 2019年12月3日

1. 恢复和复制的需要,对innodb锁机制的影响

  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
通过binlog文件对增删除改等更新数据的sql语句,实现数据库的恢复和主从复制。mysql的恢复机制(复制其实就是在slave
mysql不断做基于binglog的恢复)特点有如下:
  (1) mysql 的恢复是sql语句级的,也就是重新执行binlog中的sql语句,
oracle数据库则是基于数据库文件块的。
  (2) mysql
的binlog是按照事务提交的先后顺序记录的,恢复也是按这个顺序进行的。这也与oracle不同,oracle是按照系统更新号(SCN)来恢复数据的。

 
   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行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。下面来实际演示说明:

2.  insert into 和create table对于原表也会加共享锁
 
 下面演示原表加锁的例子:

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

SQL

 

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

会话1

会话2

SET autocommit=0;

SELECT * FROM city WHERE CityCode=’003′

city_id      country_id        cityname CityCode

103  2       杭州         003

SET autocommit=0;

SELECT * FROM city WHERE CityCode=’003′

city_id      country_id        cityname CityCode

103  2       杭州         003

INSERT INTO  cityNew

SELECT  * FROM city WHERE CityCode=’003′

共 1 行受到影响

 

 

UPDATE city SET CityCode=’004′ WHERE CityCode=’003′

等待超时

Lock wait timeout exceeded; try restarting transaction

Commit;

 

 

Commit;

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

条件

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

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

  上面的例子中,只是简单的读取city表,相当于一个普通的select
语句,在这里innodb给city表加了共享锁,并有使用多版本数据一致性技术。原因还是为了保证恢复和复制的正确性,因为不加锁,上述语句的执行过程中,其他事务对city表做了更新操作,可能导致数据恢复结果错误。如需要演示这种可以将系统变量
innodb_locks_unsafe_for_binlog的值设置为”NO”不加共享锁(set
innodb_locks_unsafe_for_binlog=’on’) 默认是”OFF”
。如果设置上面的值为ON,
可能会使Binlog中记录的sql执行顺序不一致,使用恢复的结果与实际的应用逻辑不符,如果进行复制,就会导致主从数据库不一致。
  如果不想设置为ON,又不希望对源表的并发更新产生影响,可以使用 into
outfile 将city表导入到一个txt文件,再使用load data infile
导入到新表。使用这种间接方式不会对源city表加锁。

 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

奥门威尼斯网址 1

 

  

Read commited

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

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

Repeatable read

奥门威尼斯网址 2

  

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

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

Select

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

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

 

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

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

=

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

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

None locks

奥门威尼斯网址 3

         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

长沙

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