Description
Thanks for your work on this project @jeremydaly it's really useful :)
I just ran into a bug with throwing an error inside a transaction query, running on aws lambda. While the thrown error does prevent any previous queries from executing (as expected), it doesn't actually roll back. Worse, the previous queries do get executed by a subsequent call to the lambda function!
Minimal example:
import mysql from "serverless-mysql"
const db = mysql({
config: {
host: process.env.DB_HOST,
database: process.env.DB_NAME,
user: process.env.DB_USER,
password: process.env.DB_PASS,
},
})
export default async function handler() {
try {
await db
.transaction()
.query(() => ["INSERT INTO items (created_at) VALUES(?)", [new Date()]])
.query(() => {
throw new Error("Abort transaction")
})
.commit()
console.log("Transaction succeeded")
} catch (e) {
console.error("Transaction failed")
}
await db.end()
}
Suppose this lambda is executed at time t1
. As expected, the catch block gets triggered and nothing is inserted.
Now suppose the lambda is called again at a later time t2
. Again, the catch block gets triggered and the new item is not inserted. However, as a result of the second lambda execution, the original item does get inserted with created_at = t1
.
My short-term fix is to force a SQL error instead of throwing manually:
await db
.transaction()
.query(() => ["INSERT INTO users (created_at) VALUES(?)", [new Date()]])
.query(() => ["SELECT hack_to_abort_transaction"])
.commit()
Tested on MySQL 8.0.20.