CODEWITHSUNDEEP
sql-serverentity-framework
astralmaster
astralmaster

Reputation: 2465

SQL Server transaction locks

When I execute a SQL query using Entity Framework that creates a transaction and locks a certain table to perform a time consuming operations on it, what happens when another application tries to read from the same table at the same time?

Will SQL Server wait for the first transaction to finish and proceed when the lock is gone or will it simply return with an error saying that the table is inaccessible?

Upvotes: 0

Views: 5977

Answers (3)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89101

By default EF will create SQL Server databases with READ COMMITTED SNAPSHOT on. This means that writers don't block readers and readers don't block writers. For details on Row-Version-based Isolation Levels see Snapshot Isolation in SQL Server.

While SaveChanges() has rows locked in a transactions, other sessions attempting to read those rows will be redirected to the version store, and read the last-known-good version of those rows. In other words readers see each table at a consistent point-in-time just before any in-flight transactions.

If you use EF against an existing database, it might not be configured with READ COMMITTED SNAPSHOT, and in that case readers attempting to read a row locked by a transaction will block until the end of the transaction. Normally this is only a few milliseconds, unless there are a large number of transactions running and the reader is trying to scan a lot of rows, or there are longer-running transaction. In that case you should work to move the database to READ COMMITTED SNAPSHOT in the future.

Upvotes: 2

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

Yes, Entity Framework is creating a transaction automatically when SaveChanges is called and the default isolation level is Read Committed (actually it is fetched from the provider and SQL Server's default is Read Committed).

More details can be found within this documentation page.

In order to change isolation level, you can inhibit EF's context to use it's own transaction (contextOwnsConnection: false) when instantiating the context.

My preferred alternative is to use TransactionScope as it does not require to make any changes to the way you instantiate the context, save the changes etc. However, beware that its default isolation level is Serializable (referenced question and answer also show you how to change it)

NOTE: having long transactions is not recommended, maybe you should take a look upon using bulk operations (or check this library) to avoid multiple inserts/updates/deleted generated by normal changes save.

Upvotes: 1

Xedni
Xedni

Reputation: 4695

The answer is, it depends. There are any of a number of ways SQL can lock data. Generally speaking, if you have a DML operation (Insert, Update, Delete) going on, and you attempt to select from that table, it will block the select until the DML operation completes.

That can be overridden (at your own peril) by either using with (nolock) or set transaction isolation level read uncommmitted. Doing one of those basically allows your query to read "whatever the data currently looks like" which could be incomplete, or even contain rows which, by the time the transaction would have completed, won't exist.

The only time it would return an error is if your actual connection timed out (which would be something in your applications connection settings) or if you caused a deadlock on the table (a much more complicated subject).

Upvotes: -1

Related Questions