The following LIBNAME and data set options let you control how the Amazon Redshift interface handles locking. For general information, see LIBNAME Options for Relational Databases.
The Amazon Redshift ODBC driver manager supports the S and RC isolation levels that are defined in this table.
|
Isolation Level |
Definition |
|---|---|
|
S (serializable) |
Does not allow dirty Reads, nonrepeatable Reads, or phantom Reads. |
|
RR (repeatable read) |
Does not allow dirty Reads or nonrepeatable Reads; does allow phantom Reads. |
|
RC (read committed) |
Does not allow dirty Reads or nonrepeatable Reads; does allow phantom Reads. |
|
RU (read uncommitted) |
Allows dirty Reads, nonrepeatable Reads, and phantom Reads. |
|
V (versioning) |
Does not allow dirty Reads, nonrepeatable Reads, or phantom Reads. These transactions are serializable, but higher concurrency is possible than with the serializable isolation level. Typically, a nonlocking protocol is used. |
Here are how the terms in the table are defined.
A transaction that exhibits this phenomenon has very minimal isolation from concurrent transactions. In fact, it can see changes that are made by those concurrent transactions even before they are committed.
For example, suppose that transaction T1 performs an update on a row, transaction T2 then retrieves that row, and transaction T1 then terminates with rollback. Transaction T2 has then seen a row that no longer exists.
When a transaction exhibits this phenomenon, a set of rows that it reads once might be a different set of rows if the transaction attempts to read them again.
For example, suppose that transaction T1 retrieves the set of all rows that satisfy some condition. Suppose that transaction T2 then inserts a new row that satisfies that same condition. If transaction T1 now repeats its retrieval request, it sees a row that did not previously exist, a phantom.
The Amazon Redshift ODBC driver manager supports the S and RC isolation levels that are defined in the preceding table.