Re: Improve the concurency of vacuum full table and select statement on the same relation

Get it now, thanks.
The lock upgrade(from ExclusiveLock to AccessExclusiveLock) may result in deadlocks iff one transaction first takes an AccessShareLock and then takes a lock(lockmode > AccessShareLock) on the same relation.

The single SQL statement can't take an AccessShareLock and then takes a lock(lockmode > AccessShareLock) on the same relation.
In fact, there is lock upgrade in transaction block which includes multiple query.like this transaction block "start transaction; select query; DML/DDL", it is from AccessShareLock to lockmode > AccessShareLock.

Now there may be deadlocks when run multiple transaction blocks even if no vacuum full. The some transaction will report error to break deadlock once deadlocks happen. So the vacuum full table may failed after doing lots of work in some time.

In some scenes, there are not explicit transaction block (no lock upgrade from AccessShareLock to lockmode > AccessShareLock), the deadlocks rarely happens. perhaps we can provide an option for vacuum full to let user choose whether "cluster/vacuum full" block select statement for very short time.

Are there other solutions to improve the concurency of vacuum full/cluster and select statement on the same relation?

Jinyu Zhang,
thanks

At 2015-10-10 23:34:41, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>Jinyu <call_jinyu(at)126(dot)com> writes:
>> Proposal: vacuum full table takes an ExclusiveLock on relation instead of AccessExclusiveLock at start. It can' block select statement before call function "finish_heap_swap". and select statement is safe because vacuum full table copys tuples from old relation to new relation before calling function "finish_heap_swap". But it must take an AccessExclusiveLock on relation when call function "finish_heap_swap" in order to block select statement on the same relation.
>
>> This solution can improve the concurency. the following shows the reasons.
>
>What it's more likely to do is cause the vacuum full to fail altogether,
>after doing a lot of work. Lock upgrade is a bad thing because it tends
>to result in deadlocks.
>
> regards, tom lane