Do DB locks require transactions?

Question

Is it true that "Every statement (select/insert/delete/update) has an isolation level regardless of transactions"?

I have a scenario in which I have set update of statements inside a transaction (ReadCommitted). And another set not in a transaction (select statements).

1. In this case when first set is executing another waits.

2. If I set READ_COMMITTED_SNAPSHOT for DB Deadlock occurs.

   ALTER DATABASE Amelio SET ALLOW_SNAPSHOT_ISOLATION ON
   ALTER DATABASE Amelio SET READ_COMMITTED_SNAPSHOT ON
   

To solve this problem, do I need to put "Select" statements in TransactionScope?

Answer 1

On SQL Server every transaction has an implicit or explicit transaction level. Explicit if called with BEGIN/COMMIT/ROLLBACK TRANSACTION, implicit if nothing like this is issued.

Start your snapshot before the update query starts. Otherwise you give SQL Server no chance to prepare the changed rows into tempdb and the Update query still has the lock open.

Another way without creating a snapshot isolation is to use SELECT <columns> FROM <table> WITH (NOLOCK) which is the way to tell SQL Server to get the rows no matter what (aka READ_UNCOMMITED). As it is a query hint it changes the isolation level even with your settings. Can work if you are not bothered which state of the row is queried - however caution needs to be used when evaluating the data received.