Optimistic Isolation Levels - Pro SQL Server Internals - page 426

With snapshot isolation level, however, this works a bit differently, as shown in Figure 21-10 . When the session
updates the row, it moves the old version of the row to the version store. Another session will read the row from there,
rather than being blocked and vice versa. As a result, the colors will be swapped.

Figure 21-10. Snapshot isolation level locking behavior

You need to be aware of RCSI and SNASPSHOT isolation level behavior especially if you have code that relies on
the blocking. One example is trigger-based implementation of referential integrity. You can have an ON DELETE trigger
on the referenced table where you are running a select statement to check to see if there are any rows in another table
referencing deleted rows. With an optimistic isolation level, the trigger can skip the rows that were inserted after the
transaction started. The solution here again is READCOMMITTED or other pessimistic isolation level table hints as part of
the select in both triggers on both referenced and referencing tables.

■ sQl server uses a READ COMMITTED isolation level when validating foreign key constraints. this means that you
can still have blocking between writers and readers even with optimistic isolation levels, especially if there are no indexes
on the referencing column that leads to a table scan of the referencing table.

Note