none
blocking issue RRS feed

  • Question

  • one of my insert query is blocking select query, how can i avoid block 

    1) i dont wanna use nolock due to dirty reads issue.

    2) second option is below.

    ALTER DATABASE DB_name SET READ_COMMITTED_SNAPSHOT ON with rollback immediate

    ALTER DATABASE DB_name  SET ALLOW_SNAPSHOT_ISOLATION ON 

    I can change above setting of isolation level of database.

    but whether it will impact my other queries ?? 

    any other solution which can be applied query level only?


    SQL Server DBA

    Monday, January 30, 2017 10:24 AM

Answers

  • Hello Zeal DBA,

    Before applying the options you mentioned, you first need to focus on tuning the queries.

    - Is the SELECT query written so that it can take advantage of an index?

    - If so, is there any useful index for that SELECT query?

    - If there's any redundant, unused and unnecessary index on the table where the blocking issue occurs, they need to be removed after documenting them.

    - Does the SELECT query and Insert query has to be executed at the same time if they are scheduled jobs? If not, then change their schedule.

    - If SELECT and/or INSERT queries run in batches, is there any possibility to break them?

    - If none of the above is an option or solution, then you can test Read Commited Isolation Level.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Monday, January 30, 2017 12:37 PM

All replies

  • Hi,

    1) Either you COMMIT or ROLLBACK transaction

    2) KILL the session which is causing to blocking and you find the session id by running sp_who command.



    Ramesh. M

    Monday, January 30, 2017 10:29 AM
  • i need both the transactions to get successful means need concurrency.

    SQL Server DBA

    Monday, January 30, 2017 10:42 AM
  • Hello Zeal DBA,

    Before applying the options you mentioned, you first need to focus on tuning the queries.

    - Is the SELECT query written so that it can take advantage of an index?

    - If so, is there any useful index for that SELECT query?

    - If there's any redundant, unused and unnecessary index on the table where the blocking issue occurs, they need to be removed after documenting them.

    - Does the SELECT query and Insert query has to be executed at the same time if they are scheduled jobs? If not, then change their schedule.

    - If SELECT and/or INSERT queries run in batches, is there any possibility to break them?

    - If none of the above is an option or solution, then you can test Read Commited Isolation Level.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Monday, January 30, 2017 12:37 PM
  • >>>but whether it will impact my other queries ?? 

    Read_committed_snapshot is the database option that affects behavior of the readers in READ COMMITTED isolation level. When you enable this option on database level (requires exclusive db access to change it), all your readers in READ COMMITTED isolation level will start to use row-versioning reading old(committed) versions from the version store rather than being blocked by (S)/(X) locks incompatibility. This option does not require any code changes (assuming you readers are using READ COMMITTED). It would not change behavior of writers (writers still block each other) nor behavior of readers in any other transaction isolation level.

     

    Snapshot isolation level is full blown transaction isolation level. It needs to be explicitly specified in the code. Enabling snapshot isolation level on DB level does not change behavior of queries in any other isolation levels. In that option you are eliminating all blocking even between writers (assuming they do not update the same rows) although it could lead to 3960 errors (data has been modified by other sessions).

     

    Speaking of consistency, RCSI gives you statement level consistency – readers ignores the data changes done after statement has been started. Snapshot – transaction level consistency – session deals with “snapshot” of the data at the moment transaction started. Again, it could lead to error 3960 in the system with volatile data.

     

    Downsides:

    1. Excessive tempdb usage due to version store activity. Think about session that deletes 1M rows. All those rows must be copied to version store regardless of session transaction isolation level and/or if there are other sessions that running in optimistic isolation levels at the moment when deletion started.
    2. Extra fragmentation – SQL Server adds 14-byte version tag (version store pointer) to the rows in the data files when they are modified. This tag stayed until index is rebuild
    3. Development challenges – again, error 3960 with snapshot isolation level. Another example in both isolation levels – trigger or code based referential integrity. You can always solve it by adding with (READCOMMITTED) hint if needed.

     

    While switching to RCSI could be good emergency technique to remove blocking between readers and writers (if you can live with overhead AND readers are using read committed), I would suggest to find root cause of the blocking. Confirm that you have locking issues – check if there are shared lock waits in wait stats, that there is no lock escalations that block readers, check that queries are optimized, etc.  

     


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 31, 2017 8:07 AM
    Moderator
  • If i use this on database level

    ALTER DATABASE DB_name  SET ALLOW_SNAPSHOT_ISOLATION ON 

    whether this will change behavior of my other transactions in terms of performance? 


    SQL Server DBA

    Tuesday, January 31, 2017 9:03 AM
  • It might. Whether you will notice it or not, we can't tell. Only you can, after measiring the difference for your real workload. Modifications will have to first store the prior valeue for the row, in tempdb. And SELECT will possibly have to follow a pointer for the row, to tempdb, if a prior version need to be accessed (but the alternative to that would be to be blocked, sio this is in reality improving performance).

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, February 1, 2017 12:00 PM