YSQL: Enable concurrent transactions on ALTER TABLE DROP COLUMN & ADD COLUMN DDL Statement #7327
Comments
emhna
changed the title
emhna
changed the title
emhna
added a commit
that referenced
this issue
Jul 29, 2021
…DD COLUMN] DDL Statement
Summary:
Before this change, when a DDL operation such as ALTER TABLE was performed concurrently with a transaction block, the transaction would fail with Catalog Version Mismatch. The transaction block would fail (abort) even when it only operates on tables that are not being altered. By "concurrently", we mean that the DDL occurs after the transaction block BEGIN and before the transaction block COMMIT (no issue if the transaction block occurs either before or after the DDL).
This commit addresses this issue by ensuring that only transactions that operate on the table being altered may be aborted.
Specifically, for ALTER TABLE ADD/DROP COLUMN commands, we now:
1. Abort all transactions that are currently holding a distributed transaction lock on the table's tablets (except the DDL itself).
2. Still increment the table's schema version so any operation occurring after the DDL must use the new schema to be successfully committed.
Therefore, relative to the DDL (ALTER command), we have 3 scenarios for **concurrent** transaction blocks:
1. If the transaction block takes a distributed transaction lock on a tablet of the altered table it will be aborted by case 1 above.
2. If the transaction block tries to use the table after the alter it will see that the schema version changed since it began, and again
error out (transaction blocks use snapshot isolation semantics for reading the syscatalog metadata such as the table schema).
3. Else (e.g. if the transaction block does not affect the table being altered), there will be no error.
The change will be applied for ALTER TABLE ADD/DROP COLUMN commands.
This will be extended to the other ALTER commands in a follow-up diff.
Note: The rules above have some subtleties, in particular some sub-cases of scenarios 1 and 2 where we don't need to error out and safely continue without affecting correctness.
The main cases are:
- In scenario 1 if we used the table but not taken a distributed lock (e.g. pure read in snapshot isolation or pure write in serializable) then we don't need to abort the transaction block.
- In scenario 2 if we have not yet used the altered table (i.e. before the alter finished), we may be able to just pick up the new (updated) schema version directly.
These are covered in the included tests.
Test Plan: Added new test file `TestAlterTableWithConcurrentTxn`
Reviewers: amitanand, mihnea, alex, jason, oleg
Reviewed By: mihnea, alex, jason, oleg
Subscribers: oleg, jenkins-bot, dsrinivasan, alex, nicolas, bogdan, yql
Differential Revision: https://phabricator.dev.yugabyte.com/D10666
|
Closed by c3bfef9. |
emhna
added a commit
that referenced
this issue
Sep 28, 2021
…adata field during ListSnapshots Summary: YB backup script has issues while decoding transaction_id from the transaction metadata field which is 16 byte UUID. Transaction metadata will be removed duirng ListSnapshots in yb-admin to avoid deserializaing that field. The same fix in yb-admin was applied in Github issue #7327: https://phabricator.dev.yugabyte.com/D10666. Test Plan: Jenkins: rebase: 2.6 Reviewers: oleg, streddy Reviewed By: oleg, streddy Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D13191
emhna
added a commit
that referenced
this issue
Jul 29, 2022
… DDL Statements Summary: Before this change, when DDL operations such as ALTER TABLE ADD CONSTRAINT was performed concurrently with a transaction block the transaction would fail with Catalog Version Mismatch. This was the case for most DDL operations except for DDL's that already applied the fix (see Github issue #7325 and #7327). The transaction block would fail (abort) even when it only operates on tables that are not being altered. By "concurrently", we mean that the DDL occurs after the transaction block BEGIN and before the transaction block COMMIT (no issue if the transaction block occurs either before or after the DDL). This commit addresses this issue by ensuring that only transactions that operate on the table being altered may be aborted. Specifically, for ALTER TABLE commands, we now: 1. Abort all transactions that are currently holding a distributed transaction lock on the table's tablets (except the DDL itself). 2. Still increment the table's schema version so any operation occurring after the DDL must use the new schema to be successfully committed. Therefore, relative to the DDL (ALTER command), we have 3 scenarios for **concurrent** transaction blocks: 1. If the transaction block takes a distributed transaction lock on a tablet of the altered table it will be aborted by case 1 above. 2. If the transaction block tries to use the table after the alter it will see that the schema version changed since it began, and again error out (transaction blocks use snapshot isolation semantics for reading the syscatalog metadata such as the table schema). 3. Else (e.g. if the transaction block does not affect the table being altered), there will be no error. The change will be applied for the following ALTER TABLE commands: NCP = Non Consistency Preserving (any change aborts all concurrent transactions on related object) CP = Consistency Preserving (does not conflict with any concurrent transactions on related object) | AddConstraint/DropConstraint | NCP | | ChangeOwner | NCP | | ColumnDefault | NCP | | DropNotNull/SetNotNull | NCP | | AddIdentity/DropIdentity/SetIdentity | NCP | | AddIndex| NCP | | EnableRowSecurity/DisableRowSecurity/ForceRowSecurity/NoForceRowSecurity | NCP | | EnableTrig/EnableAlwaysTrig/EnableReplicaTrig/EnableTrigAll/EnableTrigUser | NCP on main table, CP on triggered table(s) | | DisableTrig/DisableTrigAll/DisableTrigUser | NCP on main table, CP on triggered table | | AttachPartition/DetachPartition | NCP on main table, NCP on partition table | | AddForeignKey | NCP on main table, NCP on foreign table | | DropForeignKey | NCP on main table, NCP on foreign table | Note: The rules above have some subtleties, in particular some sub-cases of scenarios 1 and 2 where we don't need to error out and safely continue without affecting correctness. The main cases are: - In scenario 1 if we used the table but not taken a distributed lock (e.g. pure read in snapshot isolation or pure write in serializable) then we don't need to abort the transaction block. - In scenario 2 if we have not yet used the altered table (i.e. before the alter finished), we may be able to just pick up the new (updated) schema version directly. These are covered in the included tests. Test Plan: Added Java tests. Reviewers: amitanand, mihnea, myang Reviewed By: myang Subscribers: tverona, jason, zyu, yql, bogdan Differential Revision: https://phabricator.dev.yugabyte.com/D12505
emhna
added a commit
that referenced
this issue
Aug 24, 2022
… DDL Statements Summary: Before this change, when DDL operations such as ALTER TABLE ADD CONSTRAINT was performed concurrently with a transaction block the transaction would fail with Catalog Version Mismatch. This was the case for most DDL operations except for DDL's that already applied the fix (see Github issue #7325 and #7327). The transaction block would fail (abort) even when it only operates on tables that are not being altered. By "concurrently", we mean that the DDL occurs after the transaction block BEGIN and before the transaction block COMMIT (no issue if the transaction block occurs either before or after the DDL). This commit addresses this issue by ensuring that only transactions that operate on the table being altered may be aborted. Specifically, for ALTER TABLE commands, we now: 1. Abort all transactions that are currently holding a distributed transaction lock on the table's tablets (except the DDL itself). 2. Still increment the table's schema version so any operation occurring after the DDL must use the new schema to be successfully committed. Therefore, relative to the DDL (ALTER command), we have 3 scenarios for **concurrent** transaction blocks: 1. If the transaction block takes a distributed transaction lock on a tablet of the altered table it will be aborted by case 1 above. 2. If the transaction block tries to use the table after the alter it will see that the schema version changed since it began, and again error out (transaction blocks use snapshot isolation semantics for reading the syscatalog metadata such as the table schema). 3. Else (e.g. if the transaction block does not affect the table being altered), there will be no error. The change will be applied for the following ALTER TABLE commands: NCP = Non Consistency Preserving (any change aborts all concurrent transactions on related object) CP = Consistency Preserving (does not conflict with any concurrent transactions on related object) | AddConstraint/DropConstraint | NCP | | ChangeOwner | NCP | | ColumnDefault | NCP | | DropNotNull/SetNotNull | NCP | | AddIdentity/DropIdentity/SetIdentity | NCP | | AddIndex| NCP | | EnableRowSecurity/DisableRowSecurity/ForceRowSecurity/NoForceRowSecurity | NCP | | EnableTrig/EnableAlwaysTrig/EnableReplicaTrig/EnableTrigAll/EnableTrigUser | NCP on main table, CP on triggered table(s) | | DisableTrig/DisableTrigAll/DisableTrigUser | NCP on main table, CP on triggered table | | AttachPartition/DetachPartition | NCP on main table, NCP on partition table | | AddForeignKey | NCP on main table, NCP on foreign table | | DropForeignKey | NCP on main table, NCP on foreign table | Note: The rules above have some subtleties, in particular some sub-cases of scenarios 1 and 2 where we don't need to error out and safely continue without affecting correctness. The main cases are: - In scenario 1 if we used the table but not taken a distributed lock (e.g. pure read in snapshot isolation or pure write in serializable) then we don't need to abort the transaction block. - In scenario 2 if we have not yet used the altered table (i.e. before the alter finished), we may be able to just pick up the new (updated) schema version directly. These are covered in the included tests. Note: ALTER ADD/DROP PRIMARY KEY are removed from schema incrementation work since their operation covers that. Test Plan: Java unit test Reviewers: mihnea, alex, myang Reviewed By: alex, myang Subscribers: yql, ena, bogdan Differential Revision: https://phabricator.dev.yugabyte.com/D18946
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This issue is very similar to #7325.
When DDL transaction such as ALTER is performed at the same time as transaction block, the transaction will run into system catalog version mismatch.
To ensure this error message only appears when the transaction is operating on the same table, changes are made at Postgres and DocDB layer to abort only the transactions running on the tablet that's being altered.
This change will be applied on ALTER TABLE's DROP COLUMN and ADD COLUMN commands only.
The text was updated successfully, but these errors were encountered: