SNAPSHOT [AT NUMBER snapshot_number] usage

[amolna...@gmail.com]

Hi Mark!

Since Firebird 4.0 transaction has SNAPSHOT [AT NUMBER snapshot_number] feature.

https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-transacs-settransac

How can I use it in Jaybird?

Thank You!

[Mark Rotteveel]

Option 1:

Use a custom transaction parameter buffer and put the transaction number
in with TpbItems.isc_tpb_at_snapshot_number (available in Jaybird 5 and
higher).

See also
https://firebirdsql.github.io/jaybird-manual/jaybird_manual.html#transactions-tpb

Compared to the example linked, use something like:

```
long snapshotNumber = ... // obtained elsewhere
tpb.addArgument(TpbItems.isc_tpb_concurrency);
tpb.addArgument(TpbItems.isc_tpb_write);
tpb.addArgument(TpbItems.isc_tpb_at_snapshot_number, snapshotNumber);
tpb.addArgument(TpbItems.isc_tpb_wait);
tpb.addArgument(TpbItems.isc_tpb_lock_timeout, 15);
```

If you use Jaybird 4 or earlier (which you shouldn't ;), then you can use

```
tpb.addArgument(23, snapshotNumber);
```

23 is the value of isc_tpb_at_snapshot_number. In Jaybird 4 and earlier,
the other constants are defined in ISCConstants (TpbItems doesn't exist
yet).

Option 2:

Use Jaybird 6, and add connection property allowTxStmts=true and execute
SET TRANSACTION with auto-commit disabled and no active transaction. If
you do have an active transaction, you must call Connection#commit()
before executing SET TRANSACTION.

See also
https://firebirdsql.github.io/jaybird-manual/jaybird_manual.html#ref-allowtxstmts

For example:

```
long snapshotNumber = ... // obtained elsewhere
try (var connection =
DriverManager.getConnection("jdbc:firebird:///employee?allowTxStmts=true",
"sysdba", "masterkey";
var stmt = connection.createStatement()) {
connection.setAutoCommit(false);

// Start a transaction with the desired snapshot
stmt.execute("set transaction snapshot at number " + snapshotNumber);

// do stuff

// commit as normal
connection.commit();
}
```

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 16-03-2026 15:20, 'Mark Rotteveel' via firebird-java wrote:
> try (var connection = DriverManager.getConnection("jdbc:firebird:///
> employee?allowTxStmts=true", "sysdba", "masterkey";

That should have been:

```

try (var connection =
DriverManager.getConnection("jdbc:firebird:///employee?allowTxStmts=true",

"sysdba", "masterkey");
```
--
Mark Rotteveel

[amolna...@gmail.com]

Tried it, but it seems it does not working.

java.sql.SQLException: Transaction's base snapshot number does not exist [SQLState:0B000, ISC error code:335545252]
at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:618)
at org.firebirdsql.gds.ng.FbExceptionBuilder.toFlatSQLException(FbExceptionBuilder.java:384)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:154)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:99)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.processOperation(AbstractWireOperations.java:204)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readOperationResponse(AbstractWireOperations.java:175)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readResponse(AbstractWireOperations.java:169)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readGenericResponse(AbstractWireOperations.java:298)
at org.firebirdsql.gds.ng.wire.AbstractFbWireDatabase.readGenericResponse(AbstractFbWireDatabase.java:212)
at org.firebirdsql.gds.ng.wire.version10.V10Database.startTransaction(V10Database.java:287)
at org.firebirdsql.gds.ng.wire.version10.V10Database.startTransaction(V10Database.java:52)
at org.firebirdsql.gds.impl.GDSHelper.startTransaction(GDSHelper.java:156)
at org.firebirdsql.jaybird.xca.FBManagedConnection.findIscTrHandle(FBManagedConnection.java:1072)
at org.firebirdsql.jaybird.xca.FBManagedConnection.internalStart(FBManagedConnection.java:976)
at org.firebirdsql.jaybird.xca.FBLocalTransaction.begin(FBLocalTransaction.java:74)
at org.firebirdsql.jdbc.InternalTransactionCoordinator$AbstractTransactionCoordinator.ensureTransaction(InternalTransactionCoordinator.java:270)
at org.firebirdsql.jdbc.InternalTransactionCoordinator$LocalTransactionCoordinator.executionStarted(InternalTransactionCoordinator.java:448)
at org.firebirdsql.jdbc.InternalTransactionCoordinator.executionStarted(InternalTransactionCoordinator.java:112)
at org.firebirdsql.jdbc.FBStatement.notifyStatementStarted(FBStatement.java:245)
at org.firebirdsql.jdbc.FBStatement.notifyStatementStarted(FBStatement.java:238)
at org.firebirdsql.jdbc.FBStatement.executeQuery(FBStatement.java:224)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:319)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:319)
at hu.libra.db.firebird.Playground.snapshotAt(Playground.java:305)
at java.lang.reflect.Method.invoke(Method.java:498)
at java.util.ArrayList.forEach(ArrayList.java:1259)
at java.util.ArrayList.forEach(ArrayList.java:1259)
Caused by: org.firebirdsql.jdbc.FBSQLExceptionInfo: Transaction's base snapshot number does not exist

