I am using .NET 5.0 transactions in ReadUncommited mode:
MyDbcontext.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted)
My understanding is that with this mode, there are no locks on the DB (at the price of users possibly reading records not up to date of course).
But I notice that I DO get locks until this transaction is commited or rolled back. I can see it while triggering a breakpoint before the commit/rollback and performing a SQL request in my MS SQL Studio Manager. The request will just NOT return until the transaction is commited or rolled back.
Is it the way it should work? If so, is there a way to set my transaction with no lock at all? I do not mind reading uncommited records but I nontheless need a transaction to make sure that all DB operations are run or not.
SQL Server still requests schema stability locks when reading under read uncommitted isolation level.
Additionally SQL Server requests exclusive locks when writing no matter the isolation level it runs under.
I recommend you against using read uncommitted isolation level because it produces other problems than uncommitted reads, for example reading twice the same row or skipping to read a row.
Instead of read uncommitted I recommend you to use read committed snapshot or snapshot isolation levels. These isolation levels don't request shared locks when reading while keeping important guarantees.