Do DB transaction locks lock?

Question

Is it true that "every statement (select / insert / delete / update) has a level of isolation independent of transactions"?

I have a script in which I installed an update of statements inside a transaction (ReadCommitted). And another set is not in a transaction (operator selection).

• In this case, when the first set performs another, it waits.

• If I set READ_COMMITTED_SNAPSHOT for DB Deadlock, it will happen.

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

To solve this problem, do I need to enter Select expressions in TransactionScope?

Answer 1

In SQL Server, each transaction has an implicit or explicit transaction level. Explicit if called with BEGIN/COMMIT/ROLLBACK TRANSACTION , implicit if nothing happened.

Take a snapshot before running the update request. Otherwise, SQL Server will not be able to prepare the modified rows in tempdb, and the Update request is still blocked.

Another way without creating snapshot isolation is to use SELECT <columns> FROM <table> WITH (NOLOCK) , which allows SQL Server to retrieve rows no matter what (for example, READ_UNCOMMITED). Since this is a hint, it changes the isolation level even with your settings. It may work if you are not worried about the state of the row being requested, but care should be taken when evaluating the received data.