I have a database with data that changes fairly quickly, and there's enough data that user's need to paginate. I'm trying to figure out a way to do stable pagination and I'm wondering if there's a way to take advantage of the repeatable read transaction isolation level to give each pagination request a stable snapshot of the data. The API runs on AWS Lambda, so my question is specifically, is it possible to run multiple queries, in separate lambda invocations against the same transaction? Everything I've found so far indicates that transactions must all operate in the same statement like this
START TRANSACTION
...
COMMIT
I would like to do something like
USE TRANSACTION 12345
...
END
Hopefully that gives you an idea of what I'm trying to accomplish even though the syntax is fictitious. If this is not possible, are there any other options built-in to MySQL that would solve this problem? If not, we will implement our own stable snapshot/caching layer.