Working with XA transactions using ShardingSphere + Firebird

### Which version of ShardingSphere did you use?
5.5.2
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC

------

**Hello everyone!**
While working with XA transactions and the Firebird database, I encountered a problem that I don’t yet know how to solve. Perhaps someone has some ideas? This issue may not be directly related to ShardingSphere's code but rather to its interaction with Firebird.

### Problem Description
To begin with, we create a connection. Example code:
```Java
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
Statement statement = null;
try {
statement = connection.createStatement();
```
When initializing a connection, ShardingSphere retrieves metadata for each table in the database. In Firebird, the `FBStandAloneConnectionManager` ([GitHub link](https://github.com/FirebirdSQL/jaybird/blob/master/src/main/org/firebirdsql/jaybird/xca/FBStandAloneConnectionManager.java)) is responsible for regular connections and transaction execution. In this class, there is a variable `mc.setManagedEnvironment(false);` that changes during operation. When we retrieve metadata, this variable is modified.

Then, we execute the required query:
```Java
String sql1 = "INSERT INTO T_ORDER (USER_ID, ORDER_TYPE, ADDRESS_ID, STATUS) VALUES (123, 1, 456, 'PENDING')";
String sql2 = "INSERT INTO T_ORDER (USER_ID, ORDER_TYPE, ADDRESS_ID, STATUS) VALUES (789, 2, 101, 'PROCESSING')";
Connection connection = null;

try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
Statement statement = null;
try {
statement = connection.createStatement();
statement.executeUpdate(sql1);
statement.executeUpdate(sql2);
```
Typically, for XA transactions in Firebird, the `XAConnectionManager` ([GitHub link](https://github.com/FirebirdSQL/jaybird/blob/master/src/main/org/firebirdsql/ds/FBXADataSource.java)) is used, where the `ManagedEnvironment` variable is set to true:
```Java
@Override
public FirebirdConnection allocateConnection(FBManagedConnectionFactory mcf,
FBConnectionRequestInfo cxRequestInfo) throws SQLException {
FBManagedConnection mc = mcf.createManagedConnection(cxRequestInfo);
mc.setManagedEnvironment(true);
mc.addConnectionEventListener(this);
return mc.getConnection();
}
```
However, since we use transaction managers like `Atomikos or Narayana` in our configuration, we don’t go through the `XAConnectionManager` (in `Atomikos`, `UserTransactionManager` is used). As a result, the `ManagedEnvironment` variable is not updated to true and remains false.

### Resulting Error
This leads to the following issue: when attempting to execute an XA transaction, we receive the error:
`Connection enlisted in distributed transaction`. This occurs because the `InternalTransactionCoordinator` ([GitHub link](https://github.com/FirebirdSQL/jaybird/blob/master/src/main/org/firebirdsql/jdbc/InternalTransactionCoordinator.java)) performs a check:
```Java
void setTransactionCoordinator(boolean managedConnection, boolean autoCommit) throws SQLException {
FBManagedConnection mc = connection.getManagedConnection();
InternalTransactionCoordinator.AbstractTransactionCoordinator coordinator;
if (managedConnection && mc.inDistributedTransaction()) {
coordinator = new ManagedTransactionCoordinator(connection);
} else if (autoCommit) {
if (mc.inDistributedTransaction()) {
throw new SQLException("Connection enlisted in distributed transaction",
SQLStateConstants.SQL_STATE_INVALID_TX_STATE);
}
```
Since we are using a `inDistributedTransaction` but `managedConnection` is false, the error is thrown. I modified the variable during runtime while debugging, and the program worked correctly.

----
**_At the moment, we are facing a problem with executing XA transactions. I would greatly appreciate it if someone could provide any information or insights on how to resolve this issue. It’s possible that I lack the necessary information, and perhaps there is a way to configure the manager or set the required conditions elsewhere in the configuration._**

----

Config.yaml:
```yaml
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

mode:
type: Standalone
repository:
type: JDBC
# props:
# path: demo

dataSources:
# ds_1:
# dataSourceClassName: com.zaxxer.hikari.HikariDataSource
# driverClassName: com.mysql.cj.jdbc.Driver
# jdbcUrl: jdbc:mysql://localhost:3306/SHARD5?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&logger=com.mysql.cj.log.Slf4JLogger&profileSQL=true
# username: root
# password: password
# maxPoolSize: 10
ds_0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: org.firebirdsql.jdbc.FBDriver
jdbcUrl: jdbc:firebirdsql://localhost:3050/D:/sharding/Databases/SHARD1.FDB?useManagedConnection=true&log_level=4&trace_file=D:/sharding/logs/shard1_trace.log
username: sysdba
password: masterkey
maxPoolSize: 10
ds_1:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: org.firebirdsql.jdbc.FBDriver
jdbcUrl: jdbc:firebirdsql://localhost:3050/D:/sharding/Databases/SHARD2.FDB?useManagedConnection=true&log_level=4&trace_file=D:/sharding/logs/shard2_trace.log
username: sysdba
password: masterkey
maxPoolSize: 10
# ds_2:
# dataSourceClassName: com.zaxxer.hikari.HikariDataSource
# driverClassName: org.firebirdsql.jdbc.FBDriver
# jdbcUrl: jdbc:firebirdsql://localhost:3050/D:/sharding/Databases/SHARD3.FDB
# username: sysdba
# password: masterkey
# maxPoolSize: 10
# ds_3:
# dataSourceClassName: com.zaxxer.hikari.HikariDataSource
# driverClassName: org.firebirdsql.jdbc.FBDriver
# jdbcUrl: jdbc:firebirdsql://localhost:3050/D:/sharding/Databases/SHARD4.FDB
# username: sysdba
# password: masterkey
# maxPoolSize: 10

rules:
- !SHARDING
tables:
T_ORDER:
actualDataNodes: ds_$->{0..1}.T_ORDER_$->{0..1}
tableStrategy:
standard:
shardingColumn: ORDER_ID
shardingAlgorithmName: T_ORDER_inline
keyGenerateStrategy:
column: ORDER_ID
keyGeneratorName: snowflake_generator
defaultDatabaseStrategy:
standard:
shardingColumn: USER_ID
shardingAlgorithmName: database_inline

shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${USER_ID % 2}

T_ORDER_inline:
type: INLINE
props:
algorithm-expression: T_ORDER_$->{ORDER_ID % 2}
keyGenerators:
snowflake_generator:
type: SNOWFLAKE

transaction:
# defaultType: LOCAL
defaultType: XA
providerType: Atomikos
# props:
# transactionTimeout: 10

props:
sql-show: true
```

### You can get the sample code from: https://github.com/apache/shardingsphere/tree/master/examples/shardingsphere-jdbc-example-generator, when using the command: ```./mvnw -B clean install -f examples/shardingsphere-jdbc-example-generator/pom.xml -Pexample-generator -Dmodes=standalone -Dtransactions=xa-atomikos -Dfeatures=sharding -Dframeworks=spring-boot-starter-jdbc```

The code for the test:
```Java
public void DoubleInsertTestWithoutAutoCommit() throws SQLException {
String sql1 = "INSERT INTO T_ORDER (USER_ID, ORDER_TYPE, ADDRESS_ID, STATUS) VALUES (123, 1, 456, 'PENDING')";
String sql2 = "INSERT INTO T_ORDER (USER_ID, ORDER_TYPE, ADDRESS_ID, STATUS) VALUES (789, 2, 101, 'PROCESSING')";
Connection connection = null;

try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
Statement statement = null;
try {
statement = connection.createStatement();
statement.executeUpdate(sql1);
statement.executeUpdate(sql2);
} catch (SQLException e) {
throw new RuntimeException(e);
}
connection.commit();
} catch (SQLException e) {
if (connection != null) {
connection.rollback();
}
throw e;
} finally {
if (connection != null) {
try {
connection.setAutoCommit(true);
connection.close();
} catch (SQLException e) {
LOGGER.error("Failed to close connection", e);
}
}
}
}
```

***P.S I analyzed how connections work in other databases, such as MySQL and PostgreSQL. These databases use a more universal approach to connections, without relying on specialized connection managers (like `FBStandAloneConnectionManager`) for regular operations. In contrast, Firebird has a structure where connection managers play a key role, which creates integration issues with ShardingSphere. I understand that the problem lies in Firebird's architecture, which does not work well with ShardingSphere. However, I currently do not know how to solve this issue without modifying the code of either Atomikos or Firebird.***

Reproduce the failure with the generated XA Atomikos example and the provided DoubleInsertTestWithoutAutoCommit test. Start by tracing the cited Firebird FBStandAloneConnectionManager, FBXADataSource, and InternalTransactionCoordinator interactions with the ShardingSphere-JDBC configuration. Done requires identifying a supported configuration or a specific integration change that prevents the distributed-transaction error.

Written by the indexing model from the issue text.