sql server - Problem with SQL transaction isolation level -

i have problem transaction isolation levels. there 2 tables involved, first 1 updated transaction isolation level set serializable, second 1 has foreign key on first one.

problem occurs when doing insert or update of second table. once in few hours error message:

snapshot isolation transaction aborted due update conflict. cannot use snapshot isolation access table 'dbo.first' directly or indirectly in database 'db' update, delete, or insert row has been modified or deleted transaction. retry transaction or change isolation level update/delete statement.

i don't set transaction isolation level when inserting or updating second table, ran command dbcc useroptions , returns read_committed

i need eliminate error asap, ahead

first:
seems, you're not using serializable, snapshot isolation introduced mssql 2005. here article understand difference:
http://blogs.msdn.com/b/craigfr/archive/2007/05/16/serializable-vs-snapshot-isolation-level.aspx

=> based on error, message, have explained again in comments error comes when editing second table.

second:
modifications mssql server tries acquire locks, , since there locks (by using transaction) on first table escalate locks on second table because of (foreign key) operation fails. every modification causes in fact mini transaction.

the default transaction level on mssql read committed, if turn on option read_committed_snapshot convert read committed snapshot transaction every time use read committed. leads error message get.

to precise vladv pointed out, it's not using snapshot isolation level, read committed with row versioning rather locking, on statement basis, snapshot using row versioning on transaction basis.

to understand difference check out this:
http://msdn.microsoft.com/en-us/library/ms345124(sql.90).aspx

to find out more read_committed_snapshot, explained in detail here:
http://msdn.microsoft.com/en-us/library/tcbchxcb(vs.80).aspx
, here: default sql server isolationlevel changes

another reason see snapshot isolation if have not specified it, using implicit transaction. after turing option on , don't specify isolation level on modifying statement (which don't), ms sql server choose whatever believes right isolation level. here details:
http://msdn.microsoft.com/en-us/library/ms188317(sql.90).aspx

for theses scenarios solution same though.

solution:
need execute operations in sequence, , can using transaction serializable isolation level on both operations: when inserting/updating first , when inserting/updating second.
way block respective other until completed.