Drizzle Team•16mo ago

Inconsistent transactions with Drizzle/Planetscale

I'm having issues with updating multiple records in parallel using Promise.all inside a transaction using Planetscale's serverless driver. I've created a minimal repro here: https://github.com/tomqchristensen/drizzle-ps-tx. The gist of it is that trying to update multiple records in parallel using something like this:

const updateParallelTx = async (n: number) =>
  db.transaction(async (tx) => {
    const promises = fooIds.map(async (id) =>
      tx.update(t.foo).set({ n }).where(eq(t.foo.id, id))
    );
    await Promise.all(promises);
  });

const updateParallelTx = async (n: number) =>
  db.transaction(async (tx) => {
    const promises = fooIds.map(async (id) =>
      tx.update(t.foo).set({ n }).where(eq(t.foo.id, id))
    );
    await Promise.all(promises);
  });

results in only a single record being updated and no errors thrown, i.e. the exact opposite to what one would expect from a transaction. Any ideas as to why this is the case? Happy to open an issue on GH if this is indeed a bug.

10 Replies

Angelelz•16mo ago

I'm not sure you can parallelize stuff inside a transaction like this A transaction is meant for serialized steps where a step depends on the success of the previous step, and if one fails, all should fail In your case, you don't need a transaction You could do:

db.update(t.foo).set({n}).where(inArray(t.foo.id, fooIds))

db.update(t.foo).set({n}).where(inArray(t.foo.id, fooIds))

tomchristensenOP•16mo ago

The specific example I've given is contrived and doesn't need to be a transaction, I agree. The real-world use case is that I need to update a list of records with different values, and I need all the updates to either succeed or fail together. In the example, imagine that each foo record belongs to some foo_group and that there's a business rule that dictates that the sum of n for all foo records in a given foo_group must sum up to 5. Now suppose we want to update n for the foo records in some foo_group - to ensure that our business rule remains satisfied, we would perform the updates in a transaction to ensure that all records are either updated together or none are updated.

Angelelz•16mo ago

Then you'd need to run them in a for loop, one after the other inside the trasaction

tomchristensenOP•16mo ago

Right, but that seems like an issue that's unique to using the Planetscale driver. The code in the example works as expected if you swap out the Planetscale driver for mysql2.

Angelelz•16mo ago

Correct, we don't know how planetscale would handle parallel updates like this. Don't they use an http driver?

tomchristensenOP•16mo ago

Yep, it's fetch-based. That's also the motivation for trying to parallelize - to avoid a waterfall of http roundtrips when those requests are independent (at least within the context of the transaction).

Angelelz•16mo ago

Mysql2 connects directly to the DB using MySQL protocal Do they offer any solution for something like this in their documentation?

tomchristensenOP•16mo ago

No, there's a single example of a transaction in their docs (https://github.com/planetscale/database-js#transactions) but it just shows how to run a couple of statements in sequence.

GitHub

GitHub - planetscale/database-js: A Fetch API-compatible PlanetScal...

A Fetch API-compatible PlanetScale database driver - GitHub - planetscale/database-js: A Fetch API-compatible PlanetScale database driver

tomchristensenOP•16mo ago

Ok after some more testing it seems like this might be an issue with Planetscale's library. I'll open an issue there

Jim•14mo ago

Hi do you have a link to the issue?

Gaming

Programming

Inconsistent transactions with Drizzle/Planetscale