Views, Transactions and blocking

[Bandi]

I've been asked to look into a issue that we are experiencing relating to locking / blocking whilst running queries in VB6 / ADO to a SQL Server backend. I don't have a lot of SQL Server knowledge, so please forgive me!

I've simplified the issue down to a repeatable sequence of events... I'd like to understand why the blocking occurs and what options are available to resolve it.

Dim ADOConn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim dbrs As New ADODB.Recordset


Set ADOConn = New ADODB.Connection
ADOConn.ConnectionString = "Provider=SQLOLEDB;Data Source=*****;Initial Catalog=*****;User Id=*****;Password=*****;OLE DB SERVICES=-4"
ADOConn.CursorLocation = adUseClient
ADOConn.Open


ADOConn.Execute "BEGIN TRANSACTION"


With cmd
    .CommandType = adCmdText
    .ActiveConnection = ADOConn
    .CommandText = "CREATE VIEW [SummedBalance] AS Select sum(a_balance) as [Balance] from account"
    .Execute
End With

Set cmd = Nothing


dbrs.Open "Select Balance from SummedBalance", ADOConn, adOpenStatic, adLockOptimistic

....

At the point of running the SELECT statement, everything grinds to a halt. SSMS activity monitor shows a Task state of SUSPENDED and a Wait type of LCK_M_SCH_S against the select statement. It remains in this state until I kill that process, at which point the select statement runs and returns the expected result.

I have been able to get around the blocking in three different ways, but none of them are ideal.
1. Remove the SUM() from the VIEW creation. Not a feasible approach, but why does it work??
2. Remove the BEGIN transaction or moving it to after the VIEW creation. I guess that modifying the schema in a transaction is the base cause, but ideally we need to keep everything together in a transaction. Seems like it should be ok given point 1 above.
3. Change locking to adReadOnly. This prevents the blocking, but isn't suitable for our needs. We also lose access to field properties with this lock type.


Any insight into how to best work with this situation would be greatly appreciated. As mentioned, my SQL Server knowledge is light so there may be something obvious that I'm missing.

Thanks in advance!



