What is READ_COMMITTED_SNAPSHOT?

Asked

Viewed 1,291 times

2

What is READ_COMMITTED_SNAPSHOT EF6 Transaction Support About Anyway? What is your use within the Entity Framework?

.net entity-framework entity-framework-6 terminology software-engineering

  • Thank you for -1, could you explain the reason ? the question is not objective ? not within the community context ?

2 answers

2


This is a SQL Server configuration. It determines that everything that occurs in the current transaction will consider the values that exist at the beginning of it. Any change to any data made by another transaction will not be considered by that transaction if it has to interact with that new value, so it will continue to manipulate the old value.

Data is considered only committed, not the one that is still being processed by the other transaction. Neither reading, nor writing of other transactions are blocked by the current transaction. The serializable option is safer, but it causes a lot of containment by locking may cause Dead Locks, can be very slow to do something if there is competition.

There are other intermediaries.

This may be useful in some cases, in others you may have a running condition, as there are situations where it is essential to have the most up-to-date information possible. A change made by another transaction somewhere that this transaction needs to handle will cause the transaction to be redone, this can even occur infinitely.

This option reduces the chances of the transaction being aborted if you can pay the price for it (not having the most up-to-date data).

To documentation shows existing levels. Have a good article with more details on the subject (all his articles are valuable.

In EF, as far as I know, it’s just a way to set this up in the database. Obviously this is specific to SQL Server. I can not say if there is some form of conversion to other databases that have similar feature, but I believe that not, for me it does not make much sense to do with something so specific.

What I can say is that EF6 has adopted it by default in version 6 when using Code First (which is recommended), so the default is to be more scalable, and less secure. When the database is first created it will be configured.

Remembering that security is not always necessary, and in this context security refers only to the operation do what is expected, nothing to do with invasions or anything like.

The proper choice depends on each case. I prefer the safest before, and the most scalable after, if I have to and I can ensure that there will be no problems.

It is possible to configure the use in each transaction.

  • Yes, in that article, vi que era para o SQL SERVER onde o autor diz (If that database is SQL Server, EF is now Aligned with a "best Practice" for SQL Server Databases, which is to configure the database’s READ_COMMITTED_SNAPSHOT Setting to ON.)

  • 2

    The EF Core this is changing, if I was obliged to use a complete ORM, the EF Core I would even use, they finally understood what works in this type ide software, others I would not use, but it is what I think according to my experience, obviously many people think different.

  • 1

    It is important to note that no matter what kind of isolation transactions use, it also doesn’t matter if the SNAPSHOT feature is enabled or not, changing a record always blocks this record for changes by other transactions until the transaction that changed it completed; the handling of a record already changed by another transaction still pending will always need to wait for the completion of that transaction. So that SNAPSHOT and isolation level does not prevent Dead Locks. Note: this SQL Server SNAPSHOT feature is also present in most other Rdbmss market.

  • @Caffé is true that does not prevent, it avoids a type only.

1

One of the items listed in EF6 specifications is:

  • Standard transaction isolation level is changed to READ_COMMITTED_SNAPSHOT for databases created using Code First, potentially allowing more scalability and fewer deadlocks.

According to Nick Beradi, in his blog, had READ_COMMITTED_SNAPSHOT ON for SQL Server database, means:

Basically what it does is create a snapshot or database Read-only of your current results which is separate from your database live data. Therefore, when you perform an instruction SELECT, to read your data, you are reading from a copy read-only from your database. When you change your database data, it happens in the live database and then a new copy or snapshot is created for reading against.

That one stretch, in addition to a good explanation shows how to enable READ_COMMITTED_SNAPSHOT:

Database administrators control settings on database level for line version control using the database options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION in instruction ALTER DATABASE.

When the database option READ_COMMITTED_SNAPSHOT is defined as ON, the mechanisms used to offer are activated support the option immediately. When setting the option READ_COMMITTED_SNAPSHOT, only the connection that executes the command ALTER DATABASE is allowed in the database. There should be no other open connection in the database until ALTER DATABASE is completed. The database does not need to be in user mode single.

The following instruction Transact-SQL enabling READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2008R2
    SET READ_COMMITTED_SNAPSHOT ON;


References:

Browser other questions tagged .net entity-framework entity-framework-6 terminology software-engineering

You are not signed in. Login or sign up in order to post.