PostgreSQL批量插入是否会导致全表锁及锁查询方法

一、2100万条数据批量插入是否会导致table2全表锁定 #

这个问题的核心取决于你使用的数据库引擎和插入方式：

• MyISAM引擎：MyISAM采用表级锁机制，任何写入操作（包括批量插入）都会直接锁定整个table2，期间其他写入操作（INSERT/UPDATE/DELETE）都会被阻塞，直到批量插入完成释放锁。
• InnoDB引擎：InnoDB默认使用行级锁，但批量插入的锁范围仍可能受以下因素影响：
  • 若使用INSERT INTO table2 SELECT * FROM table1这种单语句批量插入，InnoDB会对table1加共享锁（允许其他读但阻止写），对table2则是逐行加排他锁，不会直接锁全表。但如果插入量极大（2100万条），可能因事务占用资源过多、锁等待队列过长，间接导致后续写入操作出现延迟，看起来像全表被锁，但本质是行锁的累积效应。
  • 若分批次插入（比如每次插入1万条，循环执行），锁的范围会更小，每次仅锁定当前批次插入的行，对后续写入的影响会显著降低。
  • 极端情况下，若批量插入触发了锁升级（比如InnoDB在某些场景下将大量行锁升级为表锁），也会导致全表锁定，但这种情况比较少见，通常和事务大小、索引有效性相关。

二、判断SQL是否可能引发全表锁的方法 #

1. 查看执行计划（EXPLAIN）
   对目标SQL执行EXPLAIN命令，若输出中type字段为ALL（全表扫描），且没有合适的索引支撑操作，InnoDB下可能因扫描范围过大导致锁范围扩大，MyISAM下则必然触发全表锁。
2. 明确数据库引擎的锁机制
   • MyISAM：所有写操作（INSERT/UPDATE/DELETE/ALTER）均为表级锁，读操作是表级共享锁，写锁优先级高于读锁。
   • InnoDB：仅在无索引、索引失效或操作涉及全表数据时（如无WHERE条件的UPDATE），才会触发表级锁；正常带索引的操作都是行级锁。
3. 测试环境模拟观察
   在测试库中还原生产环境的数据量，执行目标SQL的同时，用另一个会话尝试写入table2，观察是否被阻塞。同时可通过数据库命令查看锁状态：
   • MySQL：执行SHOW ENGINE INNODB STATUS查看InnoDB锁详情，或SHOW PROCESSLIST查看会话阻塞状态；
   • PostgreSQL：执行SELECT * FROM pg_locks查看当前锁信息。
4. 检查语句类型
   以下类型的SQL大概率会触发全表锁：
   • MyISAM下的任何批量写入语句；
   • InnoDB下无索引的批量UPDATE/DELETE语句；
   • 涉及ALTER TABLE、TRUNCATE TABLE等DDL语句（无论引擎，都会锁表）；
   • 无WHERE条件或WHERE条件无法命中索引的INSERT ... SELECT语句。

内容的提问来源于stack exchange，提问作者DSi