AWS redshift documentation change
Summary
Restructured documentation to explain SNAPSHOT and SERIALIZABLE isolation levels in depth, added transaction examples, and removed outdated error resolution sections. Added details about concurrency control mechanisms and catalog table behavior.
Security assessment
The changes focus on clarifying transaction isolation levels and concurrency mechanics rather than addressing security vulnerabilities. While isolation levels impact data integrity, there is no explicit mention of patching vulnerabilities or security controls like authentication/encryption.
Diff
diff --git a/redshift/latest/dg/c_serial_isolation.md b/redshift/latest/dg/c_serial_isolation.md index 0d912ec31..9fcc27928 100644 --- a//redshift/latest/dg/c_serial_isolation.md +++ b//redshift/latest/dg/c_serial_isolation.md @@ -5 +5 @@ -Serializable isolation for system tables and catalog tablesHow to fix serializable isolation errors +SNAPSHOT and SERIALIZABLE isolationConsiderations @@ -9 +9 @@ Amazon Redshift will no longer support the creation of new Python UDFs starting -# Serializable isolation +# Isolation levels in Amazon Redshift @@ -11 +11 @@ Amazon Redshift will no longer support the creation of new Python UDFs starting -Some applications require not only concurrent querying and loading, but also the ability to write to multiple tables or the same table concurrently. In this context, _concurrently_ means overlapping, not scheduled to run at precisely the same time. Two transactions are considered to be concurrent if the second one starts before the first commits. Concurrent operations can originate from different sessions that are controlled either by the same user or by different users. +Concurrent write operations are supported in Amazon Redshift in a protective way, using write locks on tables and the principle of _serializable isolation_. Serializable isolation preserves the illusion that a transaction running against a table is the only transaction that is running against that table. @@ -13,16 +13 @@ Some applications require not only concurrent querying and loading, but also the -###### Note - -Amazon Redshift supports a default _automatic commit_ behavior in which each separately run SQL command commits individually. If you enclose a set of commands in a transaction block (defined by [BEGIN](./r_BEGIN.html) and [END](./r_END.html) statements), the block commits as one transaction, so you can roll it back if necessary. Exceptions to this behavior are the TRUNCATE and VACUUM commands, which automatically commit all outstanding changes made in the current transaction. - -Some SQL clients issue BEGIN and COMMIT commands automatically, so the client controls whether a group of statements are run as a transaction or each individual statement is run as its own transaction. Check the documentation for the interface you are using. For example, when using the Amazon Redshift JDBC driver, a JDBC `PreparedStatement` with a query string that contains multiple (semicolon separated) SQL commands runs all the statements as a single transaction. In contrast, if you use SQL Workbench/J and set AUTO COMMIT ON, then if you run multiple statements, each statement runs as its own transaction. - -Concurrent write operations are supported in Amazon Redshift in a protective way, using write locks on tables and the principle of _serializable isolation_. Serializable isolation preserves the illusion that a transaction running against a table is the only transaction that is running against that table. For example, two concurrently running transactions, T1 and T2, must produce the same results as at least one of the following: - - * T1 and T2 run serially in that order. - - * T2 and T1 run serially in that order. - - - - -Concurrent transactions are invisible to each other; they cannot detect each other's changes. Each concurrent transaction will create a snapshot of the database at the beginning of the transaction. A database snapshot is created within a transaction on the first occurrence of most SELECT statements, DML commands such as COPY, DELETE, INSERT, UPDATE, and TRUNCATE, and the following DDL commands: +Amazon Redshift databases support concurrent write operations by having each operation use the latest committed version, or snapshot, of their data at the start of the transaction. A database snapshot is created within a transaction on the first occurrence of most SELECT statements, DML commands such as COPY, DELETE, INSERT, UPDATE, and TRUNCATE, and the following DDL commands: @@ -41,5 +26 @@ Concurrent transactions are invisible to each other; they cannot detect each oth -If any serial execution of the concurrent transactions produces the same results as their concurrent execution, those transactions are deemed "serializable" and can be run safely. If no serial execution of those transactions can produce the same results, the transaction that runs a statement that might break the ability to serialize is stopped and rolled back. - -System catalog tables (PG) and other Amazon Redshift system tables (STL and STV) are not locked in a transaction. Therefore, changes to database objects that arise from DDL and TRUNCATE operations are visible on commit to any concurrent transactions. - -For example, suppose that table A exists in the database when two concurrent transactions, T1 and T2, start. Suppose that T2 returns a list of tables by selecting from the PG_TABLES catalog table. Then T1 drops table A and commits, and then T2 lists the tables again. Table A is now no longer listed. If T2 tries to query the dropped table, Amazon Redshift returns a "relation does not exist" error. The catalog query that returns the list of tables to T2 or checks that table A exists isn't subject to the same isolation rules as operations performed on user tables. +No other transaction is able to change this snapshot, meaning transactions are isolated from one another. That is, concurrent transactions are invisible to each other and can’t detect each other's changes. @@ -47 +28 @@ For example, suppose that table A exists in the database when two concurrent tra -Transactions for updates to these tables run in a read committed isolation mode. PG-prefix catalog tables don't support snapshot isolation. +ny concurrent execution of transactions must produce the same results as the serial execution of those transactions. If no serial execution of those transactions can produce the same results, the transaction that runs a statement that might break the ability to serialize is stopped and rolled back. @@ -49 +30 @@ Transactions for updates to these tables run in a read committed isolation mode. -## Serializable isolation for system tables and catalog tables +For example, suppose that a user attempts to run two concurrent transactions, T1 and T2. Running T1 and T2 must produce the same results as at least one of the following scenarios: @@ -51,3 +32 @@ Transactions for updates to these tables run in a read committed isolation mode. -A database snapshot is also created in a transaction for any SELECT query that references a user-created table or Amazon Redshift system table (STL or STV). SELECT queries that don't reference any table don't create a new transaction database snapshot. INSERT, DELETE, and UPDATE statements that operate solely on system catalog tables (PG) also don't create a new transaction database snapshot. - -## How to fix serializable isolation errors + * T1 and T2 run serially in that order. @@ -55 +34 @@ A database snapshot is also created in a transaction for any SELECT query that r -### ERROR:1023 DETAIL: Serializable isolation violation on a table in Redshift + * T2 and T1 run serially in that order. @@ -57 +35,0 @@ A database snapshot is also created in a transaction for any SELECT query that r -When Amazon Redshift detects a serializable isolation error, you see an error message such as the following. @@ -60 +37,0 @@ When Amazon Redshift detects a serializable isolation error, you see an error me - ERROR:1023 DETAIL: Serializable isolation violation on table in Redshift @@ -62 +39 @@ When Amazon Redshift detects a serializable isolation error, you see an error me -To address a serializable isolation error, you can try the following methods: +Isolation levels in Amazon Redshift prevent the following problems: @@ -64 +41 @@ To address a serializable isolation error, you can try the following methods: - * Retry the canceled transaction. + * Dirty reads ‐ A dirty read occurs when a transaction reads data that has not yet been committed. For example, suppose transaction 1 updates a row. Transaction 2 reads the updated row before T1 commits the update. If T1 rolls back the change, T2 will have read data in uncommitted rows that Amazon Redshift now considers to never have existed. @@ -66 +43 @@ To address a serializable isolation error, you can try the following methods: -Amazon Redshift detected that a concurrent workload is not serializable. It suggests gaps in the application logic, which can usually be worked around by retrying the transaction that encountered the error. If the issue persists, try one of the other methods. + * Non-repeatable reads ‐ A non-repeatable read occurs when a single transaction reads the same row twice but gets different data each time. For example, suppose transaction 1 reads a row. Transaction 2 updates or deletes that row and commits the update or delete. If T1 rereads the row, it retrieves different row values or discovers that the row has been deleted. @@ -68 +45 @@ Amazon Redshift detected that a concurrent workload is not serializable. It sugg - * Move any operations that don't have to be in the same atomic transaction outside of the transaction. + * Phantoms – A phantom is a row that matches the search criteria but is not initially seen. For example, suppose transaction 1 reads a set of rows that satisfies its search criteria. Transaction 2 generates a new row in an UPDATE or INSERT statement that matches the search criteria for T1. If T1 reruns its search statement, it gets a different set of rows. @@ -70 +46,0 @@ Amazon Redshift detected that a concurrent workload is not serializable. It sugg -This method applies when individual operations inside two transactions cross-reference each other in a way that can affect the outcome of the other transaction. For example, the following two sessions each start a transaction. @@ -72 +47,0 @@ This method applies when individual operations inside two transactions cross-ref - Session1_Redshift=# begin; @@ -74 +48,0 @@ This method applies when individual operations inside two transactions cross-ref - Session2_Redshift=# begin; @@ -76 +50 @@ This method applies when individual operations inside two transactions cross-ref -The result of a SELECT statement in each transaction might be affected by an INSERT statement in the other. In other words, suppose that you run the following statements serially, in any order. In every case, the result is one of the SELECT statements returning one more row than if the transactions were run concurrently. There is no order in which the operations can run serially that produces the same result as when run concurrently. Thus, the last operation that is run results in a serializable isolation error. +## SNAPSHOT and SERIALIZABLE isolation @@ -78,2 +52 @@ The result of a SELECT statement in each transaction might be affected by an INS - Session1_Redshift=# select * from tab1; - Session1_Redshift=# insert into tab2 values (1); +SNAPSHOT and SERIALIZABLE isolation are the two serializable isolation levels available in Amazon Redshift. @@ -81,2 +54 @@ The result of a SELECT statement in each transaction might be affected by an INS - Session2_Redshift=# insert into tab1 values (1); - Session2_Redshift=# select * from tab2; +SNAPSHOT isolation is the default isolation level when creating provisioned clusters and serverless workgroups, letting you process larger volumes of data than SERIALIZABLE isolation in less time. @@ -84 +56 @@ The result of a SELECT statement in each transaction might be affected by an INS -In many cases, the result of the SELECT statements isn't important. In other words, the atomicity of the operations in the transactions isn't important. In these cases, move the SELECT statements outside of their transactions, as shown in the following examples. +SERIALIZABLE isolation takes more time, but implements stricter constraints on concurrent transactions. This isolation level prevents problems such as write-skew anomalies by only allowing one transaction to commit, while canceling all other concurrent transaction with an serializable isolation violation error. @@ -86,4 +58 @@ In many cases, the result of the SELECT statements isn't important. In other wor - Session1_Redshift=# begin; - Session1_Redshift=# insert into tab1 values (1) - Session1_Redshift=# end; - Session1_Redshift=# select * from tab2; +Following is a timeline example of how two concurrent write operations would be handled when using SNAPSHOT isolation. Each user’s UPDATE statement is allowed to commit because they don’t conflict by attempting to update the same rows. @@ -91,4 +60,5 @@ In many cases, the result of the SELECT statements isn't important. In other wor - Session2_Redshift # select * from tab1; - Session2_Redshift=# begin; - Session2_Redshift=# insert into tab2 values (1) - Session2_Redshift=# end; +Time | User 1 action | User 2 action +---|---|--- +1 | BEGIN; | +2 | | BEGIN; +3 | SELECT * FROM Numbers; @@ -96 +65,0 @@ In many cases, the result of the SELECT statements isn't important. In other wor -In these examples, there are no cross-references in the transactions. The two INSERT statements don't affect each other. In these examples, there is at least one order in which the transactions can run serially and produce the same result as if run concurrently. This means that the transactions are serializable. @@ -98 +66,0 @@ In these examples, there are no cross-references in the transactions. The two IN - * Force serialization by locking all tables in each session. @@ -100 +68,4 @@ In these examples, there are no cross-references in the transactions. The two IN -The [LOCK](./r_LOCK.html) command blocks operations that can result in serializable isolation errors. When you use the LOCK command, be sure to do the following: + digits + ------ + 0 + 1 @@ -102 +73,2 @@ The [LOCK](./r_LOCK.html) command blocks operations that can result in serializa - * Lock all tables affected by the transaction, including those affected by read-only SELECT statements inside the transaction. +| +4 | | SELECT * FROM Numbers; @@ -104 +75,0 @@ The [LOCK](./r_LOCK.html) command blocks operations that can result in serializa - * Lock tables in the same order, regardless of the order that operations are performed in. @@ -106 +76,0 @@ The [LOCK](./r_LOCK.html) command blocks operations that can result in serializa - * Lock all tables at the beginning of the transaction, before performing any operations. @@ -108 +78,4 @@ The [LOCK](./r_LOCK.html) command blocks operations that can result in serializa - * Use snapshot isolation for concurrent transactions + digits + ------ + 0 + 1 @@ -110 +83,2 @@ The [LOCK](./r_LOCK.html) command blocks operations that can result in serializa -Use an ALTER DATABASE command with snapshot isolation. For more information about the SNAPSHOT parameter for ALTER DATABASE, see [Parameters](./r_ALTER_DATABASE.html#r_ALTER_DATABASE-parameters). +5 | UPDATE Numbers SET digits=0 WHERE digits=1; | +6 | SELECT * FROM Numbers; @@ -113,0 +88,4 @@ Use an ALTER DATABASE command with snapshot isolation. For more information abou + digits + ------ + 0 + 0 @@ -115 +93,4 @@ Use an ALTER DATABASE command with snapshot isolation. For more information abou -### ERROR:1018 DETAIL: Relation does not exist +| +7 | COMMIT; | +8 | | Update Numbers SET digits=1 WHERE digits=0; +9 | | SELECT * FROM Numbers; @@ -117 +97,0 @@ Use an ALTER DATABASE command with snapshot isolation. For more information abou -When you run concurrent Amazon Redshift operations in different sessions, you see an error message such as the following. @@ -120 +100,4 @@ When you run concurrent Amazon Redshift operations in different sessions, you se - ERROR: 1018 DETAIL: Relation does not exist. + digits + ------ + 1 + 1 @@ -122 +105,2 @@ When you run concurrent Amazon Redshift operations in different sessions, you se -Transactions in Amazon Redshift follow snapshot isolation. After a transaction begins, Amazon Redshift takes a snapshot of the database. For the entire lifecycle of the transaction, the transaction operates on the state of the database as reflected in the snapshot. If the transaction reads from a table that doesn't exist in the snapshot, it throws the 1018 error message shown previously. Even when another concurrent transaction creates a table after the transaction has taken the snapshot, the transaction can't read from the newly created table. +10 | | COMMIT; +11 | SELECT * FROM Numbers; @@ -124 +107,0 @@ Transactions in Amazon Redshift follow snapshot isolation. After a transaction b -To address this serialization isolation error, you can try to move the start of the transaction to a point where you know the table exists. @@ -126 +108,0 @@ To address this serialization isolation error, you can try to move the start of -If the table is created by another transaction, this point is at least after that transaction has been committed. Also, ensure that no concurrent transaction has been committed that might have dropped the table. @@ -127,0 +110,4 @@ If the table is created by another transaction, this point is at least after tha + digits + ------ + 1 + 0 @@ -129,3 +115,2 @@ If the table is created by another transaction, this point is at least after tha - session1 = # BEGIN; - session1 = # DROP TABLE A; - session1 = # COMMIT; +| +12 | | SELECT * FROM Numbers; @@ -134 +118,0 @@ If the table is created by another transaction, this point is at least after tha - session2 = # BEGIN; @@ -135,0 +120,4 @@ If the table is created by another transaction, this point is at least after tha + digits + ------ + 1 + 0 @@ -137,3 +125 @@ If the table is created by another transaction, this point is at least after tha - session3 = # BEGIN; - session3 = # CREATE TABLE A (id INT); - session3 = # COMMIT; +If the same scenario is run using serializable isolation, then Amazon Redshift terminates user 2 due to a serializable violation and returns error `1023`. For more information, see [Troubleshooting serializable isolation errors](./c_serial_isolation-serializable-isolation-troubleshooting.html). In this case, only user 1 can commit successfully. @@ -140,0 +127 @@ If the table is created by another transaction, this point is at least after tha +## Considerations @@ -142 +129 @@ If the table is created by another transaction, this point is at least after tha - session2 = # SELECT * FROM A; +When using isolation levels in Amazon Redshift, consider the following: @@ -144 +131 @@ If the table is created by another transaction, this point is at least after tha -The last operation that is run as the read operation by session2 results in a serializable isolation error. This error happens when session2 takes a snapshot and the table has already been dropped by a committed session1. In other words, even though a concurrent session3 has created the table, session2 doesn't see the table because it's not in the snapshot. + * Query the STV_DB_ISOLATION_LEVEL catalog view to view which isolation level your database is using. For more information, see [STV_DB_ISOLATION_LEVEL](./r_STV_DB_ISOLATION_LEVEL.html). @@ -146 +133 @@ The last operation that is run as the read operation by session2 results in a se -To resolve this error, you can reorder the sessions as follows. + * Query the PG_DATABASE_INFO view to see how many concurrent transactions are supported for your database. For more information, see [PG_DATABASE_INFO](./r_PG_DATABASE_INFO.html). @@ -147,0 +135 @@ To resolve this error, you can reorder the sessions as follows. + * System catalog tables (PG) and other Amazon Redshift system tables aren't locked in a transaction. Therefore, changes to database objects that arise from DDL and TRUNCATE operations are visible on commit to any concurrent transactions. @@ -149,3 +137 @@ To resolve this error, you can reorder the sessions as follows. - session1 = # BEGIN; - session1 = # DROP TABLE A; - session1 = # COMMIT; +For example, suppose that table A exists in the database when two concurrent transactions, T1 and T2, start. Suppose that T2 returns a list of tables by selecting from the PG_TABLES catalog table. Then T1 drops table A and commits, and then T2 lists the tables again. Table A is now no longer listed. If T2 tries to query the dropped table, Amazon Redshift returns a "relation does not exist" error. The catalog query that returns the list of tables to T2 or checks that table A exists isn't subject to the same isolation rules as operations performed on user tables.