What sql server isolation level should I choose to prevent concurrent reads?

Question

I have the following transaction:

1. SQL inserts a 1 new record into a table called tbl_document
2. SQL deletes all records matching a criteria in another table called tbl_attachment
3. SQL inserts multiple records into the tbl_attachment

Until this transaction finishes, I don't want others users to be aware of the (1) new records in tbl_document, (2) deleted records in tbl_attachment, and (3) modified records in tbl_attachment.

Would Read Committed Isolation be the correct isolation level?

Answer 1

yes, like this:

BEGIN TRANSACTION

   insert into tbl_document ...
   delete tbl_attachment where ...
   inserts into tbl_attachment ...

COMMIT

you may block/lock users until you are finished and commit/rollback the transaction. Also, someone could SELECT your rows from tbl_attachment after your insert into tbl_document but before your delete. If you need to prevent that do this:

BEGIN TRANSACTION

   select tbl_attachment with (UPDLOCK,HOLDLOCK) where ...
   insert into tbl_document ...
   delete tbl_attachment where ...
   inserts into tbl_attachment ...

COMMIT

or just delete tbl_attachment before the insert into tbl_document and forget the select with the locking hints.

Answer 2

It doesn't matter the transaction isolation level of your writes. What is important is the isolation level of your reads. Normally reads will not see your insert/update/delete until is committed. The only isolation level can does see uncommitted changes is READ UNCOMMITTED. If the concurrent thread uses dirty reads, there is nothing you can do in the write to prevent it.

READ UNCOMMITTED can be either set as an isolation level, or requested explicitly with a (NOLOCK) table hint. Dirty reads can see inconsistent transaction data (eg. the debit does not balance the credit of an operation) and also can cause duplicate reads (read the same row multiple times from a table) and trigger mysterious key violations.