One of my applications retrieves data from a third-party service and caches it in an SQL database. The external service's responses use strings as the ID of certain records, but in my database those records are also given a numeric primary key. When correlating other responses for different record types from the third-party service, I often need to get the numeric ID corresponding to the service's text ID. This is a very simple subquery, but it broke recently due to an unfortunate concurrency issue. The text-ID-containing items got inserted into the table twice, so the subquery started returning multiple values, which removed the ability to put the result into a single field and therefore broke other parts of the system.
The duplication could have been prevented with transactions (which I have now put in place for that component - others already used transactions), but the cascade failure could have even more easily been prevented by putting a unique constraint on the textual ID column.
No comments:
Post a Comment