7 - 07:18:17.051888

FAQ2

How to start transaction as "FIRST SNAPSHOT"?

Sometimes you need to be sure that no other transactions do modification to database or can commit data from time before our snapshot start.

Trick with DB trigger

We need table with interested connections. The connections which must start "FIRST SNAPSHOT" transaction. This sample is good only if your system have short time transactions and not many.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
/* increase when transaction start */ CREATE GENERATOR GEN_TR_COUNTER; /* Table with interesting connections - you must fill it before use feature 'First Snapshot' transaction */ CREATE TABLE I_CONNECTIONS ( CON_ID INTEGER ); /* exceptions to stop transaction start */ CREATE EXCEPTION ESTOP_TRANSACTION 'OTHER NOT READONLY TRANSACTIONS ARE PENDING'; /* DB trigger at transaction start to block transaction start if any other not readonly transactions exists */ SET TERM ^ ; CREATE TRIGGER DB_TR_START FOR ACTIVE ON TRANSACTION START POSITION 0 AS DECLARE VARIABLE VAR_COUNT BIGINT; DECLARE VARIABLE NR_TR BIGINT; BEGIN NR_TR = GEN_ID(GEN_TR_COUNTER, 1); (SELECT COUNT(*) FROM I_CONNECTIONS IC WHERE IC.CON_ID=CURRENT_CONNECTION) INTO :VAR_COUNT; IF (VAR_COUNT>0) THEN BEGIN SELECT COUNT(*) FROM MON$TRANSACTIONS T WHERE NOT EXISTS(SELECT * FROM I_CONNECTIONS IC WHERE IC.CON_ID=CURRENT_CONNECTION) AND T.MON$ATTACHMENT_ID<>CURRENT_CONNECTION AND NOT (T.MON$READ_ONLY=1 /*AND T.MON$ISOLATION_MODE=3*/ ) INTO :VAR_COUNT; END IF (VAR_COUNT>0) THEN EXCEPTION ESTOP_TRANSACTION; IF (NR_TR <> GEN_ID(GEN_TR_COUNTER, 0)) THEN /* avoid asynhronous trigger handling - if another db trigger is fired in the middel of this trigger start/end */ EXCEPTION ESTOP_TRANSACTION; END^ SET TERM ; ^
As you can see above - interesting connections can start only "First Snapshot" transaction. If any other not readonly transaction are pending triger prevent transaction start. You must try again to aquire "First Snapshot" transaction in interesting connection.
8 - 07:18:17.051888