concurrent transactions with write followed by reading fails due to timeout

[RichardJCai]

Relevant test: https://github.com/sequelize/sequelize/blob/main/test/integration/cls.test.js#L120-L137

Context:
Doing a write in a transaction (and not committing) that uses one table and a read from the same table in a separate transaction causes timeout.

This is expected behaviour in Cockroach as serializable transactions cause writes to hold exclusive locks.

We do not expect the behaviour from the test above to succeed or Cockroach to change in a way such that the above test will succeed. We'll likely have to document that this is expected behaviour for sequelize-cockroachdb if we can't find a workaround.

Relevant issue: cockroachdb/cockroach#61269

[papb]

Hello! Some questions:

• Is this timeout enforced by Cockroach itself (so the query ends with an error) or the timeout comes from JS code (such as the testing suite timeout)?
• Which transaction times out? Is it possible to detect this situation with code and abort one of them so that the other succeeds?

[RichardJCai]

Hello! Some questions:

• Is this timeout enforced by Cockroach itself (so the query ends with an error) or the timeout comes from JS code (such as the testing suite timeout)?
• Which transaction times out? Is it possible to detect this situation with code and abort one of them so that the other succeeds?

I believe the timeout is being enforced by JS code. We can configure statement and transaction timeouts in CockroachDB but I don't believe that's what's happening here.

The transaction that times out is the read in this case. The write holds the lock until the transaction is completed.

[papb]

Hmmm. I am trying to understand if this can be the cause of the "test crashes" I've experienced when trying to set up tests here in the past. If I make such a query that hangs forever, does this impact other unrelated query attempts? For example, if you cause one thousand hanging queries like this, will the whole database crash entirely? Or will it continue working as expected? The problem I faced is that some tests were not only timing out (which would be fine) but subsequent attempts to recreate the database did not go through, essentially crashing the whole test suite. That's why I ended up dividing it all in over one hundred different jobs.

By the way here is a permalink to that test, just in case the file changes in the future.

[RichardJCai]

For example, if you cause one thousand hanging queries like this, will the whole database crash entirely?

Interesting question, I haven't tested this case specifically but it's possible that it can crash the whole database. I've recently tested that opening ~500 connections to the db will completely crash it which is concerning.

[papb]

By the way, you can find more information on my failed attempts to run all 1800 tests in a single batch in #51.