Python Aws lambda function not fetching the rds Mysql table value in realtime

Question

I have a AWS lambda implemented using python/pymysql with AWS rds Mysql instance as backend. It connects and works well and I can also access the lambda from my android app. The problem I have is after I insert a value into rds mysql tables successfully using local machine mysql workbench and run the lambda function from AWS console its not showing the newly inserted value instantly. On the python aws lambda code I am not closing the connection or cursor. But if I edit the lambda function on the AWS console, by edit I mean just insert a space and again run the lambda from AWS console it fetches the newly inserted value. How do I configure/code to make lambda fetch db values in realtime.

Answer 1

Many people use the following approach when writing a lambda.

# open connection
conn = pymysql...

def respond:
  return message

def handler(event, context):
  use conn
  respond

This has the effect of reusing the DB connection "conn" if invocations are "close" together where close is defined by AWS. The problem is that this will give you cached reads even if you alter the table. The reason why is that conn doesn't go out of scope and is reused when the lambda is reinvoked. By bringing it into the handler it goes out of scope and is closed each time its invoked. This brings a performance penalty but you get the same data as is in the current state of the DB.

The lambda should be:

def respond:
  return message

def handler(event, context):
  # open connection
  conn = pymysql...
  use conn
  respond

I might be off on the exact reasons but this works for me. BTW, I tried using NO_SQL_CACHE, auto commits, explicit commits. Nothing worked until I got the system to reconnect each time. YMMV!