Is there a default query caching on RDS Proxy for MySQL databases ?

Hi cramarokoto,

Please go through the below steps once i hope it will helps to resolve your issue.

Check Read Replica Configuration:

• Verify if read replicas are being used and whether the proxy is routing queries to them. You can configure the RDS Proxy to use only the primary instance for read and write operations if consistency is critical.

Review Proxy Settings:

• Ensure that the RDS Proxy settings do not have any configurations that might introduce caching. Although RDS Proxy itself does not cache query results, it might interact with other layers that do.

Verify Transaction Isolation Levels:

• Ensure that your transaction isolation levels are set correctly. You can check and set the isolation level in your queries or application settings.

-- Check current isolation level
SELECT @@transaction_isolation;

-- Set isolation level to READ COMMITTED (example)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Use Consistent Read Settings:

When performing read operations that require the latest data, use the FOR UPDATE or LOCK IN SHARE MODE clauses to ensure you get the most recent data.

-- Example of a consistent read with FOR UPDATE
SELECT * FROM your_table WHERE condition FOR UPDATE;

Investigate Query Execution Plans:

• Compare the execution plans of queries run directly on the database versus through the proxy. Differences in execution plans might indicate why results differ.

Disable or Adjust Connection Pooling:

• If necessary, adjust the connection pooling settings in RDS Proxy to ensure that transactions are committed and visible immediately across all connections.

Example Configuration Adjustments

Disabling Read Replicas for Consistent Reads:

In the RDS Proxy configuration, you can specify that all queries go to the primary instance to ensure you get the most up-to-date data.

{
    "DBProxyEndpoint": "your-proxy-endpoint",
    "DBProxyTargetGroup": {
        "TargetGroupName": "default",
        "TargetGroupArn": "arn:aws:rds:us-west-2:123456789012:targetgroup:mydbproxy:default",
        "ReadOnly": false
    }
}

Setting Isolation Level in Lambda Function:

• When connecting to the database from a Lambda function, set the appropriate isolation level at the start of each connection session.

import pymysql

connection = pymysql.connect(
    host='your-db-host',
    user='your-username',
    password='your-password',
    db='your-database',
    autocommit=True
)

with connection.cursor() as cursor:
    cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;")
    cursor.execute("SELECT * FROM your_table WHERE condition;")
    result = cursor.fetchall()
    print(result)