Parameterized DELETE and MERGE Allow Foreign Key Constraint Violations - by Paul White NZ

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


29
0
Sign in
to vote
ID 685800 Comments
Status Closed Workarounds
Type Bug Repros 10
Opened 8/26/2011 5:51:13 AM
Access Restriction Public

Description

Scenario:

1.  There are two tables in a FK relationship, the FK constraint is trusted and enabled.
2.  BEGIN a transaction.
3.  Set the FK to NOCHECK
4.  Cache a plan for a DELETE or MERGE statement
5.  The plan is parameterized with an ad-hoc stub
6.  ROLLBACK the transaction.  (The FK is trusted and enabled.)
7.  The non-FK-checking plans are not evicted from the plan cache.
8.  Future queries that parameterize to the form in cache allow DML that violates the FK.

Thanks to Jack Corbett, who first demonstrated this bug on SSC:
http://www.sqlservercentral.com/Forums/Topic1165693-391-1.aspx
Sign in to post a comment.
Posted by Paul White NZ on 2/11/2013 at 11:30 PM
Understood, thanks for looking into it.
Posted by Microsoft on 2/11/2013 at 10:54 PM
thanks again for reporting this condition. Given the nature of changes needed to address this issue we'll decided to not risk changes in current release but will re-evaluate for future development activities.

thanks.
Posted by ta.speot.is on 1/6/2012 at 8:35 PM
I was browsing the SQL Server Feedback and read this entry. I'm not affected by this bug but I frequently mix DDL and DML in the order described here, in addition to enabling and disabling constraints within a transaction. So +1 for common behavior...
Posted by Catadmin on 8/31/2011 at 9:10 AM
I have to agree that this scenario is far more common than you might think. I think many people are having unknown FK issues because they haven't gone to the extent Jack went to in order to validate the data. They just "set it and forget it," trusting that the transaction will take care of everything and that they don't need to go back and double-check for orphaned records.
Posted by Jack Corbett on 8/31/2011 at 5:32 AM
As the person who "first demonstrated this bug" I have to comment. I think this scenario is more common than you'd think. I found this issue because I was tasked to purge old data and the performance was unacceptable partially due to checking FK's. By disabling the FK's and then re-enabling them within the transaction performance was improved by orders of magnitude. I wrapped the DDL and DML in a transaction because if I missed something that would cause the enabling of the FK's to fail, I don't want the deletes to be persisted and I don't want the FK's left as disabled or enabled and not trusted. Leaving it in that state would require a restore of the database. I know I'm not the only one who wouldn't want to be left in this state, and wrapping it all in a transaction keeps that happening.
Posted by Microsoft on 8/30/2011 at 6:16 PM
Hello Paul,
Thanks for reporting the issue. The problem is due to how we track dependencies between objects and the plan. The fix is non-trivial for a service pack so we will consider it for a future version of SQL Server. Since this is not a common scenario or use case i.e., doing DDL changes and DML in the same transaction we don't see this as a big issue.

--
Umachandar, SQL Programmability Team
Posted by Catadmin on 8/26/2011 at 6:00 AM
Reproduced with SQL 2008 SP1.