Isolation levels and transactions
SERIALIZABLE is an isolation level that takes a snapshot of the record when the first query or update statement is issued, and errors if the data is altered in any way outside of the transaction. Note that the transaction can do other work, such as declare variables, prior to the first query.
You'll be using the FFIEC dataset again to work with data where the annual change in savings deposits RCON0352 is affected by a large offset.
This exercise is part of the course
Transactions and Error Handling in PostgreSQL
Exercise instructions
- Start a transaction in
SERIALIZABLEmode. - Update all the the records where
RCON0352is greater than 100,000 with a 50% reduction. COMMITthe transaction.- Select the count of the records still with an
RCON0352greater than 100,000.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create a new transaction with a serializiable isolation level
___ TRANSACTION ISOLATION LEVEL ___;
-- Update records with a 50% reduction if greater than 100000
___ ffiec_reci
SET ___ = ___ * 0.5
WHERE RCON0352 > 100000;
-- Commit the transaction
___;
-- Select a count of records still over 100000
SELECT ___(RCON0352)
FROM ___
WHERE RCON0352 > 100000;