What is READ_COMMITTED_SNAPSHOT?

1

What is READ_COMMITTED_SNAPSHOT transaction support for EF6 About Anyway? What is your use within the Entity Framework?

    
asked by anonymous 04.11.2016 / 13:16

2 answers

2

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

Only committed data , not what is still being processed by the other transaction. Neither reads, nor writes of other transactions are blocked by the current transaction. The serializable option is safer, but causes too much contention by locking and can cause dead locks , you can be very slow to do anything if there is competition. There are other intermediaries.

This may be helpful in some cases, others may have a race condition , as there are situations it is crucial to have the most up-to-date information possible. A change made by another transaction to something that this transaction needs to handle will cause the transaction to be redone, this may even occur infinitely.

This option reduces the chances that the transaction will be aborted if you can pay the price (do not have the most up-to-date data).

The documentation shows existing levels . You have a nice article with more details on the subject (all articles of it 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 do not know if there is any way to convert to other databases that have similar functionality, but I do not think so, it does not make much sense for me to do something so specific.

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

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

The appropriate choice depends on each case. I prefer the safest before, and the more scalable later, if need be and I can guarantee that there will be no problems.

You can configure usage for each transaction.

    
04.11.2016 / 13:48
1

One of the items listed in the EF6 specifications is:

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

According to Nick Beradi, in his blog , have READ_COMMITTED_SNAPSHOT ON to bank SQL Server data, means:

  

Basically what this does is create a snapshot or database   only read from your current results that is separate from your bank   of live data. Therefore, when you execute a statement    SELECT , to read your data, you are reading from a copy   only read from your database. When you change your bank   data, it happens in the live database, and then a new one   copy or snapshot is created for reading against.

This snippet , plus a good explanation shows how enable READ_COMMITTED_SNAPSHOT :

  

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

     

When the READ_COMMITTED_SNAPSHOT database option is   defined as ON , the mechanisms used to offer   support the option immediately. When setting the    READ_COMMITTED_SNAPSHOT , only the connection executing the ALTER DATABASE command 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   unique.

     

The following Transact-SQL statement enables    READ_COMMITTED_SNAPSHOT :

ALTER DATABASE AdventureWorks2008R2
    SET READ_COMMITTED_SNAPSHOT ON;


References:

04.11.2016 / 14:09