0

What IsolationLevel should I use in my TransactionScopes for:

  1. Reading a single record and I may update that record. This record is independent of all other data in the database so I only need to lock that one record.
  2. Trying to read a single record. If no record exists, then create a record with that value in that table. This is independent of all other tables, but it needs to lock this table so another thread doesn't also find no record, and then add the same record.

In the 2nd case, I think I need to lock the table to stop an insert on the table and any access on the record read, but allow reads of other records in the table and any access on any other table.

thanks - dave

David Thielen
  • 28,723
  • 34
  • 119
  • 193

1 Answers1

0

A am not sure about EF as I have not worked with it, but my answer is following:

  1. It is enough to use 'REPEATABLE READ' since it "Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes."

  2. I would use 'SERIALIZABLE' since "No other transactions can modify data that has been read by the current transaction until the current transaction completes."

You can read more here about isolation levels.

Ilya Palkin
  • 14,687
  • 2
  • 23
  • 36
  • I'm not sure SERIALIZABLE is enough as it won't allow inserts of keys in the range read, but by definition an insert will be a value greater than any range read. ??? – David Thielen Oct 24 '13 at 17:26
  • If you use 'SERIALIZABLE' then 'no new data can be added during the transaction'. Isn't it enough for you? – Ilya Palkin Oct 24 '13 at 19:44
  • According to the link above "Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction". I read that as a lot less than no new data added. Just no new data within the range of what was read. Am I missing something? – David Thielen Oct 24 '13 at 20:35