Error during deploy: Transaction (Process ID) was deadlocked

dnlnln

The following error is logged during deployment of a ReadyRoll database project to an instance of SQL Server 2008:

# Beginning transaction
Changed database context to 'XmlSchemaDeploy'.
***** EXECUTING MIGRATION "Migrations001_DeploySchema.sql", ID: {c4bb4034-30c3-4c01-bdc5-916e5682286f} *****

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Here is the migration T-SQL that resulted in the failure:

-- <Migration ID="c4bb4034-30c3-4c01-bdc5-916e5682286e" />
GO
CREATE XML SCHEMA COLLECTION [dbo].[MyXmlSchemaCollection] 
AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="MyXmlSchema">
    <xsd:complexType>
      <xsd:complexContent>
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="MyXmlElement" maxOccurs="unbounded" />
          </xsd:sequence>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>' 
GO
CREATE PROCEDURE [dbo].[spMyProc]
AS
    BEGIN
        DECLARE @MyTableVar TABLE
            (
              id INT PRIMARY KEY ,
              myXml XML([dbo].[MyXmlSchemaCollection])
            ); 
    END; 
GO

dnlnln

This is a known issue with deploying Xml Schema objects to SQL Server 2008.

To resolve it, the transaction containing the xml schema object must be committed and then a new transaction opened prior to deploying objects that depend on the xml schema (in the above case, a stored procedure).
This can be done by separating the code into 2 different scripts, and inserting a blank script between the two with the following content:

-- <Migration ID="864fb754-45fb-4389-bbf2-b5beee6f8d64" TransactionHandling="Custom" />
GO
PRINT 'Schema changes have been committed. Starting a new transaction for code objects...';
GO

The TransactionHandling="Custom" declaration switches off transaction handling temporarily, to allow it to be reopened by the next script (see the following article for more information on transaction handling in ReadyRoll: https://documentation.red-gate.com/display/RR1/Transaction+Handling )

Here's an example ReadyRoll project that demonstrates this:
https://documentation.red-gate.com/down ... zip?api=v2