SQLCLR User-Defined Type (UDT) SCH-M blocking

Question

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# SQLCLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without success on a solution.

Also, this "might" be a linked server issue as we see linked server queries accessing the UDT seem to block queries (sch-m running on the remote server).

Back story:

Back in 2006, we began porting some of our DB2 processes down to SQL Server 2005 (we’ve upgraded to SQL 2008 since then, but still need to use our UDT for a lot of current processes). Under DB2, we had made extensive use of the DB2 TIMESTAMP data type -- CCYY-MM-DD.HH.MM.SS.mmmmmm -- which has precision down to the microsecond. Because SQL Server, at that time, did not have DATETIME2, we were forced to create our own C# SQLCLR UDT to emulate the DB2 TIMESTAMP. The UDT was created as VARBINARY with properties that allowed us to reference things like:

            Column.Timestamp        (CCYY-MM-DD.HH.MM.SS.mmmmm)
            Column.Date                     (CCYY-MM-DD)
            Column.Microsecond     (mmmmmm)
            Etc.

When multiple processes access the UDT with just the column name (without referencing a specific property), we don’t seems to have problems.

However, when we have multiple processes accessing the UDT and reference one of the properties (.Timestamp, etc.), we encounter Sch-M locks and other processes also accessing that UDT are forced to wait.

I’ll admit that I have limited knowledge on all things CLR and UDT.

I simply do not understand why we’re getting Schema Modification locks while merely selecting the column and specifying one of the underlying properties.

I’d appreciate any suggestions you might have.

Thanks,

Answer 1

When you use SQL CLR objects across databases, SQL Server will automatically convert them if certain conditions are met (https://technet.microsoft.com/en-us/library/ms178069(v=sql.105).aspx).

In my case, this automatic conversion was taking out the SCH-M locks. I fixed the problem by manually converting the contents of my SQL CLR type to binary, and passing it to the second db as varbinary(max). I then deserialized back to my CLR type in the second db.

Answer 2

It makes sense to have a Schema lock (on the UDT itself) when accessing any of its properties or methods, but not when accessing the base VARBINARY value. When accessing the base value, it is simply a VARBINARY set of bytes that does not require any special interpretation and/or handling. But, when accessing any of the properties or methods of the User-Defined Type (UDT), it accesses the underlying VARBINARY bytes and returns its interpretation of that value based on the code for that property or method (and potentially even changes the underlying VARBINARY value). Without the Schema-lock (on the UDT), it would be possible to change the definition of the UDT via ALTER ASSEMBLY while a query that is referencing one of those properties or methods is running, which would allow for the potential to change the interpretation of the persisted value across rows within a single result set, giving you inconsistent, or at least unreliable, results.

That being said, I have been unable to reproduce the scenario for getting a Sch-M (Schema Modification) lock. I have tried numerous variations of the Table with the UDT column being in the "current" Database, in another Database, or accessed remotely via OPENQUERY, along with being an individual statement and being in an explicit Transaction. I also tried both selecting the UDT via one of its methods, and doing an UPDATE of the UDT column. I even tried adding the UDT to another Database, made Database2 the "current" Database, declared a local variable for the UDT, and inserted that UDT variable into the Table in Database1. All variations resulted in only a Sch-S (Schema Stability) lock being taking on the UDT.

The only things I can think of at the moment (and that I haven't tried) that might possibly explain a Sch-M lock are either:

• Calling a method marked as "mutator" (which I am doubting since you only mentioned selecting the property).
• Not signing the Assembly (i.e. not giving it a "strong name"). I have seen other cases where there are benefits to signing the Assembly, even if it will never be marked as either EXTERNAL_ACCESS or UNSAFE. If this is the case, and ends up being the cause, then I suspect the overall reason being that the extra precaution was necessary due to SQL Server not having an internal way to guarantee the definition of the UDT without that signature.
• Something was different in SQL Server 2008. I don't think this would make a difference (I assume not, but it also can't be ruled out without testing), but I tested on SQL Server 2012 SP3, and this issue was reported using SQL Server 2008.

---

I have now tested this using an unsigned Assembly, and in SQL Server 2008 R2 (with the unsigned Assembly) local-only / no Linked Server, and in SQL Server 2005 SP4 (also with the unsigned Assembly) both local and over a loop-back Linked Server. The only Schema lock taken in any scenario is a Sch-S lock. I cannot see any way that using a UDT would take out a Sch-M lock, nor any reason why it would since the Sch-S lock does what is needed.

I suspect that something else was going on at the same time that the UDT was being accessed, and it just looked like it was the UDT that was the issue. Either that or the UDT was doing more than simple DateTime operations. In order to figure this out, it would require:

1. the UDT code
2. the query in which is was being accessed
3. the Linked Server definition
4. looking at the specific lock-resource that was getting the Sch-M lock.

Unfortunately, it sounds like this environment (and possibly the issue itself) is no longer around to get such information from.