mysql 开发进阶篇系列 14 锁问题(避免死锁,死锁查看分析)

by admin on 2019年11月6日

一. 概述

  通常来说,死锁都是应用设计问题,通过调整业务流程,数据库对象设计,事务大小,以及访问数据库的sql语句,绝大部分死锁都可以避免,下面介绍几种避免死锁的常用
方法.
  1.
在应用中,如果不同的程序并发操作多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。按顺序对表进行操作,是很常用的一种避免死锁的操作。
比如:有二个不一样的存储过程,同时在对一个表进行复杂的删改操作。这种情况可以考虑先让一个执行完成,再让另一个在执行。
  2.
在程序中以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。比如常见的就是多线程下在程序中lock锁住,在进程下保持串行处理。
  3.
在事务中,如果要更新记录,应该直接申请足够级别的锁,即排它锁,而不是先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其它事务可能又已经获得了相同记录的共享锁,从而造成锁冲突。
我理解是在事务中首先将要更新的记录,以select .. for
update方式获得排它锁,
在事务里处理完逻辑后就可以直接更新而不用考虑锁冲突。 代码如下:

SET autocommit=0
-- 将要更新的数据先获得排它锁
SELECT * FROM city WHERE city_id=103 FOR UPDATE;
-- 逻辑处理  ....
-- 最后更新可以避免锁冲突
UPDATE city SET cityname='杭州' WHERE city_id=103;
COMMIT;

  4. 在默认级别Repeatable read下, 如果两个线程同时对相同条件记录用
select .. for update 加排它锁,在没有符合该条件记录情况下,两个线程都会加锁成功。当一个程序发现记录不存在,就试图插入一条新数据,如果两个线程都这么做,就会出现死锁。这是因为在Repeatable
read下产生了间隙锁。这种情况下,将隔离级别改成Read
commited,就可避免问题 如下图表格
贴出了二个隔离级别下产生锁的差异。

图片 1

  5. 当在Repeatable read下,如果两个线程都先执行select .. for update。
在判断是否存在符合条件的记录,如果没有,就插入记录,此时,只有一个线程能插入成功,另一个线程会出现锁等待,
当第1个线程提交后,第2个线程如因为主键值重复,会出现异常。但却获得了一个排它锁,
需要执行rollback释放排它锁。避免影响其它事务。
  总结:尽管通过上面介绍和sql
优化等措施,可以大大减少死锁,但死锁很难完全避免。因此。
在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。在程序异常里或commit或rollback。

一. 什么时候使用表锁

  对于INNODB表,在绝大部分情况下都应该使用行锁。在个别特殊事务中,可以考虑使用表锁(建议)。
  1.
事务需要更新大部份或全部数据,表又比较大,默认的行锁不仅使这个事务执行效率低,可能造成其他事务长时间锁等待和锁冲突,这种情况考虑使用表锁来提高事务的执行速度(具我在sql
server中的经历,该大表有上100w,删除40w,表锁有时会造成长时间未执行完成.
还是使用分批来执行好)。
  2.
事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况可以考虑一次性锁定事务涉及的表,避免死锁,减少数据库因事务回滚带来的开销。
  使用表锁注意两点
    (1) lock
tables虽然可以给innodb加表锁,但表锁不是由innodb存储引擎层管理,则是由上层mysql
server负责。仅当autocommit=0,
innodb_table_locks=1(默认设置)时,innodb层才知道mysql加的表锁,mysql
server也才能感知innodb加的行锁。
    (2) 用lock tables对innodb表加锁时要注意, 要将autocommit
设置为0,否则mysql 不会给表加锁; 事务结束前,不要用unlock
tables释放表锁,因为它会隐式的提交事务。 commit 或rollback
并不能释放用lock tables 加的表锁。必须用unlock tables释放表锁。

    下面在5.7版本数据库中,会话2也会阻塞,按上面说法是不会阻塞的,因为会话1没有设置SET
autocommit =0(以后在论证)

-- 会话1 给city加表锁读,  不设置  SET autocommit =0
  LOCK TABLES city READ

  --  会话2 会阻塞
 UPDATE city SET CityCode='005' WHERE city_id=103  

  -- 会话1提交
 COMMIT;
 -- 会话1 释放表锁
 UNLOCK TABLES;
  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)演示

二. 检查死锁产生的原因

  如果出现死锁,可以用SHOW ENGINE INNODB STATUS
命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的sql语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等,以此分析死锁产生的原因和改进措施。

-- 查看最后一个死锁
SHOW ENGINE  INNODB STATUS;

LATEST DETECTED DEADLOCK
------------------------
2018-08-02 18:07:45 0x7f3a12209700
*** (1) TRANSACTION:
TRANSACTION 35489574, ACTIVE 114 sec STARTING INDEX READ
mysql TABLES IN USE 1, locked 1
LOCK WAIT 4 LOCK struct(s), HEAP size 1136, 2 ROW LOCK(s)
MySQL thread id 2634494, OS thread handle 139887387092736, QUERY id 109768880 172.168.18.202 root Sending DATA
-- 因为会话2 已获得排他锁, 些语句 等待
 SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 479 page NO 3 n bits 72 INDEX GEN_CLUST_INDEX of TABLE `test`.`cityNew` trx id 35489574 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 35489577, ACTIVE 8 sec STARTING INDEX READ, thread declared inside INNODB 5000
mysql TABLES IN USE 1, locked 1
4 LOCK struct(s), HEAP size 1136, 3 ROW LOCK(s)
MySQL thread id 2634624, OS thread handle 139887388956416, QUERY id 109768953 172.168.18.202 root statistics
-- 死锁
 SELECT * FROM city  WHERE city_id=103 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 479 page NO 3 n bits 72 INDEX GEN_CLUST_INDEX of TABLE `test`.`cityNew` trx id 35489577 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 477 page NO 3 n bits 80 INDEX PRIMARY of TABLE `test`.`city` trx id 35489577 lock_mode X LOCKS rec but NOT gap waiting
*** WE ROLL BACK TRANSACTION (2)
------------

二. 关于死锁

  在myisam中是使用的表锁,在获得所需的全部锁时,
要么全部满足,要么等待,因此不会出现死锁。下面在innodb中演示一个死锁例子:

会话1

会话2

SET autocommit =0

SELECT * FROM city  WHERE city_id=103 FOR UPDATE;

SET autocommit =0

SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE;

— 因为会话2 已获得排他锁, 该语句等待

 SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE;

 

 

— 死锁

 SELECT * FROM city  WHERE city_id=103 FOR UPDATE;

错误代码: 1213

Deadlock found when trying to get lock; try restarting transaction

  上面案例中,
两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。
发生死锁后,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并不能完全自动检测到死锁,这需要设置锁等待超时参数innodb_lock_wait_timeout来解决(设置需慎重),这个参数并不是只用来解决死锁问题,在并发下,大量事务无法立即获得所需锁而挂起,将占用大量资源,甚至拖跨数据库
(在sql server中默认是-1 总是等待)。

--  下面是5秒  获取不到锁就超时
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

图片 2

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

SQL

 

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

条件

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

 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

  

Read commited

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

Repeatable read

  

serializable

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

Select

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

 

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

=

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

None locks

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

Consisten read/

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