Skip to main content

SET TRANSACTION (bSQL) | Blockpoint Docs

Modifies the current session and its subsequent transactions.

bSQL Syntax Conventions

Syntax

SET TRANSACTION <transaction_modifier>

<transaction_modifier> ::=
{
ISOLATION LEVEL <isolation>
| QUERY TIME <query_time>
}


<isolation> ::=
READ UNCOMMITTED
| READ COMMITTED
| SERIALIZABLE

<query_time> ::=
timestamp
| STATE

Arguments

ISOLATION LEVEL
Modifies the current isolation level. Once the session has been modified all transactions within the session will run using the specified isolation level. Default isolation is READ UNCOMMITTED.

[!NOTE] Currently the only supported isolation levels are READ UNCOMMITTED, READ COMMITTED, and SERIALIZABLE.

  • Isolation levels are implemented using MVCC.

isolation
The isolation level to set the connection to.

QUERY TIME
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.

[!NOTE] The query time is currently specified in UTC time format.

  • Limitations
    • LIFETIME queries and QUERY TIME are mutually exclusive. An error is thrown if a lifetime query is run on any version other than STATE.
    • Queryies run on a previous database version always use the READ UNCOMMITTED isolation level.

timestamp
The time to read from. timestamp can be specified as a timestamp in quotations or an expression that computes to a valid timestamp.

STATE
Specifies that queries should operate normally, on the current state of the system.

Examples

A. Setting the session isolation level.

We use the ISOLATION LEVEL keywords to specify the isolation level.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

A. Setting the query time.

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;

OUTPUT

Since "jimmy" was inserted into the blockchain before the specified transaction time. The record persists in that state of the database.

IDNAME
0john
1jimmy
2jeff

See Also