7

I'm working on an SQL server database, containing orders and machines, executing those orders. Not more than one active order can be assigned to a machine at the same time. In other words: this SQL request can never yield a result:

SELECT MachineId
  FROM Orders
  WHERE (Orders.Status=1)
  GROUP BY MachineId
  HAVING COUNT(Id)>1

I have this SQL request open in a Microsoft SQL Server Management Studio environment, and every time I press F5, I indeed see no results.

However, I am sure that there have been times where that SQL query did yield results, and I'm interested in those times and the results of that query at those times.

Does anybody have an idea on how I can find this out? (The "archive-log" tag is just an idea)

Thanks in advance

3
  • Btw, your issue could be as simple as wrapping a database transaction around when your code grabs the next available MachineId and when it assigns it to the Orders table, so they are within the same transaction and occur atomically, which will block any other transaction from grabbing the same MachineId
    – J.D.
    Jul 7 at 12:26
  • 2
    ” Not more than one active order can be assigned to a machine at the same time” - is this business-rule implemented as a CONSTRAINT or not? Or is it enforced by non-SQL application code - or not enforced at all? This rule should be trivial to implement using a UNIQUE constraint - and once done means you won’t need to worry about this ever happening again.
    – Dai
    Jul 8 at 0:33
  • Are you using NOLOCK or READ UNCOMMITTED isolation level? Because those aare sure ways to cause this. Jul 8 at 13:12

2 Answers 2

6

Create a logging table. Change your query to

INSERT LoggingTable(...)
SELECT MachineId, SYSUTCDATETIME()
...

Use SQL Agent to run this query as required. (It's minimum interval is 1 minute.)

The problem is you only see errors that exist for the few milliseconds the query executes every few minutes - a fraction of a percent of the time the system is running. If you add temporal tracking to Orders you can see every change that has happened. You can then run an analysis query over this history to detect errors. History can be copied to a separate archive DB if desired and removed from the operational DB when no longer required, to keep that DB lean.

10

I would create a unique filtered index and track the unique constraint violations. It prevents the wrong data in the first place, and unlike periodically polling the table, you won't miss the occurrence.

The definition would be

CREATE UNIQUE NONCLUSTERED INDEX IX_Orders_StatusPerMachine
ON dbo.Orders(MachineId) 
WHERE (Status = 1)

Then I would use the error_reported Extended event to track the specific error number, which probably will be:

Msg 2601
Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.

and grab some additional info like app_name, host_name, tsql_stack etc. to find the root cause.

I blog about debugging errors with the Extended events here
https://straightforwardsql.com/posts/investigating-errors-with-extended-events/

4
  • That's neat, but what if duplicate rows are part of the application logic and you don't know? That would break the app... Jul 7 at 13:07
  • I'm providing the answer to the question "as is". For your scenario, I would probably take this approach straightforwardsql.com/posts/…
    – Zikato
    Jul 7 at 17:15
  • 2
    It's not my scenario, i was just suggesting to the OP to be careful implementing this type of solution (which is a great, don't get me wrong) in production without testing. But that goes for almost every solution, so 🤷‍♂️ Jul 7 at 19:36
  • 1
    Could you use a trigger on insert and delete to insert and delete into a test table with this constraint and then track the errors the insert trigger gets—without affecting the logic of the main table? Jul 7 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.