Michael Vilhelmsen wrote:

>
> When starting a transaction (as first connected user) I have the
> oldest active transactions.

Right.

>
> If I never do anything again - the gab will grow.

Gap, but right. Until the oldest active transaction commits,
and all its contemporary transactions end, it continues to be the
oldest active.

>
> But when the transaction commits, the oldest active is not this one
> anymore.

Not quite. All transactions that were running at the same time have
to end as well.

>
> What if it is a rollback instead ?

Same thing.

>
>
> Or a SavePiont (Using IBO) ?

Issuing a save point does not affect the oldest active.

>
>
> Or a crash of an application ?

Anything that ends the transaction ... in the case of an application
crash the oldest active won't move until the server notices the crash
and all contemporary transactions end.

As Vlad said, the oldest active is based on what's happening at the
moment. Here's how it's tracked.

When a transaction starts, it gets an exclusive lock in the lock
table on its own transaction id. The lock entries have space for
32bits worth of data with the lock. For transaction locks, those
32 bits contain the transaction id of the oldest locked transaction.
So, when I start transaction 2030, I query the lock table for the
lowest numbered transaction lock. The lock manager tells me 1230.

OK, I put 1230 in the data portion of my lock and take a lock on
2030. Eventually, 1230 commits and releases its lock. Newer
transactions will put a higher number in their lock data, but I've
still got 1230, because that's still the oldest transaction whose
changes I'm not allowed to read - assuming that I'm a concurrency
or snapshot transaction.

When the system is deciding what it can garbage collect, it asks
the lock manager for the lowest transaction id in lock data. That
information is cached, and updated as transactions end. Eventually
my transaction and all the other transactions that overlapped 1230
will end and the oldest active transaction value will move.

When the system shuts down, the lock table goes away and the first
transaction that starts will become the oldest active.


Note this sequence ....

Transaction 1230 starts and runs for a long time.
Lots of other transactions start and end
Transaction 21040 starts and runs
Somebody notices that the oldest active is stuck and
stops transaction 1230.
Nothing happens to the oldest active
Transaction 21040 eventually ends
Now the oldest active moves

Stopping the oldest active transaction is not enough. You've
also got to stop all the transactions that overlapped it.

Regards,


Ann