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

一. 什么时候使用表锁

　　对于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释放表锁。

　　会话2设置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;

 

二. 关于死锁

　　在myisam中是使用的表锁，在获得所需的全部锁时， 要么全部满足，要么等待，因此不会出现死锁(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会自动检测到，并使一个事务释放锁并回退(回滚)，另一个事务得锁完成事务。

 

三. 锁等待查看时间　　　　

　　涉及外部锁或表锁，innodb并不能完全自动检测到死锁，这需要设置锁等待超时参数innodb_lock_wait_timeout来解决(设置需慎重)，这个参数并不是只用来解决死锁问题，在并发下，大量事务无法立即获得所需锁而挂起，将占用大量资源，甚至拖跨数据库 (在sql server中默认是-1 总是等待)。

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

转载于:https://www.cnblogs.com/MrHSR/p/9409090.html