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.

Sign up for GitHub

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

Jump to bottom

[YSQL] Read Only Deferrable in Serializable isolation is waiting for concurrent write transactions to commit #23120

Closed
1 task done
pkj415 opened this issue Jul 3, 2024 · 1 comment
Closed
1 task done

[YSQL] Read Only Deferrable in Serializable isolation is waiting for concurrent write transactions to commit #23120

pkj415 opened this issue Jul 3, 2024 · 1 comment
Assignees
@pao214
Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug priority/medium Medium priority issue

Comments

@pkj415
Copy link
Contributor

pkj415 commented Jul 3, 2024 •
edited by pao214
Loading

Jira Link: DB-12053

Description

Consider the following steps in order:

Session 1:

yugabyte=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
BEGIN

Session 2:
yugabyte=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
yugabyte=# UPDATE test SET v=v+1 WHERE k=1;
UPDATE 1

Session 1:
yugabyte=# SELECT * FROM test;
<waits for the concurrent transaction in Session 2 to commit>

The SELECT in Session 1 shouldn't really wait. SERIALIZABLE READ ONLY internally uses snapshot isolation as an optimization, so no read locks are taken by the SELECT in this case (unlike any regular read in serializable isolation level).

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

  • I confirm this issue does not contain any sensitive information.
