Drizzle Team•2y ago

many-to-one transaction

Hello, I want to create transaction involving arbitrary number of inserts. For example, Artist (one table) and insert multiple Titles (another table). Not clear to me how to best accomplish based example provided:

const results = await conn.transaction(async (tx) => {
  const whenBranch = await tx.execute('INSERT INTO branches (database_id, name) VALUES (?, ?)', [42, "planetscale"])
  const whenCounter = await tx.execute('INSERT INTO slotted_counters(record_type, record_id, slot, count) VALUES (?, ?, RAND() * 100, 1) ON DUPLICATE KEY UPDATE count = count + 1', ['branch_count', 42])
  return [whenBranch, whenCounter]
})

const results = await conn.transaction(async (tx) => {
  const whenBranch = await tx.execute('INSERT INTO branches (database_id, name) VALUES (?, ?)', [42, "planetscale"])
  const whenCounter = await tx.execute('INSERT INTO slotted_counters(record_type, record_id, slot, count) VALUES (?, ?, RAND() * 100, 1) ON DUPLICATE KEY UPDATE count = count + 1', ['branch_count', 42])
  return [whenBranch, whenCounter]
})

Also, related, is there a way for me to include non db transactions as part of transaction, such that if exception thrown say, for example, copy object to S3 bucket fails, transaction also rolls back? Thanks!

4 Replies

hotshoeOP•2y ago

Never mind on first part -- found I can simply pass array into values for matching type. Too easy :0) Still curious about if a way I can include non-db calls into transaction.

Luxaritas•2y ago

You could call tx.rollback() if a non-db call fails (Which would abort/rollback the DB transaction)

Luxaritas•2y ago

However be wary about the semantics there - when working with multiple systems, you wind up with CAP theory tradeoffs. There's a pretty good talk discussing it if you're interested: https://www.youtube.com/watch?v=qhrd3vq5jkY Though I don't necessarily fully agree with the conclusions of that talk: https://twitter.com/luxaritas/status/1475297902067167236?s=20

NDC Conferences

YouTube

Eliminating Hidden Dangers to Your Applications Patterns for Relia...

There is a danger lurking in every system that hasn't taken explicit steps to eliminate it. This under-recognized data killer can cause inconsistencies in your system which can be as difficult to find as they are to correct. Fortunately, recognizing this monster is easy, and there are a number of great techniques you can use to keep it away for ...

Jonathan Romano (@luxaritas)

@bsstahl Just watched your NDC talk about avoiding dual writes - great content, but I'm left with a question: How does splitting tasks into different execution contexts as you described resolve/reduce issues encountered with dual writes?

Twitter

hotshoeOP•2y ago

Thanks @Luxaritas , much appreciate the suggestion and pointer. Will take a closer look.

Gaming

Programming

many-to-one transaction