> On 25/07/2014 06:46, brucedickinson@... [firebird-support] wrote:
>>
>> Hi,
>>
>> 1. I've deleted 500000 records from TABLE A. I've commited transaction.
>> 2. I made SELECT * FROM TABLE A. Transaction was starting for a very
>> long time.
>>
>> I just wanted to make sure that there is no way to prevent this
>> behaviour? I mean, I can not delay somehow clearing of this garbage?
>>
>> As far as I understand this article:
>> http://www.firebirdsql.org/manual/gfix-housekeeping.html
>>
>>
>> there is no such possibility becasue:
>>
>>
>> "Whenever a subsequent transaction reaches garbage from a /committed/
>> transaction, that garbage is automatically cleared out."
>>
> So it appears. Essentially part of the cost of doing the deletion is
> palmed off onto some later user, who is faced with unpredictable delays
> in consequence. For example an overnight housekeeping job can result in
> the first end user transaction of the day taking orders of magnitude
> longer than usual (and timing out and crashing, if its timeouts are set
> to something reasonable).
>
> You can avoid this cost being passed onto the poor user who through no
> fault of his own is the first to follow the housekeeping by including
> the "SELECT * FROM TABLEA" as part of the cleanup job, thus putting the
> cost back with its owner. In other words you can't delay the garbage
> collection, but you can make it happen earlier so that it's predictable.

SELECT COUNT(*) might be better because all pages are read and only a
single value is transferred to the client. And the whole only works as
advertised when old record versions are no longer "interesting" to other
concurrent transactions.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.