[YSQL] Support SELECT ... NOWAIT in READ COMMITTED isolation level #11760
Assignees
Labels
area/ysql
Yugabyte SQL (YSQL)
Comments
pkj415
added a commit
to pkj415/yugabyte-db
that referenced
this issue
Mar 19, 2022
…evel Summary: The NOWAIT clause with a SELECT (that is attempting to take explicit lock(s)) ensures that if the statement sees another txn holding a conflicting lock mode, it will error out immediately with an error message like follows - "could not obtain lock on row in relation". The NOWAIT clause currently has no effect for any isolation level in YSQL. In READ COMMITTED isolation level - the statement would face a kConflict error and the statement retry mechanism of this isolation would retry indefinitely till the lock(s) are acquired (in yb_attempt_to_restart_on_error()). In REPEATABLE READ and SERIALIZABLE isolation level - the statement would either abort the conflicting txn(s) or would abort itself and throw a kConflict error. The former would occur if the statement's txn has higher priority than all conflicting txn(s). The diff fixes the semantics for READ COMMITTED isolation level as a first step since that is easier to do. All txns in READ COMMITTED isolation internally use the same priority and so the txn always faces a kConflict error. We just have to skip retrying the statement for such errors if the NOWAIT clause is specified. Test Plan: Jenkins: urgent ./yb_build.sh --java-test org.yb.pgsql.TestPgExplicitLocks#testNoWait Reviewers: mihnea Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D16007
pkj415
added a commit
to pkj415/yugabyte-db
that referenced
this issue
Mar 22, 2022
…evel Summary: The NOWAIT clause with a SELECT (that is attempting to take explicit lock(s)) ensures that if the statement sees another txn holding a conflicting lock mode, it will error out immediately with an error message like follows - "could not obtain lock on row in relation". The NOWAIT clause currently has no effect for any isolation level in YSQL. In READ COMMITTED isolation level - the statement would face a kConflict error and the statement retry mechanism of this isolation would retry indefinitely till the lock(s) are acquired (in yb_attempt_to_restart_on_error()). In REPEATABLE READ and SERIALIZABLE isolation level - the statement would either abort the conflicting txn(s) or would abort itself and throw a kConflict error. The former would occur if the statement's txn has higher priority than all conflicting txn(s). The diff fixes the semantics for READ COMMITTED isolation level as a first step since that is easier to do. All txns in READ COMMITTED isolation internally use the same priority and so the txn always faces a kConflict error. We just have to skip retrying the statement for such errors if the NOWAIT clause is specified. Test Plan: Jenkins: urgent ./yb_build.sh --java-test org.yb.pgsql.TestPgExplicitLocks#testNoWait Reviewers: mihnea Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D16007
pkj415
added a commit
that referenced
this issue
Apr 9, 2022
Summary: The NOWAIT clause with a SELECT (that is attempting to take explicit lock(s)) ensures that if the statement sees another txn holding a conflicting lock mode, it will error out immediately with an error message like follows - "could not obtain lock on row in relation". The NOWAIT clause currently has no effect for any isolation level in YSQL. In READ COMMITTED isolation level - the statement would face a kConflict error and the statement retry mechanism of this isolation would retry indefinitely till the lock(s) are acquired (in yb_attempt_to_restart_on_error()). In REPEATABLE READ and SERIALIZABLE isolation level - the statement would either abort the conflicting txn(s) or would abort itself and throw a kConflict error. The former would occur if the statement's txn has higher priority than all conflicting txn(s). The diff fixes the semantics for READ COMMITTED isolation level as a first step since that is easier to do. All txns in READ COMMITTED isolation internally use the same priority and so the txn always faces a kConflict error. We just have to skip retrying the statement for such errors if the NOWAIT clause is specified. Test Plan: Jenkins: urgent ./yb_build.sh --java-test org.yb.pgsql.TestPgExplicitLocks#testNoWait Reviewers: mihnea, dmitry Reviewed By: dmitry Subscribers: lnguyen, yql Differential Revision: https://phabricator.dev.yugabyte.com/D16007
pkj415
added a commit
that referenced
this issue
Apr 9, 2022
…ation level Summary: The NOWAIT clause with a SELECT (that is attempting to take explicit lock(s)) ensures that if the statement sees another txn holding a conflicting lock mode, it will error out immediately with an error message like follows - "could not obtain lock on row in relation". The NOWAIT clause currently has no effect for any isolation level in YSQL. In READ COMMITTED isolation level - the statement would face a kConflict error and the statement retry mechanism of this isolation would retry indefinitely till the lock(s) are acquired (in yb_attempt_to_restart_on_error()). In REPEATABLE READ and SERIALIZABLE isolation level - the statement would either abort the conflicting txn(s) or would abort itself and throw a kConflict error. The former would occur if the statement's txn has higher priority than all conflicting txn(s). The diff fixes the semantics for READ COMMITTED isolation level as a first step since that is easier to do. All txns in READ COMMITTED isolation internally use the same priority and so the txn always faces a kConflict error. We just have to skip retrying the statement for such errors if the NOWAIT clause is specified. Original commit: 7c705d2 / D16007 Test Plan: Jenkins: rebase: 2.12 ./yb_build.sh --java-test org.yb.pgsql.TestPgExplicitLocks#testNoWait Reviewers: dmitry Reviewed By: dmitry Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D16417
pkj415
added a commit
to pkj415/yugabyte-db
that referenced
this issue
May 6, 2022
Summary: The NOWAIT clause with a SELECT (that is attempting to take explicit lock(s)) ensures that if the statement sees another txn holding a conflicting lock mode, it will error out immediately with an error message like follows - "could not obtain lock on row in relation". The NOWAIT clause currently has no effect for any isolation level in YSQL. In READ COMMITTED isolation level - the statement would face a kConflict error and the statement retry mechanism of this isolation would retry indefinitely till the lock(s) are acquired (in yb_attempt_to_restart_on_error()). In REPEATABLE READ and SERIALIZABLE isolation level - the statement would either abort the conflicting txn(s) or would abort itself and throw a kConflict error. The former would occur if the statement's txn has higher priority than all conflicting txn(s). The diff fixes the semantics for READ COMMITTED isolation level as a first step since that is easier to do. All txns in READ COMMITTED isolation internally use the same priority and so the txn always faces a kConflict error. We just have to skip retrying the statement for such errors if the NOWAIT clause is specified. Test Plan: Jenkins: urgent ./yb_build.sh --java-test org.yb.pgsql.TestPgExplicitLocks#testNoWait Reviewers: mihnea, dmitry Reviewed By: dmitry Subscribers: lnguyen, yql Differential Revision: https://phabricator.dev.yugabyte.com/D16007
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Description
Currently, NOWAIT has no effect in READ COMMITTED isolation level. This is to be fixed.
The text was updated successfully, but these errors were encountered: