Kysely•10mo ago

question about transaction isolation level

I am not sure if the way I do queries with transaction with kysely is the right way. I would be glad if someone could take a look:

try {
    await sql`BEGIN;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`
    .execute(db)

    // some kysely queries

    await sql`COMMIT;`
    .execute(db)

    res.send('ok')
} catch (error) {
    await sql`ROLLBACK;`
    .execute(db)

    next(error)
}

try {
    await sql`BEGIN;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`
    .execute(db)

    // some kysely queries

    await sql`COMMIT;`
    .execute(db)

    res.send('ok')
} catch (error) {
    await sql`ROLLBACK;`
    .execute(db)

    next(error)
}

3 Replies

koskimas•10mo ago

That won't work. Kysely takes a new connection from the pool for each query. What you have will run begin, commit and rollback on separate connections. See the db.transaction method and corresponding documentation. The API docs have a bunch of examples, and there's some in kysely.dev too.

tzezarOP•10mo ago

thanks for the quick reply I've done some reading on docs, and I have one more question: executing raw queries this way is fine, right?

  await db.transaction()
.setIsolationLevel('serializable')
.execute(async tx => {
    await sql`SOMETHING`.execute(tx)
})

  await db.transaction()
.setIsolationLevel('serializable')
.execute(async tx => {
    await sql`SOMETHING`.execute(tx)
})

sorry im not able to format better this code part on discord

koskimas•10mo ago

Yep, that's fine

Gaming

Programming

question about transaction isolation level