CODEWITHSUNDEEP
sql-serverviewtransactions
Frobozz
Frobozz

Reputation: 243

Prevent SQL view from being blocked by lengthy delete/insert transaction using snapshot isolation

I am attempting to prevent "splash pages" on our website, which are generated from SQL views, from blocking during the rather lengthy update of underlying tables.

The update stored procedure utilizes snapshot isolation and the resulting row versioning allows transacted select queries from the tables being updated; returning their values prior to the updating transaction beginning. Wonderful stuff.

Unfortunately, my select queries still appear to be blocked if they are from a view constructed from the underlying tables.

UPDATE: Turns out these queries are not blocked in the traditional sense as there are no requests in WAIT state in sys.dm_tran_locks. But are none-the-less stalled waiting for synchronous statistics to complete before compiling.

Thank you, @Charlieface for exposing this essential piece to using SNAPSHOT isolation for data availability during large update transactions!

Upvotes: 1

Views: 521

Answers (1)

Charlieface
Charlieface

Reputation: 72415

It seems, after working through various options in the comments, that AUTO_UPDATE_STATISTICS_ASYNC was set to OFF.

This meant that a big enough update was causing a synchronous statistics refresh, which in turn blocked other queries from even compiling.

If AUTO_UPDATE_STATISTICS_ASYNC is set to ON then currently compiling queries do not benefit from the statistics update (a minor disadvantage), however there is no blocking because the statistics are refreshed asynchronously.

One more downside if it is ON is that you cannot access the database in single-user mode, although that case is obviously very rare, and you can just turn it off for that one time.

See more in this great article, there are more very rare circumstances when you want it OFF

Upvotes: 1

Related Questions