These LIBNAME and data set options let you control how the Greenplum interface handles locking. For general information about an option, see LIBNAME Options for Relational Databases. For additional information, see your Greenplum documentation.
The Greenplum database manager supports the RC, RR, RU, S, and V isolation levels that are defined in the following table. The default value is RC.
Regardless of the isolation level, the database manager places exclusive locks on every row that is inserted, updated, or deleted. All isolation levels therefore ensure that only this application process can change any given row during a unit of work. No other application process can change any rows until the unit of work is complete.
|
Isolation Level |
Definition |
|---|---|
|
RC (read uncommitted) |
Allows dirty read, nonrepeatable read, and phantom Read operations. |
|
RR (repeatable read) |
Does not allow dirty read, nonrepeatable read, or phantom Read operations. |
|
RU (read uncommitted) |
Allows dirty read, nonrepeatable read, and phantom Read operations. |
|
S (serializable) |
Does not allow dirty read, nonrepeatable read, or phantom Read operations. |
|
V (versioning) |
Does not allow dirty read, nonrepeatable read, or phantom Read operations. These transactions are serializable, but higher concurrency is possible with this level than with the serializable isolation level. Typically, a nonlocking protocol is used. |
Here is how the terms in the table are defined.
A transaction that has very minimal isolation from concurrent transactions. In fact, this type of transaction can see changes that concurrent transactions make even before those transactions commit their changes.
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.
A transaction where the system reads a row once and is unable to read the row again later in the same transaction. This might occur if a concurrent transaction changed or even deleted the row. Therefore, the Read operation is not (necessarily) repeatable.
For example, suppose that transaction T1 retrieves a row, transaction T2 then updates that row, and transaction T1 then retrieves the same row again. Transaction T1 has now retrieved the same row twice but has seen two different values for it.
A transaction where a set of rows that is read once might become a different set of rows if the transaction attempts to read the rows again.
For example, suppose that transaction T1 retrieves the set of all rows that satisfy a 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 Greenplum database manager supports the RC, RR, S, and V isolation levels that are defined in the preceding table. Uncommitted reads are not allowed with this option. The default value is RC.