Add intelligence to the AWS JDBC wrapper to Auto-detect and set readOnly=true flag based on query nature(SELECT * from... queries), eliminating mandatory @Transactional(readOnly=true) in Spring applications.

[beingaryan]

Describe the feature

Description

Add intelligence to the AWS JDBC wrapper to automatically set readOnly=true for transactions that only contain SELECT queries, eliminating the need for explicit annotation with @transactional(readOnly=true) in Spring applications.

Problem Statement

Currently, developers must manually annotate methods with @Transactional(readOnly=true) to optimize read-only transactions.
It has below challenges:

• Creates potential for human error if annotations are forgotten
• Increases code verbosity
• Requires developers to understand the performance implications of read-only transactions

Proposed Solution

Implement a wrapperplugin in the AWS JDBC wrapper that:

• Inspects SQL statements before execution
• Automatically sets readOnly=true for connections when only SELECT statements are detected.
• If this plugin is enabled along with readWriteSplitting, it will automatically redirects the query to reader instances, without explicitly setting @Transactional(readOnly=true)
• Provides configuration options to enable/disable this feature.
• aws.jdbc.autoReadOnly.enabled=true

Scenarios

• As there can be below kinds of queries for interacting with SQL databases

1. NATIVE QUERY
2. JPA BASED QUERY

Benefits

• Performance optimization: Read-only transactions can be optimized by the database
• Reduced boilerplate: No need for explicit @transactional(readOnly=true) annotations
• Error prevention: Eliminates human error in forgetting to set read-only flag

Sample code snippet for this
`public class AutoReadOnlyConnectionWrapper implements ConnectionPlugin {
private boolean enabled = false;
private static final Pattern SELECT_PATTERN = Pattern.compile("^\sSELECT\s+.", Pattern.CASE_INSENSITIVE);

@Override
public Connection connect(ConnectionPlugin next, ConnectionPluginContext ctx) throws SQLException {
    Connection connection = next.connect(ctx);
    if (enabled) {
        return new ReadOnlyAwareConnection(connection);
    }
    return connection;
}

private static class ReadOnlyAwareConnection implements Connection {
    private final Connection delegate;
    private boolean containsWriteOperations = false;
    
    // Implementation that monitors statements and sets readOnly appropriately
    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        if (!SELECT_PATTERN.matcher(sql).matches()) {
            containsWriteOperations = true;
            delegate.setReadOnly(false);
        } else if (!containsWriteOperations) {
            delegate.setReadOnly(true);
        }
        return delegate.prepareStatement(sql);
    }
    
    // Other methods delegated to the underlying connection
}

}`

Use Case

• Performance optimization: Read-only transactions can be optimized by the database
• Reduced boilerplate: No need for explicit @transactional(readOnly=true) annotations
• Error prevention: Eliminates human error in forgetting to set read-only flag

Proposed Solution

Implement a wrapperplugin in the AWS JDBC wrapper that:

• Inspects SQL statements before execution
• Automatically sets readOnly=true for connections when only SELECT statements are detected.
• If this plugin is enabled along with readWriteSplitting, it will automatically redirects the query to reader instances, without explicitly setting @Transactional(readOnly=true)
• Provides configuration options to enable/disable this feature.
• aws.jdbc.autoReadOnly.enabled=true

Other Information

No response

Acknowledgements

• I may be able to implement this feature request
• This feature might incur a breaking change

The AWS Advanced JDBC Driver version used

2.5.6

JDK version used

17

Operating System and version

WIN11

[beingaryan]

Hello @aaron-congo ,
Could you please have a check on this feature enhancement ?

If there's anything we can do to help or any further information needed from our side, please let us know!

Thanks,
Aryan Gupta

[aaron-congo]

Hi @beingaryan,

Thank you for taking the time to submit this feature request and for providing such detailed requirements.

Your suggestion happens to be an idea that we have recently been thinking about. The idea is technically possible but has some complications:

• achieving 100% accuracy with auto-detecting writes vs reads is likely not feasible given all the different ways that SQL queries can be executed
• difficulty in handling edge cases when distinguishing between read and write operations, for example, most SELECTs are read-only, but they can be used to call functions that perform write operations
• difficulty in handling SQL strings containing multiple statements
• performance penalties due to the need to parse all incoming SQL strings
• autocommit would have to be set to true, so that auto-switching doesn't occur during a transaction

Our technical team is currently evaluating the feasibility and impact of implementing this feature. However, given the complexity involved and our current development priorities, we cannot make any commitments about if or when this might be implemented. We will continue to keep this on our feature consideration list and will keep it in mind as we plan future development cycles. While we can't promise an implementation timeline, we appreciate you bringing this use case to our attention. Thank you for your understanding.