private void setTransaction(
Connection conn,
boolean readOnly,
boolean snapshot,
int lockTimeout,
Long transactionId)
throws SQLException {

conn.setAutoCommit(false);
conn.setReadOnly(readOnly);
conn.setTransactionIsolation(snapshot ? TRANSACTION_SERIALIZABLE : TRANSACTION_READ_COMMITTED);
@SuppressWarnings("resource")
FirebirdConnection fc = conn.unwrap(FirebirdConnection.class);
TransactionParameterBuffer tpb = fc.createTransactionParameterBuffer();
if (readOnly) {
tpb.addArgument(TpbItems.isc_tpb_read);
}
else {
tpb.addArgument(TpbItems.isc_tpb_write);
}
if (snapshot) {
tpb.addArgument(TpbItems.isc_tpb_concurrency);
}
else {
tpb.addArgument(TpbItems.isc_tpb_read_committed);
}
if (!snapshot) {
tpb.addArgument(TpbItems.isc_tpb_rec_version);
}
if (lockTimeout > 0) {
tpb.addArgument(TpbItems.isc_tpb_wait);
tpb.addArgument(TpbItems.isc_tpb_lock_timeout, lockTimeout);
}
else {
tpb.addArgument(TpbItems.isc_tpb_nowait);
}
if (snapshot && (transactionId != null)) {
tpb.addArgument(TpbItems.isc_tpb_at_snapshot_number, transactionId);
}
fc.setTransactionParameters(tpb);
}

@Test
public void snapshotAt()
throws SQLException {

//@formatter:off
try(BasicDataSource ds = new BasicDataSource() {{
setDefaultAutoCommit(false);
setRollbackOnReturn(true);
setDriverClassName(org.firebirdsql.jdbc.FBDriver.class.getName());
setUrl("jdbc:firebirdsql:172.16.173.71/3055:t:\\AMOLNAR_FB50.gdb3s?charSet=UTF-8&isc_dpb_sql_dialect=1&authPlugins=Legacy_Auth");
setUsername("SYSDBA");
setPassword("masterkey");
}};
Connection c1 = ds.getConnection();
Connection c2 = ds.getConnection()) {
//@formatter:on
setTransaction(c1, false, true, 0, null);
long txid;
try(ResultSet rs = c1.createStatement().executeQuery("SELECT CURRENT_TRANSACTION FROM rdb$database")) {
if (!rs.next())
throw new RuntimeException("Nope");
txid = rs.getLong(1);
}
setTransaction(c2, false, true, 0, txid);
try(ResultSet rs = c2.createStatement().executeQuery("SELECT 1 FROM rdb$database")) {
if (!rs.next())
throw new RuntimeException("Nope");
System.out.println(rs.getString(1));
}
}
}

[Mark Rotteveel]

On 17-03-2026 12:50, amolna...@gmail.com wrote:
> Tried it, but it seems it does not working.
>
> java.sql.SQLException: Transaction's base snapshot number does not exist
> [SQLState:0B000, ISC error code:335545252]

[..]

>
> private void setTransaction(
> Connection conn,
> boolean readOnly,
> boolean snapshot,
> int lockTimeout,
> Long transactionId)
> throws SQLException {

[..]

> if (snapshot && (transactionId != null)) {
> tpb.addArgument(TpbItems.isc_tpb_at_snapshot_number, transactionId);
> }
> fc.setTransactionParameters(tpb);
> }
>
> @Test
> public void snapshotAt()
> throws SQLException {

[..]

> setTransaction(c1, false, true, 0, null);
> long txid;
> try(ResultSet rs = c1.createStatement().executeQuery("SELECT
> CURRENT_TRANSACTION FROM rdb$database")) {
> if (!rs.next())
> throw new RuntimeException("Nope");
> txid = rs.getLong(1);
> }
> setTransaction(c2, false, true, 0, txid);

You need to pass the snapshot CN, which is *not* the transaction id.

As documented in
https://firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb40-dml-set-tran-snapshot
and in
https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref50/firebird-50-language-reference.html#fblangref50-transacs-settransac,
you need to use:

```
select RDB$GET_CONTEXT('SYSTEM', 'SNAPSHOT_NUMBER') from RDB$DATABASE
```

Mark
--
Mark Rotteveel

[Mark Rotteveel]

A further remarks inline below.

On 17-03-2026 12:50, amolna...@gmail.com wrote:

> Tried it, but it seems it does not working.

> private void setTransaction(
[..]
> conn.setTransactionIsolation(snapshot ? TRANSACTION_SERIALIZABLE :
> TRANSACTION_READ_COMMITTED);


With the default configuration of Jaybird, TRANSACTION_SERIALIZABLE is
SNAPSHOT TABLE STABILITY, not SNAPSHOT. Also, setting it is entirely
unnecessary, as you're passing a custom transaction configuration anyway.

Mark
--
Mark Rotteveel

[amolna...@gmail.com]

My bad, did not read the documentation fully. 

It works now.

Thank You!