As a side note... Above is for example purposes only. The Views that are created are usually far more complex and the entire process can contain several views and multiple update and select steps (Hence why I'd like to keep everything in a transaction).

[Zvoni]

If you begin a Transaction you either have to commit it or rollback (HINT HINT) BEFORE you do anything else (like executing a SELECT-Query)

btw: Don't "execute" a "BEGIN TRANSACTION"
Use the Method of the ADODB.Connection: BeginTrans (Have a guess....)
oh, and the Connection-Object also has "CommitTrans" and "RollbackTrans" (have a guess, too)

and FWIW: Why do you create such a simple View, just to query it afterwards?

Leave the View out, and Query directly

Because if you run your code once, it works (your issues with the blocking not withstanding), but run it a second time, and you get a KABOOM, because the View alredy exists!!
Check out SQL-Syntax for
"CREATE VIEW IF NOT EXISTS....."

As a side note... Above is for example purposes only. The Views that are created are usually far more complex and the entire process can contain several views and multiple update and select steps (Hence why I'd like to keep everything in a transaction).

Doesn't change the fact, that Views should never be created from a running program, just to get a specific perspective on data.
Views are "static" objects in a Database (like a table), and should be part of the Database-Design itself.

[Bandi]

Hi Zvoni,

Thanks very much for the reply on this. Much appreciated.

If you begin a Transaction you either have to commit it or rollback (HINT HINT) BEFORE you do anything else (like executing a SELECT-Query)

Is there a fundamental reason for this? I think this is getting to the core of what I'm trying to understand. If transactions are designed to keep several actions together, why is creation of a VIEW different to say, a Select into or Alter table that also modifies the DB schema? Will a Select into also cause blocking issues in some situations? This is what I'm trying to understand at the moment (without much luck!)

btw: Don't "execute" a "BEGIN TRANSACTION"
Use the Method of the ADODB.Connection: BeginTrans (Have a guess....)
oh, and the Connection-Object also has "CommitTrans" and "RollbackTrans" (have a guess, too)

This is done as an execute to allow support for Savepoints as ADODB with the SQL OleDB provider doesn't support nested transactions to my knowledge. Is there a way to handle save points when going via the ADODB connection method? If so, I will look further into it.

and FWIW: Why do you create such a simple View, just to query it afterwards?

I fear that by simplifying this down to the bare minimum, I've lost a lot of context. What I'm dealing with is a harness of sorts that allows other developers to create their own reports. Those reports can utilise a lot of different functionality including Views, Updates, selects, data imports and custom scripting amongst other things. The VIEW above is the simplest I could come up with that triggers the blocking issue. In reality, the VIEW that bought this to light was a couple of hundred lines long and is reused in multiple places throughout this report. This system has been in place for a long time so I'm constrained in what functional changes can be made without breaking a lot of existing reports. I agree that using VIEWS for this isn't ideal though and they are being done away with in new reports.

Because if you run your code once, it works (your issues with the blocking not withstanding), but run it a second time, and you get a KABOOM, because the View alredy exists!!

Oh, definitely agree. This is all handled currently but left out of my example so I could keep it as simple as possible.


Thanks again.

[Zvoni]

OK, a LCK_M_SCH_S is Schema Stability Lock, which usually occurs, when a long running Statement is blocking anything coming after it.

At a guess: Your DB-Server is not done creating the VIEW when you already try to access it

See here:
https://www.sqlshack.com/sql-server-...e-lck-m-sch-s/
https://stevestedman.com/2025/03/sql...e-lck_m_sch_s/

Obvious solution: Commit the Creation of the View

[Bandi]

Thanks again Zvoni.

I've gone back and confirmed that the VIEW creation has completed by checking for that VIEW prior to running the select.

While looking into that, I came across OPTION (EXPAND VIEWS). Including this in the SELECT statement lets it run without any blocking issues, so I'll do some more reading and see if that will work for us.


Appreciate your help.

[FunkyDexter]

It sounds like Zvoni has resolved your main question but I thought I'd chip in on a few of the side questions you asked:-

the SQL OleDB provider doesn't support nested transactions

This is because SqlServer doesn't support nested transactions - at least, not properly. You can open several transactions but, when you issue a single commit or rollback, they are ALL processed, not just the last one. If you need to nest transactions properly you need to keep track of @@TranCount and use that to decide what to do. It's a bit crap and gets complicated but it's the way SQLServer has always been (I'm not sure that any other database supports proper nesting - it's to do with the way the log works so may not be a soluble problem).

why is creation of a VIEW different to say, a Select into or Alter table that also modifies the DB schema?

It's not. I think the problem is that you're creating the view in one session and querying it on another. You're using the same connection but the view is being created via a command and the query is being issued via a recordset and those two entities aren't associated so the database treats them separately. I think, if you did the whole thing via the command or the result set you would find it worked though I'm not in a position to test that right now. Similarly, as Zvoni said, you could just commit the command first or, as you have done, check for it's existence before querying.

Will a Select into also cause blocking issues in some situations?

EVERYTHING causes blocking because EVERYTHING is a transaction - it's just that sometimes it's not obvious. E.g. "Select Count(*) From My Table" executes in it's own transaction - it's just that it's (probably) a very quick transaction and only applies Shared locks so it doesn't get in the way of anything else. But if you do an update (either schema or data) query it will apply Exclusive locks until the transaction is committed or rolled back. If you do that in a transaction that takes a long time to run (for any reason, it doesn't need to be the actual update) then you will get blocking issues. This is true even of a long running update statement that isn't in an explicit transaction - because it is in an implicit transaction.

The take away is that you need to keep your transactions short. If you can't do that then you have a deeper problem - either your approach or your architecture is wrong. We can help with that but would need more context.

Views should never be created from a running program

This! One thousand times: This! Is there a reason you're creating views on the fly? There's almost certainly something better we can suggest.

[Zvoni]

This! One thousand times: This! Is there a reason you're creating views on the fly? There's almost certainly something better we can suggest.

Yes. It's called CTE's

[RedHeadedLefty]

Hey, CTE! Everything an App dev wants to see!

HOWEVER: Never use them. Unless doing a recursive call. (You will see this as you scale to larger datasets)

WHY? No statistics. Statistics matter. Query plans and optimization. Can get past this replacing with temp tables (a table has statistics!) instead of CTEs.

[wqweto]

Hey, CTE! Everything an App dev wants to see!

HOWEVER: Never use them. Unless doing a recursive call. (You will see this as you scale to larger datasets)

WHY? No statistics. Statistics matter. Query plans and optimization. Can get past this replacing with temp tables (a table has statistics!) instead of CTEs.

Just chime in to say that this makes a lot of sense.

Also "classic" temp tables are different than table variable as these *automatically* get statistics created (if missing) when different query plans are tested/optimized by the query engine.

Table variables and CTEs are more like worktables (internal temporary tables the engine uses for sorting, grouping, etc.) which don't get statistics created on demand. This has a big implication on query plan chosen and ultimately the performance of the final query.

cheers,
</wqw>

[gigemboy]

Thanks for affirmation wqweto. (Just recovered old login)

[Zvoni]

Just chime in to say that this makes a lot of sense.

Also "classic" temp tables are different than table variable as these *automatically* get statistics created (if missing) when different query plans are tested/optimized by the query engine.

Table variables and CTEs are more like worktables (internal temporary tables the engine uses for sorting, grouping, etc.) which don't get statistics created on demand. This has a big implication on query plan chosen and ultimately the performance of the final query.

cheers,
</wqw>

All true.
Though often enough, you are in a situation, you have no influence whatsoever, since your only task is to write a query.

e.g. myself: I'm not a DBA, but i have to write queries for business-reports.
Soooo, no, i can't create an Index. Noooo.... i can't use a temp table. Nooo, i don't have write access (only SELECT).

I like CTE's because they will always work, irrespective if you're DBA or not.

Performance-Penalties not withstanding, but that's not my problem

[gigemboy]

Can't create a temp table? I'd challenge that one.

Just replace CTEs With:

SELECT * INTO #MyTempTableThatNowHasStatistics FROM WhateverMyCTEDid

[Zvoni]

Can't create a temp table? I'd challenge that one.

Just replace CTEs With:

SELECT * INTO #MyTempTableThatNowHasStatistics FROM WhateverMyCTEDid

Yeah, right..... i'd like to see you doing that on a Production IBM-DB2 in a Company with 700 employees operating worldwide.
As a Non-DBA, that would be close to 99% career-suicide

[gigemboy]

Yeah, right..... i'd like to see you doing that on a Production IBM-DB2 in a Company with 700 employees operating worldwide.
As a Non-DBA, that would be close to 99% career-suicide

Well this turned. Who has done you wrong? I'm not here for your therapy. I was kind of assuming we were talking about MS databases here, where creating a simple temp table doesn't appear to bring a whole system down.

I'm not the only one here... just handing out exp for free.

https://www.sqlshack.com/why-is-my-cte-so-slow/

https://www.reddit.com/r/SQLServer/c...n_ctes_go_bad/

Or just ask ChatGPT about it

[Zvoni]

OK, came out wrong. Definitely didn't mean any offense to you and your expertise.

Yes, i know that CTEs are "slow" compared to "real" tables, since, as you correctly pointed out, there are no statistics, nevermind indices.

My point is, that CTEs will always work (provided the DB-Engine supports it --> MySQL5.7 being a "famous" one not supporting it).
That was my crucial point.

But the performance of such a query is in my case not my problem, since i have to use what's available.

I have a such a business-report-query in production (actually hidden within a VBA-Project in Excel),
using some 30-40 CTE across some 5 queries in total (which in itself access some 50-60 base-tables with "i-lost-count-how-many-joins").

When i rolled out that thing...
Me: "That's how you configure it, that's what you have to do to start it"
Users: "OK, sounds easy enough"
Me: "It is, but once you start that thing, go for a coffee-break....."
Users: "It takes that long?"
Me: "Yes. It is as it is. If you think it's too slow, you can take up the discussion with IT-Department...."

[gigemboy]

You are correct in that it does work. Since they are expressions and just need read access to execute. I was just saying it's usually the least performant way of approaching the result (which typically doesn't matter if in cases where its a few seconds or less of things). I do get your point.

[wqweto]

Btw, persisting dervied tables and/or CTEs is the first thing I usually attempt when performance tuning a query in MSSQL.

Another option to "force order" in the execution plan besides temp tables (i.e. first compute this, then do this join) is to use something like SELECT TOP 99999999 ... FROM BaseTable with or without an ORDER BY.

This usually changes the plan radically i.e. the optimizer is forced to create a worktable and cannot push predicates up/down the query tree beyond the ordered sub-query.

cheers,
</wqw>