TRUNCATE in postgresql

最新推荐文章于 2024-08-14 09:13:33 发布

cudi7618

最新推荐文章于 2024-08-14 09:13:33 发布

阅读量588

点赞数

文章标签：数据库

本文探讨了PostgreSQL中truncate命令的行为，特别是在事务安全性和MVCC兼容性方面的特性。通过对比MySQL和Oracle，展示了PostgreSQL如何支持truncate操作的回滚，并深入分析了其在并发场景下的行为。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在我们的印象里，truncate 命令是 ddl 是没有事务的。
在mysql 跟oracle 里是这样的。

在pg 里truncate 是事务安全的，也就是是说是可以回滚的。

[code]
cyp_app=>     begin;
BEGIN
Time: 0.130 ms
cyp_app=> select count(*) from tmp_lsl;
count
-------
   140
(1 row)

Time: 0.358 ms
cyp_app=> truncate table tmp_lsl ;
TRUNCATE TABLE
Time: 0.371 ms
cyp_app=> select count(*) from tmp_lsl;
count
-------
     0
(1 row)

Time: 0.274 ms
cyp_app=> rollback;
ROLLBACK
Time: 91.841 ms
cyp_app=> select count(*) from tmp_lsl;
count
-------
   140
(1 row)

Time: 0.401 ms
[/code]

这个功能得益于PG的mvcc 的实现。

但是truncate 操作不是MVCC-safe的。
什么意思呢？

我们看看先：
session A :
[code]
cyp_app=> begin;
BEGIN
Time: 0.122 ms
cyp_app=> select count(*) from tmp_lsl;
count
-------
   140
(1 row)

Time: 0.282 ms
[/code]

session B :
[code]
BEGIN
cyp_app=> select count(*) from tmp_lsl ;
count
-------
   140
(1 row)

[/code]

session A : 继续执行：
[code]
cyp_app=> truncate table tmp_lsl;

这个时候操作已经被挂起了，
[/code]

session B:

[code]
继续下一步操作

cyp_app=> select count(*) from tmp_lsl ;
count
-------
   140
(1 row)

cyp_app=> delete from tmp_lsl;
DELETE 140
cyp_app=>

cyp_app=> rollback;
ROLLBACK

session B 可以执行删除。
[/code]

session A
[code]
这个是sessiona 的truncate 返回了

cyp_app=> truncate table tmp_lsl;
                TRUNCATE TABLE
Time: 4953153.416 ms
cyp_app=>
cyp_app=>                 rollback;
ROLLBACK
Time: 92.143 ms
cyp_app=>
[/code]

在看另一个
sessionＡ：　
[code]
cyp_app=> begin;
BEGIN
Time: 0.123 ms
cyp_app=> select count(*) from tmp_Lsl;
count
-------
   140
(1 row)

Time: 0.377 ms
cyp_app=> truncate table tmp_lsl;
TRUNCATE TABLE
Time: 0.331 ms
cyp_app=>
表已经被truncate 了但是没有提交。
[/code]

开启session B
[code]
cyp_app=> begin;
BEGIN
cyp_app=> select count(*) from tmp_lsl;

sessionB 被挂起了，无法查询到结果：
[/code]

session A 继续：
[code]
cyp_app=> commit;
COMMIT
Time: 92.136 ms
cyp_app=>
session A 提交数据库修改：
[/code]

sessionB ：
[code]
cyp_app=> select count(*) from tmp_lsl;
count
-------
     0
(1 row)

cyp_app=> commit;
COMMIT
cyp_app=>

sesssion B 返回了0行，session b 看到的是session A 执行完成后的数据快照。
[/code]

什么意思呢？根据我们设置的readcommited 的事务隔离级别，sessionB 看到sessionA 提交的结果是对的。

主要的问题是truncate 不是mvcc-safe 的，就是truncate 的操作发生后，对所有的事务都返回一个空的结果集，不论这个事务是否在truncate 操作发生前还是发生后。

对于前面的那个，session b 不提交，则session a 的truncate 被挂起，则是因为session b 只有的access share 共享锁，阻止了truncate clusive 锁的获得。

truncate 不会带来任何数据一致性的问题。

因为这个原因，我们在在pg 同步oracle 的表的时候，本想直接在一个事务里truncate table 然后再insert 的做法，似乎就会有些行不通。

这回导致与阻止其他事务，或者被其他事务阻止，而insert 是全表数据，如果数据量比较大，阻塞其他事务的机会应该更大一些。

比较靠谱的操作是用 a  b 表轮换的方式。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133735/viewspace-732292/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/133735/viewspace-732292/