Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Reduce Postgres transaction isolation level #1860

Merged
merged 1 commit into from
Jul 8, 2021
Merged

Conversation

pm47
Copy link
Member

@pm47 pm47 commented Jul 5, 2021

I was able to reproduce #1856 by replaying the "concurrent channel
updates" test with hardcoded additional delays in the database code. It
was due to a conflict between addOrUpdateChannel and
updateChannelMetaTimestampColumn. The two calls run in parallel and
the latter completed before the former, causing it to fail. Reducing
the isolation level makes the problem disappear.

We reduce the transaction isolation level from SERIALIZABLE to
READ_COMMITTED. Note that [*]:

Read Committed is the default isolation level in PostgreSQL.

I'm not sure why we were using a stricter isolation level than the
default one, since we only use very basic queries. Doc does say that:

This behavior makes Read Committed mode unsuitable for commands that involve complex search conditions; however, it is just right for simpler cases

To make sure this didn't cause regression withe the locking
mechanism, I wrote an additional test specifically on the withLock
method.

Here is what the doc says on the INSERT ON CONFLICT DO UPDATE
statement, which we use for addOrUpdateChannel:

INSERT with an ON CONFLICT DO UPDATE clause behaves similarly. In Read Committed mode, each row proposed for insertion will either insert or update. Unless there are unrelated errors, one of those two outcomes is guaranteed. If a conflict originates in another transaction whose effects are not yet visible to the INSERT, the UPDATE clause will affect that row, even though possibly no version of that row is conventionally visible to the command.

In the scenario described above, the addOrUpdate will update the row
which timestamp was updated in parallel by the
updateChannelMetaTimestampColumn, and it's exactly what we want.

Fixes #1856.

[*] https://www.postgresql.org/docs/13/transaction-iso.html

@pm47 pm47 requested a review from t-bast July 5, 2021 14:33
t-bast
t-bast previously approved these changes Jul 5, 2021
Copy link
Member

@t-bast t-bast left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM, I don't see why we'd need a stronger isolation either.
Let's run with that for a while and see how it behaves.

@pm47
Copy link
Member Author

pm47 commented Jul 7, 2021

Rebased on #1862.

I was able to reproduce #1856 by replaying the "concurrent channel
updates" test with hardcoded additional delays in the database code. It
was due to a conflict between `addOrUpdateChannel` and
`updateChannelMetaTimestampColumn`. The two calls run in parallel and
the latter completed before the former, causing it to fail. Reducing
the isolation level makes the problem disappear.

We reduce the transaction isolation level from `SERIALIZABLE` to
`READ_COMMITTED`. Note that [*]:

> Read Committed is the default isolation level in PostgreSQL.

I'm not sure why we were using a stricter isolation level than the
default one, since we only use very basic queries. Doc does say that:

> This behavior makes Read Committed mode unsuitable for commands that involve complex search conditions; however, it is just right for simpler cases

To make sure this didn't cause regression withe the locking
mechanism, I wrote an additional test specifically on the `withLock`
method.

Here is what the doc says on the `INSERT ON CONFLICT DO UPDATE`
statement, which we use for `addOrUpdateChannel`:

> INSERT with an ON CONFLICT DO UPDATE clause behaves similarly. In Read Committed mode, each row proposed for insertion will either insert or update. Unless there are unrelated errors, one of those two outcomes is guaranteed. If a conflict originates in another transaction whose effects are not yet visible to the INSERT, the UPDATE clause will affect that row, even though possibly no version of that row is conventionally visible to the command.

In the scenario described above, the `addOrUpdate` will update the row
which timestamp was updated in parallel by the
`updateChannelMetaTimestampColumn`, and it's exactly what we want.

Fixes #1856.

[*] https://www.postgresql.org/docs/13/transaction-iso.html
@pm47
Copy link
Member Author

pm47 commented Jul 8, 2021

Rebased on master.

@pm47 pm47 requested a review from t-bast July 8, 2021 14:15
@pm47 pm47 merged commit 95fffe3 into master Jul 8, 2021
@pm47 pm47 deleted the reduce-isolation-level branch July 8, 2021 15:10
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging this pull request may close these issues.

Node crash during payment
2 participants