Let us look at a very simple example, in which I create an XML schema collection, and then create something that uses it as part of its schema (in this case it is a table returning function that has a strongly typed XML column). Note that such a table is not completely uncommon J, I have seen customer databases that have this.

BEGIN TRANSACTION a
go
CREATE XML SCHEMA COLLECTION [dbo].[xmlSchemaCollectionA]
AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://belokosztolszki/xx1" targetNamespace="http://belokosztolszki/xx1" elementFormDefault="qualified">
  <xsd:complexType name="complextType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Blah" type="xsd:string" />
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
</xsd:schema>'
go 

CREATE FUNCTION f ()
RETURNS @ret TABLE (a int, b XML(CONTENT [dbo].[xmlSchemaCollectionA]))
AS BEGIN
INSERT INTO @ret VALUES (1, CAST(N'<blah/>' AS XML([dbo].[xmlSchemaCollectionA])))
RETURN
END
go

COMMIT

The result under SQL Server 2005 RTM is what one would expect, i.e. both objects are created. Under SP1 however the following greets the user:

Msg 1205, Level 13, State 55, Procedure f, Line 3
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Funny, there is nothing else running. Unfortunately XML schema collections are not the only things that can cause this problem. User defined types may cause self deadlocking transactions as well. Microsoft apparently introduced this behaviour on purpose. But it does make life more difficult. Particularly for the scripts SQL Compare generates. These generated scripts migrate changes from one database to another, and need to perform operations similar to the above example. These scripts could be rather complicated, and in order to avoid inconsistent database states, we encapsulate them into a single transaction, which under SP1 may deadlock on itself, and thus may not be executed. This is rather uncomfortable, and the only workaround avaialble is to break up the transaction into several transactions, and separate the UDT or XML schema definiton from its use. Our hope is SP2 :).

Andras