Sql-server – Why does querying for records that violate a foreign key return incorrect results

foreign keysql-server-2012stored-proceduresuniqueidentifier

While doing some pre-checking on a table before adding a new Foreign Key, we were querying to see how many current rows would violate the new key. This is a fairly active database with near constant inserts on the tables in question.

The FK would be from MessagePatientIdentifier.MessageID to Message.MessageID.

The query we used was as simple as could be:

select * from MessagePatientIdentifier as mpi
where
    MessageID not in (select MessageID from Message)

The issue that we are seeing, is that rows are being returned from MessagePatientIdentifier

MessagePatientID                     | MessageID 
553bde76-47d4-4ec3-96d1-b5d2e98931e1 | 7d45464d-8cc4-4a2e-8828-020722165b39

in this case and when you then select * from Message where MessageID = 7d45464d-8cc4-4a2e-8828-020722165b39, the record does in fact exist.

We then moved on and tried the same query, but on a table that DOES have a FK in place, referencing Message table in the same way. The same results…the query is reporting that Child table records exist without parent the corresponding parent table (Message) record.

MessageRecipID                       | MessageID 
26d6d632-87b3-407e-aeb0-04552981e5f8 | 750f0fb4-3e6c-485d-996e-f061f8caa360

Then, once again if you select * from Message where MessageID = 750f0fb4-3e6c-485d-996e-f061f8caa360, this returns the record.

This data comes in via stored procedures from a Mirth Server as well as through a BizTalk WCF-SQL send port. The proc inserts the message record, gets the new uniqueidentifier as an output variable and then uses that to call auxiliary stored procs to insert into both the MessagePatient.MessageID and MessageRecip.MessageID.

Is this expected behaviour and I'm just not educated on the inner workings of SQL? Technically I believe BizTalk runs everything in a transaction, so it shouldn't get out of sync, even if it did where would it get the MessageID value if not from the Message insert?

What am I missing here?

Best Answer

This is a fairly active database

If you can restore a static backup somewhere, and do analysis on that unchanging copy, I think you'll find that the weird behavior you're seeing goes away.

Read Committed

The default isolation level in SQL Server is READ COMMITTED. Under this isolation level, you read the data that is currently committed to the database. The only guarantee that it provides is that when SQL Server reads a given page, it will never return an uncommitted result (known as a dirty read).

In your case you're scanning two tables and comparing them. While SQL Server is doing that scan, there is data movement.

You read the first few pages of data and have the first bit of data.
Some of the rows you haven't read yet are updated. Perhaps the MessageSubject or some other column is updated to a longer value, and this results in a page split. Or perhaps the key column is updated and the row is physically moved "up" the index into the portion you already read.
In both cases, it's possible for a row (or many rows) to move from a place you haven't yet read to a place you already read. You'll never see the row because it moved around. This causes the row to be "missing" from your scan.
It's also possible a row moves in the other direction: from a place you've already read to a spot you haven't gotten to yet. In this case, you'll see that row twice and your results will have a mysterious double result.

Your examples use integers, but in your question you mention uniqueidentifier. Since uniqueidentifiers are random, the randomness means rows are constantly being inserting into random places on the table. This increases the chances that a page fills up and has to be split to accommodate the new page, and increases the chances you see these phenomena.

Read the section titled "Locking Read Committed Behaviours" in the above linked article for a thorough explanation.

The fix?

If you want to avoid these off phenomena, don't use the default isolation level in SQL Server. My favorite & default isolation level is READ COMMITTED SNAPSHOT, which avoids these problems. (Read about all the isolation levels here to decide which one is right for you.)

Or if it's just for a one-time analysis, you could use a database snapshot to create a static image to query, or you could stop writing to the database, or restore a copy elsewhere. Stopping writes will stop data movement, and you won't run into the problem.

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt

Mysql – Using Foreign Key and References for ON DELETE CASCADE in thesql

It is a MySQL "gotcha". The first way does NOT work.

From MySQL docs, FOREIGN KEY Constraints:

Important:

... 4 paragraphs below...

Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications.

Creating the 2 tables (way 1):

CREATE TABLE parent_table (
parent_id int(11) not null auto_increment,
PRIMARY KEY(parent_id)
);

CREATE TABLE child1 (
id int(11) not null auto_increment,
parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE CASCADE,
PRIMARY KEY(id)
);

Lets see what is child1:

> SHOW CREATE TABLE child1 ;

delimiter $$

CREATE TABLE `child1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Where is the FOREIGN KEY ? ... Gone with the wind (and without warning)

Creating the table child2 (way 2) works fine:

CREATE TABLE child2 (
id int(11) not null auto_increment,
parent_id int(11),
PRIMARY KEY(id),
FOREIGN KEY(parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE
);

> SHOW CREATE TABLE child2 ;

delimiter $$

CREATE TABLE `child2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `child2_ibfk_1` 
    FOREIGN KEY (`parent_id`) REFERENCES `parent_table` (`parent_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$