Databases Reference
In-Depth Information
,CURRENT_TIMESTAMP
);
EXEC SP_EXECUTESQL
N'PRINT ''DELETE FROM Person.Person WHERE BusinessEntityID = '' +CAST(@Scope_
Identity as varchar(8));
PRINT ''DELETE FROM Person.BusinessEntity WHERE BusinessEntityID = ''
+CAST(@Scope_Identity as varchar(8));'
,N'@Scope_Identity int',@Scope_Identity = @Scope_Identity
SELECT @Scope_Identity as BusinessEntityID
COMMIT TRANSACTION;
Run Session 1 now before switching over and executing Session 2.
You should see in the results of the i rst query from Session 1
(see Figure 6-6) that Syed Abbas is the i rst person of i ve
returned.
FIGURE 6-6
However, in the result of the second query from Session 1
(see Figure 6-7) James Anthony A is now i rst. James Anthony A is
a phantom.
To demonstrate how phantoms can be prevented, i rst remove
James Anthony A from the table. If you revert to Session 2 and look
in your message tab, you should see two delete
statements (see Figure 6-8 for details).
FIGURE 6-7
Copy those two rows into a new window and
execute them.
In Session 1, change the transaction isolation
level from read committed to serializable, and
repeat the example by running the code in
Session 1 i rst, followed by that in Session 2:
FIGURE 6-8
SET TRANSACTION ISOLATION LEVEL
--READ COMMITTED;
SERIALIZABLE;
This time the results for selects one and two from Session 1 are the
same, as shown in Figure 6-9. Note that the insert from Session 2
still happened, but only after the transaction in Session 1 had been
committed.
Don't forget to remove James Anthony A from your
AdventureWorks2012 database before continuing by repeating the
steps just outlined.
FIGURE 6-9
Search WWH ::
Custom Search