2. Vector Concepts : Transaction Isolation Model : Transaction Examples
 
Share this page                  
Transaction Examples
Non-dirty Read Example
This example demonstrates how committing transactions are not visible to concurrently running transactions. In this case, Transaction A will insert a value, but Transaction B will not see it until a new transaction is started.
Transaction A
Transaction B
BEGIN TRANSACTION;
SELECT A FROM T; --sees the old state
 
 
INSERT INTO T VALUES (7);
SELECT A FROM T; --sees the old
                   state + 
                   value 7
 
COMMIT;
 
 
BEGIN TRANSACTION;
SELECT A FROM T; --sees the same old state
 
 
SELECT A FROM T; --sees the old state without 7
 
 
SELECT A FROM T; --sees the old state without 7
COMMIT;          --new transaction starts
SELECT A FROM T; --sees the new
                   state including 7
Conflict Resolution Example
In this example, two transactions concurrently insert a record with the same value in a Primary Key attribute. This causes a conflict for the second committing transaction due to constraint violation.
Transaction A
Transaction B
--Empty Table T has a PRIMARY KEY on attribute PK
BEGIN TRANSACTION;
SELECT A FROM T; --sees an empty table
INSERT INTO T VALUES(6);
INSERT INTO T VALUES(7);
SELECT A FROM T; --sees 6 and 7
 
 
 
 
SELECT A FROM T; --sees 6 and 7
 
COMMIT;          --success
 
 
 
 
 
 
BEGIN TRANSACTION;
SELECT A FROM T; --sees an empty table
INSERT INTO T VALUES(5);
INSERT INTO T VALUES(7);
 
SELECT A FROM T; --sees 5 and 7
 
SELECT A FROM T; --sees 5 and 7
COMMIT;          --fails due to a
                   primary key
                   violation on 7
Note:  Concurrent updates on a table with a clustered index on foreign key columns will conflict; only one of the transactions will be allowed to commit. Others will return an error and be aborted.