@pkj415 pkj415 added area/ysql Yugabyte SQL (YSQL) status/awaiting-triage Issue awaiting triage labels Jul 3, 2024
@yugabyte-ci yugabyte-ci added kind/bug This issue is a bug priority/medium Medium priority issue labels Jul 3, 2024
@yugabyte-ci yugabyte-ci removed the status/awaiting-triage Issue awaiting triage label Jul 8, 2024
@pkj415 pkj415 mentioned this issue Jul 17, 2024
Merged
@pao214 pao214 self-assigned this Jul 17, 2024
pao214 added a commit that referenced this issue Jul 18, 2024
@pao214
[#23120] YSQL: Do not wait for a safe snapshot in serializable read o…
a64fbfc 
…nly deferrable mode.

Summary:
PostgreSQL uses SSI to implement serializable isolation level. Read only serializable isolation transactions may abort other
serializable transactions by creating a cycle in the transaction dependency graph. This can be avoided by waiting for all
concurrent transactions to finish by using the READ ONLY DEFERRABLE mode.

On the other hand, YugabyteDB does not use SSI for serializable transactions and uses 2-phase
locking instead. And YB assigns a different meaning to the DEFERRABLE mode for READ ONLY
serializable transactions as compared to Pg. In YB, DEFERRABLE READ ONLY transactions wait
out the maximum possible clock skew to avoid read restart errors (see https://docs.yugabyte.com/preview/architecture/transactions/read-restart-error/).
So, in YB's use of DEFERRABLE, there is no need to wait for concurrent transactions to complete, apart from waiting out the max_clock_skew_usec.
Jira: DB-12053

Test Plan:
Jenkins

```
./yb_build.sh --java-test TestPgRegressWaitQueues
```

There are tests already in modify-transaction-characteristics that use a read only deferrable transaction for a select statement. Modified the test output to remove waiting.

Reviewers: pjain, bkolagani, smishra

Reviewed By: pjain

Subscribers: yql

Differential Revision: https://phorge.dev.yugabyte.com/D36671
pao214 added a commit that referenced this issue Jul 19, 2024
@pao214
[BACKPORT 2.20][#23120] YSQL: Do not wait for a safe snapshot in seri…
3191178 
…alizable read only deferrable mode.

Summary:
Original commit: a64fbfc / D36671
PostgreSQL uses SSI to implement serializable isolation level. Read only serializable isolation transactions may abort other
serializable transactions by creating a cycle in the transaction dependency graph. This can be avoided by waiting for all
concurrent transactions to finish by using the READ ONLY DEFERRABLE mode.

On the other hand, YugabyteDB does not use SSI for serializable transactions and uses 2-phase
locking instead. And YB assigns a different meaning to the DEFERRABLE mode for READ ONLY
serializable transactions as compared to Pg. In YB, DEFERRABLE READ ONLY transactions wait
out the maximum possible clock skew to avoid read restart errors (see https://docs.yugabyte.com/preview/architecture/transactions/read-restart-error/).
So, in YB's use of DEFERRABLE, there is no need to wait for concurrent transactions to complete, apart from waiting out the max_clock_skew_usec.
Jira: DB-12053

Test Plan: Jenkins

Reviewers: pjain, bkolagani, smishra

Reviewed By: bkolagani

Subscribers: yql

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D36697
pao214 added a commit that referenced this issue Jul 19, 2024
@pao214
[BACKPORT 2024.1][#23120] YSQL: Do not wait for a safe snapshot in se…
7579fb9 
…rializable read only deferrable mode.

Summary:
Original commit: a64fbfc / D36671
PostgreSQL uses SSI to implement serializable isolation level. Read only serializable isolation transactions may abort other
serializable transactions by creating a cycle in the transaction dependency graph. This can be avoided by waiting for all
concurrent transactions to finish by using the READ ONLY DEFERRABLE mode.

On the other hand, YugabyteDB does not use SSI for serializable transactions and uses 2-phase
locking instead. And YB assigns a different meaning to the DEFERRABLE mode for READ ONLY
serializable transactions as compared to Pg. In YB, DEFERRABLE READ ONLY transactions wait
out the maximum possible clock skew to avoid read restart errors (see https://docs.yugabyte.com/preview/architecture/transactions/read-restart-error/).
So, in YB's use of DEFERRABLE, there is no need to wait for concurrent transactions to complete, apart from waiting out the max_clock_skew_usec.
Jira: DB-12053

Test Plan:
Jenkins

```
./yb_build.sh --java-test TestPgRegressWaitQueues
```

There are tests already in modify-transaction-characteristics that use a read only deferrable transaction for a select statement. Modified the test output to remove waiting.

Reviewers: pjain, bkolagani, smishra

Reviewed By: bkolagani

Subscribers: yql

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D36696
@pao214
Copy link
Contributor

pao214 commented Jul 19, 2024

Landed on master, 2024.1, and 2.20.

@pao214 pao214 closed this as completed Jul 19, 2024
jasonyb pushed a commit that referenced this issue Jul 19, 2024
@yugabyte-ci @jaki
[BACKPORT pg15-cherrypicks] all: Bulk port from master - 59
b0de783 
Summary:
 a64fbfc [#23120] YSQL: Do not wait for a safe snapshot in serializable read only deferrable mode.
 9f82c01 [#23188] DocDB: Persist new colocated_id mapping discovered as part of processing CHANGE_METADATA_OP in xCluster ClusterConfig.
 1522522 Update third-party dependencies to remove CentOS 7
 cd7410c 2.18.1.1 release notes (#23241)
 550458d [#23047] docdb: Fix cotable ids in flushed frontier at restore
 225ddfe [PLAT-14700] Make node-agent error message on installation to be more precise
 9c9a059 [PLAT-14563] Fixing disk mounting logic to use mount points defined in the config
 84eefbd [DOC-368] Azure workload identity docs changes (#22881)
 781af0d [#23183] xCluster: Move Setup, Bootstrap, Alter and Delete Target Replication functions out of CatalogManager
 1d646b1 [docs] Add ysql_output_buffer_size to yb-tserver config reference. (#23233)
 2d95dd2 Fix the issue yaml parsing
 dd9e85b [PLAT-14534]Add regex match for GCP Instance template
 d3bba18 update diagram (#23245)
 78b317c [/PLAT-14708] Fix JSON field name in TaskInfo query
 ac9164b [#23173] DocDB: Allow large bytes to be passed to RateLimiter

Test Plan: Jenkins: rebase: pg15-cherrypicks

Reviewers: jason, tfoucher

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D36723
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@pao214 pao214

Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug priority/medium Medium priority issue
Projects
Wait-Queue Based Locking
Status: Done
Milestone
No milestone
Development

No branches or pull requests
3 participants
@pkj415 @pao214 @yugabyte-ci