Error during deploy: Transaction (Process ID) was deadlocked

dnlnlndnlnln Posts: 234 Gold 2
edited August 15, 2016 5:15AM in ReadyRoll
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" />
CREATE XML SCHEMA COLLECTION [dbo].[MyXmlSchemaCollection] 
AS N'<xsd:schema xmlns:xsd="">
  <xsd:element name="MyXmlSchema">
        <xsd:restriction base="xsd:anyType">
            <xsd:element name="MyXmlElement" maxOccurs="unbounded" />
        DECLARE @MyTableVar TABLE
              id INT PRIMARY KEY ,
              myXml XML([dbo].[MyXmlSchemaCollection])
Daniel Nolan
Product Manager
Redgate Software


  • 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" />
    PRINT 'Schema changes have been committed. Starting a new transaction for code objects...';
    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: )

    Here's an example ReadyRoll project that demonstrates this: ... zip?api=v2
    Daniel Nolan
    Product Manager
    Redgate Software
Sign In or Register to comment.