locked
Isolation level not working RRS feed

  • Question

  • Hello,

    I am using sql server  2012 sp3 standard edition. I noticed we were receiving too many deadlocks on a database where updates or inserts were deadlocking the selects. Later the isolation level of the database has been modified to Read Committed Snapshot isolation. However when I look at the incoming queries their isolation levels are still showing as Serializable and Read committed. As far as I remember for RCSI, code changes are not required as sqlserver forces the queries to use RCSI once the database has been altered to RCSI. This is indicating that the optimistic concurreny is not being used.

    Any ideas why this is happening?

    Thanks a ton

    Thursday, February 22, 2018 3:53 PM

Answers

  • You cannot "change the database isolation level to snapshot".

    You can reconfigure the behaviour of read committed. The default behavious is the blocking, but you can change it to a versioning behaviour by setting the database option READ_COMMITTED_SNAPSHOT to on.

    You can also *allow" clients to use the islation level whcih is named SNAPSHOT. You do this by setting the database option ALLOW_SNAPSHOT_ISOLATION to on.

    A client connection defaults to READ_COMMITTED. It can override the default using SET TRANSACTION ISOLATION LEVEL command. A client library/API can of cause also override the default.

    So, there no such setting as "set the isolation level" at the database level.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, February 23, 2018 2:18 PM
  • if you change the isolation level of the database to snapshot or read committed snapshot it will enforce any transaction to RCSI? 

    I think Tibor answered this question. The way I think about it is that row versioning is used only with SNAPSHOT and with READ COMMITTED in the context of a RCSI database. Locking is always used in SERIALIZABLE and REPEATABLE READ. No locking (sans schema stability) occurs with READ UNCOMMITTED.

    In your case with the app using SERIALIZABLE, index and query tuning is especially important to avoid deadlocks.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 23, 2018 7:42 PM
    Answerer
  • Turning on the READ_COMMITTED_SNAPSHOT database option does not change the session isolation level. It only changes the method SQL Server uses to provide read consistency for READ_COMMITTED sessions, using row versioning instead of locking.

    SERIALIZABLE transactions will use locking as before. With READ_COMMITTED_SNAPSHOT on, READ_COMMITTED and SERIALIZABLE transactions will block one another (and potentially deadlock) when the same resources are locked during updates.

    SELECT queries using READ_COMMITTED with READ_COMMITTED_SNAPSHOT ON should not block other sessions regardless of the transaction isolation level. If you see that in a deadlock, post the deadlock graph.

    Note that SERIALIZABLE is prone to deadlocks in general and should be avoided unless required.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Thursday, February 22, 2018 4:43 PM
    Answerer
  • It is generally best to use READ_COMMITTED unless you have a specific reason not to. Knowing nothing about the application, I can't say if SERIALIZABLE is deliberate or an oversight. SERIALIZABLE is sometimes used accidentally when using TransactionScope in .NET, which defaults to SERIALIZABLE.

    SERIALIZABLE might be used intentionally to ensure SELECT statement results do not change in a multi-statement transaction until committed, but at the cost of increased blocking and deadlocking. Query and index tuning can mitigate the likelihood of deadlocks by touching (and locking) only the data needed by a query. That's especially important when using SERIALIZABLE.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 23, 2018 1:43 AM
    Answerer
  • Perhaps SNAPSHOT isolation can be en option, where READ COMMITTED (with the snapshot behaviour) isn't enough?

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, February 23, 2018 7:58 AM

All replies

  • Turning on the READ_COMMITTED_SNAPSHOT database option does not change the session isolation level. It only changes the method SQL Server uses to provide read consistency for READ_COMMITTED sessions, using row versioning instead of locking.

    SERIALIZABLE transactions will use locking as before. With READ_COMMITTED_SNAPSHOT on, READ_COMMITTED and SERIALIZABLE transactions will block one another (and potentially deadlock) when the same resources are locked during updates.

    SELECT queries using READ_COMMITTED with READ_COMMITTED_SNAPSHOT ON should not block other sessions regardless of the transaction isolation level. If you see that in a deadlock, post the deadlock graph.

    Note that SERIALIZABLE is prone to deadlocks in general and should be avoided unless required.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Thursday, February 22, 2018 4:43 PM
    Answerer
  • The deadlocks I am seeing are between update and select where select is the victim and for both the isolation level is shown as Serializable. But shouldnt RCSI avoid these deadlocks or support writers doesnt block readers. I understand if the deadlocks were between 2 updates.
    Thursday, February 22, 2018 9:15 PM
  • I think the application is using isolation level 'Serializable' by default. So should that be changed to readcommitted in the code to avoid the update and select deadlocks?
    Thursday, February 22, 2018 10:01 PM
  • It is generally best to use READ_COMMITTED unless you have a specific reason not to. Knowing nothing about the application, I can't say if SERIALIZABLE is deliberate or an oversight. SERIALIZABLE is sometimes used accidentally when using TransactionScope in .NET, which defaults to SERIALIZABLE.

    SERIALIZABLE might be used intentionally to ensure SELECT statement results do not change in a multi-statement transaction until committed, but at the cost of increased blocking and deadlocking. Query and index tuning can mitigate the likelihood of deadlocks by touching (and locking) only the data needed by a query. That's especially important when using SERIALIZABLE.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 23, 2018 1:43 AM
    Answerer
  • Perhaps SNAPSHOT isolation can be en option, where READ COMMITTED (with the snapshot behaviour) isn't enough?

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, February 23, 2018 7:58 AM
  • OK. I havent found a resource but is it true that irrespective of .net code (application) transactionscope (including serializable)  if you change the isolation level of the database to snapshot or read committed snapshot it will enforce any transaction to RCSI? 
    Friday, February 23, 2018 1:57 PM
  • You cannot "change the database isolation level to snapshot".

    You can reconfigure the behaviour of read committed. The default behavious is the blocking, but you can change it to a versioning behaviour by setting the database option READ_COMMITTED_SNAPSHOT to on.

    You can also *allow" clients to use the islation level whcih is named SNAPSHOT. You do this by setting the database option ALLOW_SNAPSHOT_ISOLATION to on.

    A client connection defaults to READ_COMMITTED. It can override the default using SET TRANSACTION ISOLATION LEVEL command. A client library/API can of cause also override the default.

    So, there no such setting as "set the isolation level" at the database level.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, February 23, 2018 2:18 PM
  • if you change the isolation level of the database to snapshot or read committed snapshot it will enforce any transaction to RCSI? 

    I think Tibor answered this question. The way I think about it is that row versioning is used only with SNAPSHOT and with READ COMMITTED in the context of a RCSI database. Locking is always used in SERIALIZABLE and REPEATABLE READ. No locking (sans schema stability) occurs with READ UNCOMMITTED.

    In your case with the app using SERIALIZABLE, index and query tuning is especially important to avoid deadlocks.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 23, 2018 7:42 PM
    Answerer