question

shodgin avatar image
shodgin asked

CLR UDT blocking with sch-m (possible linked server issue)

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# CLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without success on a solution. 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# CLR 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 SCHEMA_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_M locks while merely selecting the column and specifying one of the underlying properties. I’d appreciate any suggestions you might have Thanks,
blockingclruser-defined-function
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.