Today I'll be showing you how to use transactions to simulate snapshots in bSQL. Because of its multi-version protocols users can access old data by running queries with a set query time. By setting a query time all subsequent queries run as if they had been run at that time. This allows for analysis of old versions of the database, giving you rich data insights. The following diagram illustrates snapshotting in traditional databases and setting the query time with blockpoint's immutable database.
SET TRANSACTION QUERY TIME <query_time> <query_time> ::= timestamp | STATE
Sets queries to run in a snapshot-like environment at the time specified. Values that were AMENDED or DISCONTINUED after the specified time will be restored and relational entities are guaranteed to hold.
The query time is currently specified in UTC time format.
Specifies that queries should operate normally, on the current state of the system.
LIFETIME queries and
QUERY TIMEare mutually exclusive. An error is thrown if a lifetime query is run on any version other than
- Queryies run on a previous database version always use the
READ UNCOMMITTEDisolation level.
- LIFETIME queries and
The time to read from. timestamp can be specified as a timestamp in quotations or an expression that computes to a valid timestamp.
We can use the
QUERY TIME keyword to simulation a snapshot at the time specified. Because the system continuously tracks changes setting the transaction time should have only a small effect on performance. Although all the statements below were technically committed, we can still view previous states.
CREATE BLOCKCHAIN users TRADITIONAL (id UINT64 AUTO INCREMENT PRIMARY, name STRING PACKED); INSERT users (name) VALUES ("john"), ("jimmy"), ("jeff"); --Assume Time = "2021-02-26 00:07:10.000000000" DISCONTINUE users (id, name) VALUES (1, "jimmy"); SET TRANSACTION QUERY TIME "2021-02-26 00:07:10.000000000"; SELECT * FROM users;
Since "jimmy" was inserted into the blockchain before the specified transaction time. The record persists in that state of the database.
Although this example is trivial, the same logic is used for audits and data analysis. It uses the database structure in a unique way to give users access to data evolution over time.