如何强制MySQL不对触发器中使用的所有表加锁

你遇到的触发器关联表全表锁并非MySQL的默认强制规则，基本都是存储引擎选择、索引缺失或逻辑设计不合理导致的，可行的优化方案如下：

• 优先切换存储引擎为InnoDB
  如果你使用的是MyISAM等不支持行级锁的引擎，所有写操作都会默认加全表锁，切换到InnoDB后默认使用行级锁，只会锁定实际操作的行。
• 给DML过滤条件字段加索引
  你示例中UPDATE logs SET lbody = 'test' WHERE logid = 5语句，必须给logid字段加主键或唯一索引。没有索引的情况下，InnoDB需要全表扫描定位数据，会将扫描到的所有行加锁，最终等效于全表锁。加索引后只会锁定logid=5的单一行，其他行的读写操作完全不受影响。
• 移除触发器中的耗时操作，缩短锁持有时间
  触发器的执行逻辑和触发它的父DML属于同一个事务，事务未提交前所有持有的锁都不会释放。你示例中的sleep(20)属于典型的长耗时操作，会导致锁被长时间持有，实际业务中必须把这类操作从触发器中移除。
• 用消息队列解耦异步逻辑，替代触发器做跨表操作
  如果跨表更新不需要和原更新保持强一致，建议完全抛弃用触发器实现跨表操作的方案：更新messages表后发送一条事件消息到本地队列，由独立的消费进程异步更新logs表。这样原事务不会持有logs表的任何锁，完全不会影响其他业务对logs表的操作。
• 调整事务隔离级别减少锁范围
  将MySQL的事务隔离级别从默认的REPEATABLE READ修改为READ COMMITTED，该级别会关闭间隙锁，大幅减少InnoDB行锁的锁定范围，避免大量无关行被误锁。

内容的提问来源于stack exchange，提问作者s.abbaasi