Skip site navigation (1) Skip section navigation (2)

Re: transactions getting slon in councurrent environment

From: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
To: Tigran Mkrtchyan <tigran(dot)mkrtchyan(at)desy(dot)de>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: transactions getting slon in councurrent environment
Date: 2006-12-29 21:50:17
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
Tigran Mkrtchyan wrote:
> Does it mean that I have to commit after each select statement?
> Here what the manual says:
> ------------
> Description
> COMMIT commits the current transaction. All changes made by the 
> transaction become visible to others and are guaranteed to be durable 
> if a crash occurs.
> -----------
> Does select produces some changes?
No, but with autocommit off, you are effectively doing
SELECT * from x;

and then leaving the transaction open.  The way MVCC works is to write 
new data into the heap when you insert/update.  It doesn't replace the 
data.  As this select transaction is open, it may still need to read 
data from before the update.  So VACUUM can't clean that data out of the 
table when it runs and the data still needs to be available to that 

So basically if you have autocommit off, the transaction stays open.  
You can either set autocommit on, or you can COMMIT the transaction.

> Regards,
>     Tigran.
> Tom Lane wrote:
>> Tigran Mkrtchyan <tigran(dot)mkrtchyan(at)desy(dot)de> writes:
>>> I got a incorrect or unexpected behavior in concurrent environment.
>> This is not a bug, nor even surprising.  Since you haven't committed
>> the second transaction, there are a growing number of
>> dead-but-not-recyclable versions of the updated row.  The active client
>> has to check each of these versions during its primary key uniqueness
>> check during each update.
>>             regards, tom lane

In response to

pgsql-bugs by date

Next:From: GeorgeDate: 2006-12-30 22:57:55
Subject: BUG #2868: Help
Previous:From: Dave PageDate: 2006-12-29 09:25:33
Subject: Re: BUG #2865: Cannot save the data in pgAdmin

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group