3. Vector Concepts : Transaction Isolation Model : Transaction Examples : Conflict Resolution Example
 
Share this page                  
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.