AWS redshift documentation change
Summary
Updated isolation level documentation - reordered syntax options, consolidated explanations, removed detailed concurrency examples and viewing methods
Security assessment
Changes primarily restructure documentation about transaction isolation levels without introducing new security content. The removal of concurrency examples and viewing methods reduces operational documentation but doesn't indicate a security fix. The isolation level ordering change is syntactical without security implications.
Diff
diff --git a/redshift/latest/dg/r_CREATE_DATABASE.md b/redshift/latest/dg/r_CREATE_DATABASE.md index 8fcecbeab..a36080676 100644 --- a//redshift/latest/dg/r_CREATE_DATABASE.md +++ b//redshift/latest/dg/r_CREATE_DATABASE.md @@ -15 +15 @@ To create a database, you must be a superuser or have the CREATEDB privilege. To -You can't run CREATE DATABASE within a transaction block (BEGIN ... END). For more information about transactions, see [Serializable isolation](./c_serial_isolation.html). +You can't run CREATE DATABASE within a transaction block (BEGIN ... END). For more information about transactions, see [Isolation levels in Amazon Redshift](./c_serial_isolation.html). @@ -34 +34 @@ You can't run CREATE DATABASE within a transaction block (BEGIN ... END). For mo - [ ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT } ] + [ ISOLATION LEVEL { SNAPSHOT | SERIALIZABLE } ] @@ -115 +115 @@ CASE_SENSITIVE and CS are interchangeable and yield the same results. Similarly, -ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT } +ISOLATION LEVEL { SNAPSHOT | SERIALIZABLE } @@ -118,3 +118 @@ ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT } -A clause that specifies the isolation level used when queries run against a database. - - * SERIALIZABLE isolation – Provides full serializability for concurrent transactions. For more information, see [Serializable isolation](./c_serial_isolation.html). +A clause that specifies the isolation level used when queries run against a database. For more information on isolation levels, see [Isolation levels in Amazon Redshift](./c_serial_isolation.html). @@ -123,0 +122 @@ A clause that specifies the isolation level used when queries run against a data + * SERIALIZABLE isolation – Provides full serializability for concurrent transactions. @@ -127,79 +125,0 @@ A clause that specifies the isolation level used when queries run against a data -You can view which concurrency model your database is running as follows: - - * Query the STV_DB_ISOLATION_LEVEL catalog view. For more information, see [STV_DB_ISOLATION_LEVEL](./r_STV_DB_ISOLATION_LEVEL.html). - - SELECT * FROM stv_db_isolation_level; - - * Query the PG_DATABASE_INFO view. - - SELECT datname, datconfig FROM pg_database_info; - -The isolation level per database appears next to the key `concurrency_model`. A value of `1` denotes SNAPSHOT. A value of `2` denotes SERIALIZABLE. - - - - -In Amazon Redshift databases, both SERIALIZABLE and SNAPSHOT isolation are types of serializable isolation levels. That is, dirty reads, non-repeatable reads, and phantom reads are prevented according to the SQL standard. Both isolation levels guarantee that a transaction operates on a snapshot of data as it exists when the transaction begins, and that no other transaction can change that snapshot. However, SNAPSHOT isolation doesn't provide full serializability, because it doesn't prevent write skew inserts and updates on different table rows. - -The following scenario illustrates write skew updates using the SNAPSHOT isolation level. A table named `Numbers` contains a column named `digits` that contains `0` and `1` values. Each user's UPDATE statement doesn't overlap the other user. However, the `0` and `1` values are swapped. The SQL they run follows this timeline with these results: - -Time | User 1 action | User 2 action ----|---|--- -1 | BEGIN; | -2 | | BEGIN; -3 | SELECT * FROM Numbers; - - - digits - ------ - 0 - 1 - -| -4 | | SELECT * FROM Numbers; - - - digits - ------ - 0 - 1 - -5 | UPDATE Numbers SET digits=0 WHERE digits=1; | -6 | SELECT * FROM Numbers; - - - digits - ------ - 0 - 0 - -| -7 | COMMIT; | -8 | | Update Numbers SET digits=1 WHERE digits=0; -9 | | SELECT * FROM Numbers; - - - digits - ------ - 1 - 1 - -10 | | COMMIT; -11 | SELECT * FROM Numbers; - - - digits - ------ - 1 - 0 - -| -12 | | SELECT * FROM Numbers; - - - digits - ------ - 1 - 0 - -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 [How to fix serializable isolation errors](./c_serial_isolation.html#c_serial_isolation-serializable-isolation-troubleshooting). In this case, only user 1 can commit successfully. Not all workloads require serializable isolation as a requirement, in which case snapshot isolation suffices as the target isolation level for your database.