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