• Dirty reads occur when a transaction reads a record that is a part of another transaction that isn't complete yet.
• An unrepeatable read is caused when you read the record twice in a transaction, and a separate transaction alters the data in the interim.
• Lost updates happen when one update is successfully written to the database, but is accidentally overwritten by another transaction.

An example of a stored procedure that changes the current isolation level in order to avoid phantom records:

CREATE PROC wbs_CreateSession
    @USER VARCHAR(30)
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @SessionID INT, @ID INT
 
    -- Avoid phantom records by setting "serializable" isolation level
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 
    BEGIN TRAN
 
    -- Retrieve the last ID (if any)
    SELECT TOP 1 @ID = ISNULL([ID],0)+1 FROM [SESSION] ORDER BY [ID] DESC
 
    -- Generate a random session id
    SET @SessionID = RAND(@ID + 100) * 2147000000 
 
    -- Insert a new session record
    INSERT INTO [SESSION] ([SessionID], [ID], [USER], [TIMESTAMP])
    VALUES (@SessionID, @ID, @USER, GETDATE())
 
    COMMIT TRAN
 
    -- Revert to the default isolation level
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
    SELECT @SessionID
 
    SET NOCOUNT OFF
END