Hi,
I see from other emails that you have had a few discussions on various
combinations of isc_tpb_write + isc_tpb_read_committed +
> isc_tpb_no_rec_version + isc_tpb_wait and so on. I suppose you have your
reasons for using non-default transaction modes. (i.e. not simple
concurrency).
Having said that, when I need to delete a large amount of rows (typically
after having archived them to another database) I always do it via a
stored procedure, where I have control. I also always use normal
concurrency transactions:
Create procedure Delete_my_rows
as
declare variable dbkey char(8);
begin
for select rdb$db_key
from mytable
where some condition
into :dbkey do
begin
delete from mytable where rdb$db_key = :dbkey;
when any do
begin
/* either nothing, or log to another table*/
end
end
end;
In this way, if a row is locked or we are waiting for a transaction to
finish (because you are using isc_tcb_wait), we can simply carry on and
any locked rows will be deleted next time we run.
Regards
Vince
Vince Duggan
I.T. Architect
Virgin Active South Africa (Pty) Ltd
Tel (+27) (0)21 684 3525
Fax (+27) (0)21 684 3225
Cell (+27) (0)82 747 6127
Email: [email protected]
Live happily ever active
[email protected] wrote on 2012/02/28 05:03:59 PM:
> [image removed]
>
> [firebird-support] Update take hours to delete records :(
>
> nathanelrick
>
> to:
>
> firebird-support
>
> 2012/02/28 05:04 PM
>
> Sent by:
>
> [email protected]
>
> Please respond to firebird-support
>
>
> Hello,
>
> I have a table that i use to store session
>
> Table Session
> ID: VARCHAR(16); /* GUID ID */
> Expiry_date: Timestamp;
> Data: Varchar(1000);
>
> i use the table like this only :
>
> Insert into Session(ID,Expiry_date,Data)
> VALUES (CHAR_TO_UUID(NewGUID), NOW + 15minutes,...)
>
> Update Session Set
> data=...
> Expiry_date= NOW + 15minutes
> where
> id=... and
> Date_expired < 'NOW'
>
> Select ... from SESSION where
> ID=... and Expiry_date > 'NOW'
>
> THat ok,
>
> now the probleme is that i have another thread that one time a day
> delete all the expired node
>
> Delete From SESSION where Expiry_date < NOW - 3 days
>
> i use isc_tpb_write + isc_tpb_read_committed +
> isc_tpb_no_rec_version + isc_tpb_wait for this second thread (but
> normally as i delete the rec that expire 3 days ago they must not be
> any probleme of deadlock)
>
> but the probleme is that this SQL make hours (days?) to return :(
> if i replace the isc_tpb_wait by isc_tpb_nowait then i have often
> some deadlock (why ?? still not understand ...)
>
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
[Non-text portions of this message have been removed]
