mysql 开发进阶篇系列 15 锁问题 (总结)

by admin on 2019年12月3日

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

一.概述

  1. innodb
    行锁是基于索引实现的,如果不通过索引访问数据,innodb会使用表锁。

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

一.概述

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

  在数据库中,数据是属于共享资源,为了保证并发访问的一致性,有效性,产生了锁。接下来重点讨论mysql锁机制的特点,常见的锁问题,以及解决mysql锁问题的一些方法或建议。
相比其他数据库,mysql 锁机制比较简单,显著的特点是
不同的存储引擎支持不同的锁机制。在innodb中支持行锁和表锁,默认行锁。

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

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

SQL

  mysql 的三种锁归纳如下:
  表级锁:开销小,加锁快,不会出现死锁,锁定粒度大,发生锁冲突的概率最高,并发度最低。
  行级锁:开销大,加锁慢;会出现死锁,锁定粒度最小,发生锁冲突的概率最低,并发度最高。
  页面锁:开销和加锁时间界于表锁和行锁之间,会出现死锁,并发度一般。
  在不同sql语句执行,会采用不同的锁,由mysql
内部自动加锁,解锁,以及对应的锁类型。重点介绍mysql表锁和innodb行锁。由于MyisAm将被innodb取代了,后面重点讲innodb。

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

 

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

条件

  1. 事务介绍

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

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

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

Read uncommited

  innodb的二个特点是一是支持事务,二是采用行级锁,但事务的引入也带来了一些新问题,先介绍一下背景知识。

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

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

  1.1 事务及其属性ACID
    事务是同一组sql语句组成的逻辑处理单元,具有原子性,一致性,隔离性,持久性。

  

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

Repeatable read

  1.2 并发事务处理带来的问题
    更新丢失,脏读,不可重复读,幻读。

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

图片 2

serializable

  1.3 事务隔离级别
    由于并发事务处理带来的问题,那么解决方法就是对应不同的事务隔离级别。
数据库实现事务隔离的方式,基本上分为以下两种:
    (1)一种是在读取数据前,对其加锁,阻止其他事务对数据进行修改。
    (2)另一种是不用加任何锁,通过快照 Snapshot 形式的
“数据版本并发控制” (MultiVersion Concurrency Control)简称MVCC。
    数据库的事务隔离级别越严格,并发副作用越小,付出的代价也就越大,因为实质上就是使事务在一定程度上”串行化”进行,这与”并发”是矛盾的。

  

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

Select

    下面是四种隔离级别:

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

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

 

图片 3

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

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

=

 

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

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

None locks

 

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

图片 4

Consisten 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

长沙

None locks

 

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

Consisten read/

  

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

None locks

 

Share locks

 

范围

 

None locks

 

Consisten read/

 

None locks

 

Consisten read/

2. 隔离级别查看

None locks

-- 查看事务隔离级别,默认是REPEATABLE-READ,在sql server里默认是Read Committed
SELECT @@tx_isolation

Share next-key

图片 5

Update

  关于事务,事务并发问题,事务隔离级别,它们的原理这里有详细介绍“sql
server
锁与事务拨云见日”。(未完)

=

 

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