--- In firebird-support@yahoogroups.com, "nathanelrick" <nathanelrick@...> wrote:

>
> 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 ...)
>

Hi,

i am curious why do you use also date - not only id in this statement?
id is not primary key?

> Update Session Set
> data=...
> Expiry_date= NOW + 15minutes
> where
> id=... and
> Date_expired < 'NOW'

and more relevant question
how offen do you fire update of session in every request?
if in every request then this is not good solution.

if you need to control session in e.g. 15 minutes live then
you can optimise record versions count like this

Update Session Set
data=...
Expiry_date= NOW + 15minutes
where
id=... and
Date_expired > DATEADD(-5 minute to CAST('now' AS TIMESTAMP))

in this scenario you generate record version in every 5 minutes not in every request

also look at external tables - if you need update in every request - this is better for you (no record versions)
But i do not know how many active sessions are in the same time (external tables do not use indexes)

Karol Bieniaszewski