You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "makssent (via GitHub)" <gi...@apache.org> on 2026/10/19 13:09:29 UTC
[I] Firebird Proxy writes data in a read-only transaction and ignores NO WAIT and LOCK TIMEOUT [shardingsphere]
makssent opened a new issue, #40248:
URL: https://github.com/apache/shardingsphere/issues/40248
### Before reporting a bug
- [x] I have searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- [x] I have read the [ShardingSphere documentation](https://shardingsphere.apache.org/document/current/en/overview).
### ShardingSphere version
master, commit [`01446d4fad4`](https://github.com/apache/shardingsphere/commit/01446d4fad48450e7fc3eb3b3631b25d6bf411df)
### ShardingSphere project
ShardingSphere-Proxy
### Expected behavior
The proxy should apply the transaction parameters [`isc_tpb_read`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3137-L3161), [`isc_tpb_nowait`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3111-L3135) and [`isc_tpb_lock_timeout`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3320-L3383) of [`op_transaction`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5147-L5148) as the Firebird server does.
### Actual behavior
The proxy does not apply these parameters. It writes in a read-only transaction, and an update of a locked row waits until the lock is released.
The same steps were executed directly on the Firebird server and through the proxy, and the rows were read directly from the server:
| Steps | Firebird server | Proxy |
| --- | --- | --- |
| [`setReadOnly(true)`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBConnection.java#L601-L613) ([`isc_tpb_read`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3137-L3161)), insert a row with ID = 8951, commit | Error [`isc_read_only_trans`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/rlck.cpp#L70-L73) "attempted update during read-only transaction", the commit succeeds, the row is not saved | Success, the commit succeeds, the row is saved |
| Another transaction, directly on the Firebird server, updates the row with ID = 8953 and does not commit. A transaction with [`isc_tpb_nowait`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3111-L3135) updates the same row | Error [`isc_lock_conflict`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/lock/lock.cpp#L552-L553) "lock conflict on no wait transaction; update conflicts with concurrent update" | No answer within 5 seconds. After the other transaction rolls back, success |
| The same with [`isc_tpb_wait`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3018-L3033) and [`isc_tpb_lock_timeout`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3320-L3383) 1 instead of [`isc_tpb_nowait`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/tra.cpp#L3111-L3135) | Error [`isc_lock_timeout`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/lock/lock.cpp#L552-L553) "lock time-out on wait transaction; update conflicts with concurrent update" | No answer within 5 seconds. After the other transaction rolls back, success |
So through the proxy an application saves rows in a read-only transaction, and its updates of a locked row hang until the lock is released, while the Firebird server answers them with an error.
### Reason analysis
- [`FirebirdStartTransactionPacket`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/transaction/FirebirdStartTransactionPacket.java#L31) reads from the TPB only [`isAutoCommit`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/transaction/FirebirdStartTransactionPacket.java#L48-L50), [`isReadOnly`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/transaction/FirebirdStartTransactionPacket.java#L57-L59) and [`getIsolationLevel`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d
6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/transaction/FirebirdStartTransactionPacket.java#L66-L77). The items [`WAIT`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/constant/buffer/type/FirebirdTransactionParameterBufferType.java#L42), [`NOWAIT`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/constant/buffer/type/FirebirdTransactionParameterBufferType.java#L43) and [`LOCK_TIMEOUT`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/constant/buffer/type/FirebirdTransactionParamete
rBufferType.java#L57) are parsed, but nothing reads them.
- [`FirebirdStartTransactionCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/transaction/FirebirdStartTransactionCommandExecutor.java#L44-L51) puts only [auto-commit, read-only and the isolation level](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/transaction/FirebirdStartTransactionCommandExecutor.java#L46-L48) into [`ConnectionSession`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/session/ConnectionSession.java#L44), so the wait mode and the lock timeout never reach the transaction on the Firebird server.
- [`ProxyDatabaseConnectionManager#replayTransactionOptions`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/connector/ProxyDatabaseConnectionManager.java#L227-L235) calls [`setReadOnly(true)`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBConnection.java#L568-L578) and then [`setTransactionIsolation`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBConnection.java#L611-L619) on the connection to the Firebird server. [`getIsolationLevel`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/transaction/FirebirdStartTransactionPacket.java#L76) always returns a level, so the isolation level is always set. Jaybird, which the proxy uses for this c
onnection, replaces the whole TPB in [`FBManagedConnection#setTransactionIsolation`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jaybird/xca/FBManagedConnection.java#L1122-L1130), and the read-only flag that [`FBManagedConnection#setReadOnly`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jaybird/xca/FBManagedConnection.java#L1147-L1149) has set is lost.
### Steps to reproduce
#### Environment
- Firebird server 5.0.
- Client: Jaybird 6.0.7.
#### Steps
1. Create the table on the Firebird server:
```sql
CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20));
```
2. Start ShardingSphere-Proxy with this configuration:
```yaml
# global.yaml
authority:
users:
- user: root@%
password: root
privilege:
type: ALL_PERMITTED
props:
proxy-frontend-database-protocol-type: Firebird
```
```yaml
# database-firebird.yaml
databaseName: firebird_db
dataSources:
ds_0:
url: jdbc:firebirdsql://<host>/<database file>
username: <user>
password: <password>
rules:
- !SINGLE
tables:
- "*.*"
```
3. Execute the steps from the table under "Actual behavior" through the proxy and directly on the Firebird server, and compare the results. "Example code" runs all of them.
### Example code
All cases on Jaybird 6.0.7, one method per case. The comment above each method shows what it prints against the Firebird server and through the proxy.
```java
import org.firebirdsql.gds.TransactionParameterBuffer;
import org.firebirdsql.jaybird.fb.constants.TpbItems;
import org.firebirdsql.jdbc.FirebirdConnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;
public class TransactionParameters {
// The proxy; put the URL of the Firebird server here to compare
static final String URL = "jdbc:firebirdsql://<proxy host>:<proxy port>/firebird_db?encoding=UTF8&wireCrypt=DISABLED";
static final String USER = "root";
static final String PASSWORD = "root";
// The Firebird server, to lock the row and to read the rows directly
static final String SERVER_URL = "jdbc:firebirdsql://<host>/<database file>";
static final String SERVER_USER = "<user>";
static final String SERVER_PASSWORD = "<password>";
public static void main(final String[] args) throws Exception {
executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 8951 AND 8953");
executeOnServer("INSERT INTO T_PLAIN (ID, NAME) VALUES (8953, 'locked')");
readOnly();
noWait();
lockTimeout();
executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 8951 AND 8953");
}
// Firebird server: "READ ONLY, insert: attempted update during read-only transaction ...", "READ ONLY, commit: success",
// "ID = 8951: the row is lost";
// proxy: "READ ONLY, insert: success", "READ ONLY, commit: success", "ID = 8951: the row is saved"
static void readOnly() throws SQLException {
try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
connection.setAutoCommit(false);
// Jaybird sends isc_tpb_read in the TPB of op_transaction
connection.setReadOnly(true);
System.out.println("READ ONLY, insert: " + execute(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (8951, 'read only')"));
connection.commit();
System.out.println("READ ONLY, commit: success");
}
printRow(8951);
}
// Firebird server: "NO WAIT, update: lock conflict on no wait transaction; update conflicts with concurrent update; ...";
// proxy: "NO WAIT, update: no answer within 5 seconds", "NO WAIT, update after the other transaction rolled back: success"
static void noWait() throws Exception {
try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
TransactionParameterBuffer tpb = tpb(connection);
tpb.addArgument(TpbItems.isc_tpb_nowait);
updateLockedRow("NO WAIT", connection, tpb);
}
}
// Firebird server: "LOCK TIMEOUT 1, update: lock time-out on wait transaction; update conflicts with concurrent update; ...";
// proxy: "LOCK TIMEOUT 1, update: no answer within 5 seconds", "LOCK TIMEOUT 1, update after the other transaction rolled back: success"
static void lockTimeout() throws Exception {
try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
TransactionParameterBuffer tpb = tpb(connection);
tpb.addArgument(TpbItems.isc_tpb_wait);
tpb.addArgument(TpbItems.isc_tpb_lock_timeout, 1);
updateLockedRow("LOCK TIMEOUT 1", connection, tpb);
}
}
// A transaction with read committed, record version and read-write; the case adds its wait mode
static TransactionParameterBuffer tpb(final Connection connection) throws SQLException {
TransactionParameterBuffer result = connection.unwrap(FirebirdConnection.class).createTransactionParameterBuffer();
result.addArgument(TpbItems.isc_tpb_read_committed);
result.addArgument(TpbItems.isc_tpb_rec_version);
result.addArgument(TpbItems.isc_tpb_write);
return result;
}
// Another transaction, directly on the Firebird server, updates the row with ID = 8953 and does not commit.
// Then the connection under test updates the same row in a transaction with the given TPB.
static void updateLockedRow(final String name, final Connection connection, final TransactionParameterBuffer tpb) throws Exception {
ExecutorService executor = Executors.newSingleThreadExecutor();
try (Connection holder = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD)) {
holder.setAutoCommit(false);
execute(holder, "UPDATE T_PLAIN SET NAME = 'holder' WHERE ID = 8953");
connection.setAutoCommit(false);
connection.unwrap(FirebirdConnection.class).setTransactionParameters(tpb);
Future<String> update = executor.submit(() -> execute(connection, "UPDATE T_PLAIN SET NAME = 'second' WHERE ID = 8953"));
try {
System.out.println(name + ", update: " + update.get(5, TimeUnit.SECONDS));
holder.rollback();
} catch (final TimeoutException ex) {
System.out.println(name + ", update: no answer within 5 seconds");
// Releases the lock, so that the waiting update can go on
holder.rollback();
System.out.println(name + ", update after the other transaction rolled back: " + update.get(10, TimeUnit.SECONDS));
}
connection.rollback();
} finally {
executor.shutdownNow();
}
}
static String execute(final Connection connection, final String sql) {
try (Statement statement = connection.createStatement()) {
statement.executeUpdate(sql);
return "success";
} catch (final SQLException ex) {
return ex.getMessage();
}
}
// Reads the row directly on the Firebird server
static void printRow(final int id) throws SQLException {
try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD);
ResultSet resultSet = connection.createStatement().executeQuery("SELECT NAME FROM T_PLAIN WHERE ID = " + id)) {
System.out.println("ID = " + id + ": " + (resultSet.next() ? "the row is saved" : "the row is lost"));
}
}
static void executeOnServer(final String sql) throws SQLException {
try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD)) {
connection.createStatement().execute(sql);
}
}
}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org