图片 2

mysql 开发进阶篇系列 13 锁问题(关于表锁,死锁示例,锁等待设置)

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

一.概述

一. 哪些时候利用表锁

  对于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

会话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行锁的这一特征,不然的话,可能导致大批量的锁争执,进而影响并发质量。下边来实在演示验证:

二. 关于死锁

  在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会自动检验到,并使三个作业释放锁并回落(回滚),另二个业务得锁实现业务。

 

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

三. 锁等待查看    

  涉及外界锁或表锁,innodb并不能够一心自动物检疫测到死锁,那供给设置锁等待超时参数innodb_lock_wait_timeout来减轻(设置需谨慎),这几个参数实际不是只用来消除死锁难题,在并发下,多量作业不能马上获得所需锁而挂起,将占领大批量能源,以致拖跨数据库
(在sql server中暗中认可是-1 总是等待)。

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

图片 1

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

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

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