Problems Arising from Lack of Transaction Isolation
Without proper transaction isolation, several data integrity issues can occur:
- Lost Updates: When two transactions simultaneously modify the same record, a failure in one transaction can cause both modifications to fail.
This happens due to absence of locking mechanisms, allowing concurrent data operations without isolation.
-
Dirty Reads: Occurs when uncommitted changes are read by concurrent transactions. If the modifying transaction rolls back, the reading transaction has accessed invalid data, leading to inconsistent results.
-
Non-repeatable Reads: A transaction retrieves different values for the same query multiple times within its scope because another transaction modified the data between queries.
Difference Between Dirty Reads and Non-repeatable Reads
Dirty reads involve reading uncommitted modifications from ongoing transactions, while non-repeatable reads occur when repeated reads within a transaction encounter changes made by other committed transactions.
Important Note
Oracle and SQL Server default to Read Committed isolation level, whereas MySQL defaults to Repeatable Read level.
Checking Current Isolation Level
-- Check current transaction isolation level
DBCC UserOptions
Isolation Levels
Read Uncommitted (RU)
Also known as dirty read isolation, suitable for scenarios with low data integrity requirements or infrequently modified data.
Allows dirty reads but prevents lost updates. When one transaction writes data, other tarnsactions cannot write but can read using "exclusive write locks".
Since simultaneous writes are prevented, lost updates don't occur, but reads are permitted, making both pre- and post-modification data accessible, resulting in dirty and non-repeatable reads.
-- Set transaction isolation level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- First transaction not yet committed
BEGIN TRANSACTION
SELECT * FROM ProductTable WHERE RecordId = 7;
-- Second transaction modifies
UPDATE ProductTable SET Name = 'isolation demo' WHERE RecordId = 7;
-- Rollback first transaction
ROLLBACK TRANSACTION
Summary: Write blocks writes but allows reads.
Read Committed (RC)
Default level for SQL Server and Oracle databases.
Permits non-repeatable reads but prevents dirty reads through "instant shared read locks" and "exclusive write locks".
Reading transactions allow others to access the same data, but uncommitted data remains inaccessible to other transactions.
During modification, not only are other write operations blocked, but even uncommitted reads are prohibited until the writing transaction completes, ensuring write operation security and preventing dirty reads.
However, read operation security isn't guaranteed, so non-repeatable reads remain possible.
Summary: Write blocks writes and uncommitted reads.
Repeatable Read (RR)
MySQL's default isolation level.
Prevents dirty reads and non-repeatable reads but allows phantom reads through "shared read locks" and "exclusive write locks".
Reading transactions prevent writes but allow reads, while writing transactions block all other operations.
Ensures that between two read operations within a transaction, other transactions cannot modify the data. This level acquires shared locks before data retrieval and maintains them until transaction completion.
First transaction:
-- Check isolation level
DBCC UserOptions
-- Set isolation level to repeatable read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Begin transaction
BEGIN TRANSACTION
SELECT * FROM ProductTable WHERE RecordId = 4;
-- End transaction
ROLLBACK TRANSACTION
Second transaction:
-- Begin transaction for modification
-- When first transaction hasn't rolled back, this statement waits due to read blocking write
-- After rollback, the lock is released and modification completes
-- Other operations wait until this transaction commits
BEGIN TRANSACTION
UPDATE ProductTable SET Name = 'Repeatable Read demonstration' WHERE RecordId = 4;
COMMIT TRANSACTION
After the first transaction rolls back, the Name field becomes 'Repeatable Read demonstration' since the first transaction held shared locks.
The second transaction waited for the shared lock until the first transaction ended, then successfully modified the data.
Summary: Write blocks everything, read blocks writes.
Phantom Reads
In repeatable read mode, transactions can only lock data retrieved during their first execution. They cannot lock rows outside the initial result set, including newly inserted records.
If data satifsying the initial query condition is inserted between two queries, the second query returns different results. This is called a phantom read.
First transaction:
-- Begin transaction
BEGIN TRANSACTION
SELECT * FROM ProductTable WHERE Category = 'demo category'; -- Returns 2 records initially
SELECT * FROM ProductTable WHERE Category = 'demo category'; -- Returns 3 records, including new insertion
Second transaction:
-- Begin transaction for insertion
-- Since inserted record matches Category 'demo category'
-- This doesn't affect data from first transaction's query
-- So it can execute normally
-- Only writes to records from first transaction are blocked
-- Not all database writes
BEGIN TRANSACTION
INSERT INTO ProductTable (TypeId, Name, Cost, Link, ImagePath) VALUES (1, 'demo category', 123, '44.com', 'dddd.jpg')
COMMIT TRANSACTION
Serializable
Strictest transaction isolation level.
Transactions execute sequentially, reducing efficiency. Generally unused due to performance impact. Eliminates phantom reads since read and insert transactions execute sequentially.
Commonly used isolation level is Read Committed despite potential non-repeatable reads and phantom reads. These can be addressed using pessimistic and optimistic locking strategies.
Read Committed Snapshot
Similar to Read Committed mechanism, isolation level reads committed versions prior to operations.
Like RC, cannot avoid non-repeatable reads and phantom reads, but eliminates need for shared locks to access data.
Under RC level, when data is being modified but not committed, reading operations get blocked. However, Read Committed Snapshot avoids blocking.
Enable RC snapshot:
-- Enable read committed snapshot
ALTER DATABASE Inventory SET READ_COMMITTED_SNAPSHOT ON;
GO
SELECT * FROM ProductTable WHERE id = 4;
BEGIN TRANSACTION
Modification transaction:
-- Begin modification transaction
-- Not yet committed, but first read transaction can still query
-- Modified results aren't visible in first transaction, old data persists
BEGIN TRANSACTION
UPDATE ProductTable SET Name = 'Repeatable Read demo01' WHERE id = 4;
SELECT * FROM ProductTable WHERE id = 4;
COMMIT TRANSACTION -- After committing, first read transaction sees modification
During modification transaction, the reading transaction continues to operate, accessing old data until modification commits.
Under RC isolation, reading would be blocked while modification remains uncommitted.
Disable RC snapshot:
-- Disable read committed snapshot (requires closing connections except current)
ALTER DATABASE [Inventory] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [Inventory] SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE [Inventory] SET MULTI_USER;
GO