PostgreSQL常用DML操作的锁类型归纳

DML锁类型分析

本文对PostgreSQL的insert、 update、 truncate、 delete等常用DML操作的锁类型进行了归纳类比： 包括是否排他、 共享、 表级、 行级等的总结。

• truncate ：access exclusive mode（block all read/write）、table-level lock(表级锁)

• insert ： ROW EXCLUESIVE mode ;表级锁；其它事务可以select, 其它事务不能create index、create trigger、刷新物化视图、不能DROP TABLE/TRUNCATE/REINDEX/CLUSTER/VACUUM_FULL/ REFRESH MATERIALIZED VIEW (without CONCURRENTLY) 。看来insert对于数据来说，其它事务受影响不大，可以更新也可以删除，也可以查询。

• update:

  • 1.ROW EXCLUSIVE mode ;表级锁；其它事务可以与不可以做的描述同上insert。
  • 2.FOR UPDATE mode ;行级锁；其它事务可以select, 但阻止锁定的行被其它事务修改删除、SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHAREorSELECT FOR KEY SHARE
  • 3.FOR NO KEY UPDATE;行级锁；不修改主键的更新获取该锁，其它事务允许SELECT\SELECT FOR KEY SHARE, 但阻止锁定的行被其它事务修改删除、SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE

• delete:

  • 1.ROW EXCLUSIVE ;表级锁；其它事务可以与不可以做的描述同上insert。
  • 2.FOR UPDATE mode ;行级锁；其它事务可以select, 但阻止锁定的行被其它事务修改删除、SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHAREorSELECT FOR KEY SHARE

注：上文中的XXX mode 是PostgreSQL官方文档对于锁模式的几种定义类型，具体可以去PostgreSQL官方文档查询，链接为https://www.postgresql.org/docs/current/sql-lock.html。

定义理解：

共享锁: 查询共享期不允许修改，要保持数据的一致性。

排他锁：变更期间不允许查询与其它变更，防止不一致与冲突。

一次优化实践：

写了一个很长包含很多Insert与update操作的存储过程，在定时任务中会调用这个存储过程，存储过程中每次执行都会清理结果表的数据，并重新生成结果，把结果重新保存到结果表。在接手到前任写的代码时，在清理的时候使用了truncate，后来随着业务增长，数据量越来越大，存储过程执行越来越慢，这样在执行这个定时任务时，业务上去查询这个结果表，都得卡死等待定时任务执行完才会开始查询。

经过思考，原来是truncate操作会排它锁全表，即使其它任何读操作也被阻塞。而且这个锁定的时间范围扩大到了整个存储过程，因为整个存储过程是一个完事的事务。

后来把truncate表的操作改成了delete操作，成功解决了这个问题。delete操作不会阻塞读操作。存储过程未执行过程中，其它事务依然可以读取结果表的数据，只不过是读到的是上一次的结果，因为这一次还没有执行完。这个存储过程的事务隔离级别是读已经提交，只有存储过程的事务执行完成后，才会被其它事务看到，在存储过程 中，执行的delete全结果表，对其它事务查询结果表是不影响的。