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!

Answer 2

AWS recommends making a global connection (before your handler function definition) in order to increase performance. Idea is that a new connection does not have to be established and the previous connection to the DB is reused, even when multiple instances of Lambda are run in close connection. But if your use case involves referencing MySQL tables through Lambda, especially if the table is regularly updated, I'd recommend initiating the connection object locally (inside the handler function) and then closing it after you run your queries. This is much in tandem to @dnevins' response and was the only way it worked for me as well. Hope this helps!