Prevent phantom reads just in some rows
You need to analyze some data about your bank customers with the customer_id between 1 and 10. You only want to lock the rows of the customers table with the customer_id between 1 and 10. In doing this, you guarantee nobody will be able to change these rows, and you allow other transactions to work with the rest of the table.
You need to select the customers and execute some mathematical operations again. (We won't focus either on these operations for this exercise.) After that, you want to select the customers with the customer_id between 1 and 10 again, ensuring nothing has changed.
How can you prepare the script?
This exercise is part of the course
Transactions and Error Handling in SQL Server
Exercise instructions
- Set the appropriate isolation level to prevent phantom reads.
- Begin a transaction.
- Select those customers you want to lock.
- Commit the transaction.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Set the appropriate isolation level
___ ___ ___ ___ ___
-- Begin a transaction
___ ___
-- Select customer_id between 1 and 10
SELECT *
FROM customers
___ customer_id ___ ___ AND ___;
-- After completing some mathematical operation, select customer_id between 1 and 10
SELECT *
FROM customers
___ customer_id ___ ___ AND ___;
-- Commit the transaction
___ ___