Savepoint's effect on isolation levels

Now that you've explored savepoints, let's use them to set up a series of transactions that all need to work from the same initial snapshot of the data. REPEATABLE READ is an isolation level that enables us to give each statement inside the transaction the same data as the first statement operated on instead of the data as a result of the prior statement(s).

Recently, the FFEIC allowed for a progressive curtailment of foreign deposits, field RCON2203 in thousands, in the dataset. The new curtailment is 35% for more than $1 billion, 25% for more than $500 million, and 13% for more than $300 million. It's possible to order these statements to avoid reducing the data more than once. However, statements have the data before any adjustments with REPEATABLE READ.

Diese Übung ist Teil des Kurses

Transactions and Error Handling in PostgreSQL

Kurs anzeigen

Anleitung zur Übung

Start a REPEATABLE READ transaction.
Reduce RCON2203 by 35% if more than $1 billion, by 25% if more than $500 million, or by 13% if more than $300 million with a SAVEPOINT after each.
Close the transaction.
Total the RCON2203 field.

Interaktive Übung

Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.

-- Create a new transaction with a repeatable read isolation level
___ TRANSACTION ISOLATION LEVEL ___ ___;

-- Update records with a 35% reduction if greater than 1000000000
UPDATE ffiec_reci 
SET ___ = CAST(___ AS FLOAT) * .65 
WHERE CAST(RCON2203 AS FLOAT) > 1000000000;

___ million;

-- Update records with a 25% reduction if greater than 500000000
UPDATE ___ 
___ ___ = CAST(___ AS FLOAT) * .75 
___ CAST(RCON2203 AS FLOAT) ___ 500000000;

SAVEPOINT five_hundred;

-- Update records with a 13% reduction if greater than 300000000
___ ffiec_reci 
___ ___ = CAST(___ AS FLOAT) * .87 
___ CAST(___ AS FLOAT) ___ 300000000;

SAVEPOINT three_hundred;

-- Commit the transaction
___;

-- Select SUM the RCON2203 field
SELECT ___(CAST(RCON2203 AS FLOAT)) 
FROM ffiec_reci

Code bearbeiten und ausführen