Using an isolation level
As seen in the video, sometimes it's important to be able to select an isolation level for an individual transaction. It's best to use START TRANSACTION to do this which is an alias of BEGIN TRANSACTION to make it clear something is different. You can specify an ISOLATION LEVEL when starting the transaction.
Here we are going to use REPEATABLE READ which protects us from dirty reads, nonrepeatable reads, and phantom reads. In the FFEIC data, RCON2210 is the demand deposits field, and tracks all outstanding checking accounts, bank-issued checks and unposted credits. They can be a liability to a bank if there was a funds rush for any reason. Let's find all those banks with over $100,000,000 in demand deposits.
This exercise is part of the course
Transactions and Error Handling in PostgreSQL
Exercise instructions
- Start a transaction with repeatable read.
- Select all the records with
RCON2210over $100,000,000 in demands deposits. - Select all the records with
RCON2210still over $100,000,000 in demands deposits. - Commit the transaction.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create a new transaction with an isolation level of repeatable read
___ TRANSACTION ___ LEVEL ___ ___;
-- Count of records over 100000000
SELECT ___(RCON2210)
FROM ffiec_reci
WHERE ___ > ___;
-- Count of records still over 100000000
SELECT ___(RCON2210)
FROM ffiec_reci
WHERE ___ > ___;
-- Commit the transaction
___;