Prisma1: Transactions for multiple mutations

[marktani]

TLDR: Have a way to run multiple mutations, if one of them fails, all is rolled back

Longer version:
The Prisma client API should provide a way to send multiple mutations within the same HTTP request. The result of the request can be one of two cases:

• All mutations succeeded
• No mutation succeeded

Here is what a possible API for this feature could look like:

const mut1 = prisma.createUser()
const mut2 = prisma.updateOrder()

await prisma.transaction([mut1, mut2])

Note that this feature is not the same as long-running transactions where the submitted mutations _depend on each other_.

[thekevinbrown]

For us it's less about the rollback, and more about not exposing partially written mutations to other clients, which transactions would (theoretically, depending on how the connections are handled) give us as well.

[marcusstenbeck]

Sadly, I don't feel comfortable using nested mutations unless they're guarantee that one of two outcomes happen.

a. The nested mutation failed to pass all constraints, and as such will not modify the database.
b. The nested mutation passes all constraints, and is saved entirely to the database.

[danmkent]

I agree that each mutation should be atomic, including it's nested mutations.

It would also be nice to have a mechanism for defining a set of separate mutations as a transaction but I see making nested mutation atomic with their parent mutation as more important.

[marktani]

There are two ways I currently see that would enabled transactional guarantees for mutations.

• many mutations in the same GraphQL document could be marked with a certain directive (for example @transaction(key: "trans-1") (see #53)
• nested mutations should provide transactional guarantees out-of-the-box (see #191)

[sorenbs]

Transactional guarantees for nested mutations are handled in https://github.com/graphcool/graphcool/issues/597

We need a proposal for transactional guarantees for multiple mutations.

[kbrandwijk]

Would it be possible to mark a request with multiple mutations as a transaction. Either in the header, or with an attribute?

Also, transaction support for multiple mutations also has to include defining the lock level on a Type (like transaction support in for example SQL Server). Otherwise it will have too much impact on performance. On the other hand, it's the only way for it to work across multiple clients. I'll write up a more detailed analysis in a separate issue for this.

[omatrot]

Were are we on the transaction support for multiple mutations ? Any progress ?

[sorenbs]

@omatrot Nested mutations are now transactional and atomic in Prisma. You can read more in https://github.com/graphcool/prisma/issues/1280

This issue tracks adding a way to indicate that all mutations in a single request should be run in a transaction. This is still not implemented. In many cases it is possible to rewrite multiple mutations to a single nested mutation to achieve this result

[timBrockman]

Not sure if this is useful as I'm pretty new to this project, but the type of story problem I usually look to solve with transactions are something like this: Player1 purchases a unique item from Player2 for 2 coins without spending coins that don't exist or destroying/duplicating anything.

type Player {
  name: String! @unique
  coins:  Int!
  items: [Item!]!
}

type Item {
  id: ID! @unique
  owner: Player
}

I can almost make this happen with nested mutations by adding another type (Exchange) to provide edges between the players (you could do this with direct edges between players too).

type Player {
  name: String! @unique
  coins:  Int!
  items: [Item!]!
  exchange: Exchange!
}

type Item {
  id: ID! @unique
  owner: Player
}

type Exchange {
  name: String! @unique
  players: [Player!]!
} 

Assuming the players were connected to the same Exchange I could write:

mutation{
  updateExchange(
    where:{name:"bank"}
    data:{
      player:{
        update:[
          {
            where:{
                name:"p2"
            }
            data: {
                totalCoins:4
              items: {
                disconnect:{ name: "belt of partyin' down"}
              }
            }
          },
          {
            where: {
              name: "p1"
            }
            data: {
              totalCoins:1
              items: {
                connect:{ name: "belt of partyin' down"}
              }
            }
            }
        ]
      }
    }
  ){
    name
  }
}

But, if there's still no way of ensuring that the player isn't double spending if the resolver is hit a bunch or whatever. If the where in update could be expanded to include something like an andWhere: [PlayerWhereInput!] it would be solved.
After thinking about this a bit - a work around would be to create additional @unique properties on the player type to ensure the integrity of the state from a preceding query and update it with something like: where: {lastKnownState: "someCUID"}

[cem]

How about SQL-like connection semantics? It might be the only solution that allows the client to atomically: (1) fetch data, (2) process data, (3) write results.

[j-colter]

@marktani How to add a transaction in a resolver function.
eg:
Mutation: {
createDraft: async (_, args, context, info) => {
const draft = await context.prisma.mutation.createDraft(args, info);
// do something …
if (flag) throw new Error(‘create a draft error…’);
return draft;
}
}
What should I do to rollback when something went wrong in this situation, thanks a lot.

[trevorr]

I don't think nested mutations and same-document atomics can handle certain classic transactional use-cases like bank account transactions (conditionally create transaction record and update balance in account record). Perhaps transactions should be a feature/extension of the Prisma bindings API?

Mutation: {
  withdraw(parent, { acctId, amount }, ctx, info) {
    const txn = await ctx.db.newTransaction()
    try {
      const acct = await txn.query.account({ where: { id: acctId } }, '{ balance }')
      if (acct.balance < amount) throw Error('Insufficient funds')
      await txn.mutation.createAccountTransaction(...)
      await txn.mutation.updateAccount({ where: { id: acctId }, data: { balance: acct.balance - amount } })
      await txn.commit()
    } catch (e) {
      await txn.rollback()
      throw e
    }
  }
}

Or better yet, handle the try/catch/commit/rollback within the API by wrapping the user logic in a callback:

Mutation: {
  withdraw(parent, { acctId, amount }, ctx, info) {
    await ctx.db.doInTransaction(txn => {
      const acct = await txn.query.account({ where: { id: acctId } }, '{ balance }')
      if (acct.balance < amount) throw Error('Insufficient funds')
      await txn.mutation.createAccountTransaction(...)
      await txn.mutation.updateAccount({ where: { id: acctId }, data: { balance: acct.balance - amount } })
    })
  }
}

Obviously this would require extending the protocol beyond a standard GraphQL endpoint (and keeping all operations on the same connection), but does this seem to others like what this should look like? I can't envision anyway to capture it directly on the schema (e.g. with annotations).

[kokokenada]

What about using executeRaw() to issue BEGIN; SAVEPOINT A, COMMIT, ROLLBACK ?

[sorenbs]

@kokokenada - that would be a valid solution. Currently executeRaw() does not support multiple statements, but that is a limitation we intend to lift.

[kokokenada]

Thanks @sorenbs - is there something unreliable about issuing a stand alone executeRaw that starts a transaction, issuing separate executeRaw mutations for the actual work, and a seperate Rollback or Commit? I thought I had that scenario working when I tested in a playground instance, but I only tried it once.

[braska]

@kokokenada I just checked suggested workaround. This works fine, but I think there is no guarantee that Prisma will execute BEGIN, mutation request and COMMIT in one connection to Postgres. We need @sorenbs comments about this case.

Note: mutation should be wrapped by executeRaw. If you try to execute BEGIN and than use mutation generated by prisma-binding and than execute ROLLBACK, you mutation will not be rolled back.

[kokokenada]

I tried BEGIN; SAVE POINT etc on some tests that run in parallel in order to lock an read/update sequence and found that it did NOT work. So it seems different connections were used.

[huzaifaali14]

Hey, any update or workaround for this ?

[gustawdaniel]

I wanted to ask about mutation in context of https://github.com/prisma/prisma/issues/3738

Is any plan of introducing it?

[perrosnk]

Any updates on this?