25.09.2018 22:37, Karol Bieniaszewski wrote:
Hi Vlad,
Maybe you should go first to the end of may email (quite long – sorry please be
patient)
Not so long ;)
I suppose that i misunderstand names and meaining.
This is the commit number of currently used snapshot, if present. Non-RC transactions create snapshot at own start and
SNAPSHOT_CN is the CN of that snapshot. RCRC transactions create snapshot when user query execution starts. Old style RC
transactions doesn't create snapshots.
Maybe i still do not know what does „snapshot” in name SNAPSHOT_CN mean? Is this CN of last commited snapshot transaction
(excluding any read commited) or what?
If this is last committed snapshot transaction and block progress until next snapshot is commited then it is not so usefull,
Because if read commited transaction change something i will not see changes in delta until new snapshot transaction will be
commited. But maybe my understand of SNAPSHOT_CN is wrong?
If my understanding is ok – then i can fix this by starting empty snapshot transaction and commit it or do any select in RC
transaction before got delta. Then i can run my interesting query with delta fetch. But maybe GLOBAL_CN is better here and fix
all my maining issues.
Seems you understanding (or my explanations) is not complete.
Database have new special counter (Global Commit Number) which incremented
when
any transaction commits. This is GLOBAL_CN. When any transaction commits, engine
increments GLOBAL_CN and assign its value to the transaction. No two committed
transactions could have the same CN assigned.
Database snapshot is identified by value of GLOBAL_CN at the moment when
snapshot
was created. We name this snapshot id as Snapshot CN.
When SNAPSHOT or CONCURRENCY transaction is starts, it creats own database
snapshot.
I.e. this snapshot have Snapshot CN equal to the GLOBAL_CN at the time when
transaction
started. This transaction snapshot is current snapshot for all the statements
running
within this transaction.
When any kind of READ COMMITTED transaction started - it doesn't create any
snapshots.
When some statement start execution within READ COMMITTED READ CONSISTENCY
(RCRC)
transaction, it creates own database snapshot and makes it current until
statement
execution finished. Then this statement-level snapshot is released. Every new
top-level
statement creates its own database snapshot.
Statements, running within READ COMMITTED RECORD VERSION and\or READ
COMMITTED NO RECORD
VERSION transaction doesn't create database snapshot.
Context variable SNAPSHOT_CN returns value of *current* snapshot, if it is
exists.
So, it could be transaction-level snapshot (if statement running within
SNAPSHOT or
CONCURRENCY transaction), or statement-level snapshot (if statement running
within RCRC
transaction).
Hmm... probably you want here SELECT * FROM NAMES WHERE
RDB$GET_TRANSACTION_CN(RDB$RECORD_VERSION) >= :lastCN
If this call to RDB$GET_TRANSACTION_CN will be efficient as separate request per record then yes If not then virtual table will
be much better
You may try it yourself, it should be very fast. Virtual (monitoring) table
is bad here by
the following reasons:
- it could be very big, if Next-OIT is big
- monitoring tables is not indexed - JOIN with such table will be not efficient
- it will be populated for every monitoring snapshot even if no query requires
it
- it will be stable until transaction end - this is not what you need, i believe
And we can add to the WHERE clause „AND RCN.TR_ID>=last_retrived_OAT” Hmm...
not sure i got this idea
Sorry wrong alias AND N.TR_ID>= last_retrived_OAT This is only for using index scan and read only portion of table instead of
natural scan throught whole table.
I should think on it more
With only SNAPSHOT_CN we have same problem as with OAT.
Please, explain
It may not be necessary if the answer to the first question about what
SNAPSHOT_CN is like is that I misunderstood its meaning.
But when i read my above email i think that, first will be good to see sample about value of GLOBAL_CN (GCN) and SNAPSHOT_CN
(SCN) and its real maining e.g. (maybe you can explain this shortly without answer to values of GCN, SCN here)
I assume that all RC transactions below is actually RCRC transactions.
SCN values below related to the corresponding transaction\statement.
1. dabase restored
2. First connection established (GCN=?, SCN=?)
GCN = 1, SCN = NULL (there is no current transaction\statement)
3. Transaction 1 RC start
4. (GCN=?, SCN=?)
GCN = 1, SCN = NULL (there is no current statement)
5. Transaction 2 RC start
6. (GCN=?, SCN=?)
GCN = 1, SCN = NULL
7. Transaction 3 Snapshot start
8. (GCN=?, SCN=?)
GCN = 1, SCN = 1
9. Transaction 1 select
10. (GCN=?, SCN=?)
GCN = 1, SCN = 1
11.Transaction 2 select
12. (GCN=?, SCN=?)
GCN = 1, SCN = 1
13. Transaction 3 select
14. (GCN=?, SCN=?)
GCN = 1, SCN = 1
15. Transaction 1 update
16. (GCN=?, SCN=?)
GCN = 1, SCN = 1 (it is new snapshot, but it have the same number as GCN was
not changed)
17. Transaction 2 update
18. (GCN=?, SCN=?)
GCN = 1, SCN = 1 (it is new snapshot, but it have the same number as GCN was
not changed)
19. Transaction 3 update
20. (GCN=?, SCN=?)
GCN = 1, SCN = 1 (it is same snapshot as in (7))
21. Transaction 2 is commited
22. (GCN=?, SCN=?)
GCN = 2, SCN = NULL (no current transaction\statement),
RDB$GET_TRANSACTION_CN(2) = 2 now
23. Transaction 3 is commited
24. (GCN=?, SCN=?)
GCN = 3, SCN = NULL (no current transaction\statement),
RDB$GET_TRANSACTION_CN(3) = 3 now
25. Transaction 4 snapshot start
26. (GCN=?, SCN=?)
GCN = 3, SCN = 3
27. Transaction 4 rollback!
28. (GCN=?, SCN=?)
GCN = 3, SCN = NULL (no current transaction\statement),
RDB$GET_TRANSACTION_CN(4) = -2 now
but! actually, engine will undo all changes by tx4 and mark it as committed, so
GCN = 4, SCN = NULL (no current transaction\statement),
RDB$GET_TRANSACTION_CN(4) = 4 now
29. Transaction 1 commit
30. (GCN=?, SCN=?)
GCN = 5, SCN = NULL (no current transaction\statement),
RDB$GET_TRANSACTION_CN(1) = 5 now
Hope it is more clear now,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
