Subject Re: [firebird-support] Stopping indexes see through Transactions?
Author Ivan Prenosil
Use multicolumn PK/index, and assign CURRENT_TRANSACTION
to the additional column.


----- Original Message -----
From: "johnsparrowuk" <jsparrow@...>
To: <>
Sent: Friday, March 26, 2004 2:33 PM
Subject: [firebird-support] Stopping indexes see through Transactions?

> If I insert something into a PK field (say '1'), and then do the same
> in another transaction context (without committing the first). It
> will fail, yes?
> Presumably the same is true of other unique indexes or constraints -
> they can 'see through' the transaction context?
> Is there a way of preventing this from happening (maybe not with PK's
> but with indexes?)
> Sometimes I use a table as working storage for a sproc, and use
> transacton isolation to prevent one instance seeing data from
> another. When each instance is finnished they 'delete from mytable'
> so the committed state of the table is always empty. (I also empty
> the table at the start of the sproc, just to be sure!)
> Good idea? I can't use PK's because these see through the context, so
> if I must maintain uniqueness I usually have a trigger:
> before insert trigger:
> select count(*) from mytable where pk = into :a;
> if (a > 0) then
> exception key_viol;
> Which seems a bit inefficient...
> I suppose I could use a non-unique index to make the trigger
> faster.. ;)
> Thanks,
> John