Dirty Read (SELECT) | Non-Repeatable Read (UPDATE) | Phantom Read (INSERT, DELETE) | |
---|---|---|---|
Read uncommitted | Yes | Yes | Yes |
Read committed | No | Yes | Yes |
Repeatable read | No | No | Yes |
Serializable | No | No | No |
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