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:
- the UDT code
- the query in which is was being accessed
- the Linked Server definition
- 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.