none
DELETE transaction with SNAPSHOT isolation level - conflicts another table RRS feed

  • Question

  • Hi,

    we are executing the following query in a stored procedure using snapshot isolation level:

    DELETE FROM tBackgroundProcessProgressReport
    FROM         tBackgroundProcessProgressReport LEFT OUTER JOIN
                          tBackgroundProcess ON
                          tBackgroundProcess.BackgroundProcessProgressReportID = tBackgroundProcessProgressReport.BackgroundProcessProgressReportID LEFT OUTER JOIN
                          tBackgroundProcessProgressReportItem ON
                          tBackgroundProcessProgressReport.BackgroundProcessProgressReportID = tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportID
    WHERE     (tBackgroundProcess.BackgroundProcessID IS NULL) AND
                          (tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportItemID IS NULL)


    The query should delete records from tBackgroundProcessProgressReport which are not connected with the other two tables.
    However, for some reasone we get the following exception:

    System.Data.SqlClient.SqlException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.tBackgroundProcess' directly or indirectly in database 'RHSS_PRD_PT_Engine' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

    The exception specifies that we are not allowed to update/delete/insert records in tBackgroundProcess, but the query indeed deletes records from tBackgroundProcessProgressReport, not from the table in the exception.
    Is the exception raised because of the join?

    Has someone encountered this issue before?

    Thanks,

    Yani
    Thursday, November 29, 2007 12:56 PM