ShardingProxy Base transaction mode, "Select for update" execution exception

[lklogs]

current environment

• ShardingProxy + Base
• Sharding Proxy：5.3.2
• Seata：1.6.1
• PostgreSQL：13.2

Steps to reproduce

Open a transaction, execute the select for update statement, and ERROR: portal "C_n" does not exist appears. The steps to reproduce are as follows:

sharding_db=> begin;
BEGIN
sharding_db=*> select * from ticket where id=1 for update;
ERROR:  portal "C_4" does not exist
sharding_db=!> rollback;

Exception Details

[INFO ] 2023-06-28 18:25:29.725 [Connection-1-ThreadExecutor] i.s.tm.api.DefaultGlobalTransaction - Begin new global transaction [192.168.2.73:8091:5215575959221506544]
[INFO ] 2023-06-28 18:25:32.116 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select * from ticket where id=1 for update
[INFO ] 2023-06-28 18:25:32.116 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from ticket where id=1 for update
[ERROR] 2023-06-28 18:25:32.153 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.postgresql.util.PSQLException: ERROR: portal "C_2" does not exist
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
	at org.postgresql.core.v3.QueryExecutorImpl.fetch(QueryExecutorImpl.java:2562)
	at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:2145)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
	at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.type.stream.JDBCStreamQueryResult.next(JDBCStreamQueryResult.java:53)
	at org.apache.shardingsphere.sharding.merge.common.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
	at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.next(DatabaseConnector.java:416)
	at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.simple.PostgreSQLComQueryExecutor.next(PostgreSQLComQueryExecutor.java:122)
	at org.apache.shardingsphere.proxy.frontend.postgresql.command.PostgreSQLCommandExecuteEngine.writeDataPackets(PostgreSQLCommandExecuteEngine.java:107)
	at org.apache.shardingsphere.proxy.frontend.postgresql.command.PostgreSQLCommandExecuteEngine.processSimpleQuery(PostgreSQLCommandExecuteEngine.java:94)
	at org.apache.shardingsphere.proxy.frontend.postgresql.command.PostgreSQLCommandExecuteEngine.writeQueryData(PostgreSQLCommandExecuteEngine.java:85)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java:120)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:109)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)
[INFO ] 2023-06-28 18:25:34.270 [Connection-1-ThreadExecutor] i.s.tm.api.DefaultGlobalTransaction - Suspending current transaction, xid = 192.168.2.73:8091:5215575959221506544
[INFO ] 2023-06-28 18:25:34.271 [Connection-1-ThreadExecutor] i.s.tm.api.DefaultGlobalTransaction - [192.168.2.73:8091:5215575959221506544] rollback status: Rollbacked

Comparison with other modes

Normal performance in LOCAL mode and Atomikos XA mode

[linghengqian]

• I confirmed in Bump the minimum Seata Client version for Seata AT integration to 2.2.0 #33872 that this is a problem with the Alibaba Druid version used by Seata Client being too low. Seata Client uses Alibaba Druid for database dialect parsing.