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.
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# 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,
Aucun commentaire:
Enregistrer un commentaire